Posts Tagged ‘TSQL’

TSQL: Restore a database without a log (ldf) file

I had to restore a bunch of databases missing a log file today. This was only a test server but I wanted to get it running as quickly as possible. The script here is based on the advice in this blog post from sqlskills. Note that this is very dodgy and should only be used […]

A quick example of why IN can be bad

Here’s just a quick demo that illustrates why the IN operator in TSQL might not perform as well as alternatives (like a range query or joining onto a temporary table containing your values). ?View Code TSQLIF OBJECT_ID(’tempdb..#temp’) IS NOT NULL BEGIN DROP TABLE #temp; END;   CREATE TABLE #temp ( id INTEGER NOT NULL IDENTITY(1,1), […]

Generate PK drops and creates using TSQL

Here’s just a couple of queries I used to generate PK drops and creates using the sys.key_constraints view. I wanted to do this for a database using Poor Mans Partitioning. Generate drops… ?View Code TSQLSELECT ‘ALTER TABLE ‘ + OBJECT_SCHEMA_NAME(t.[object_id]) + ‘.’ + + ‘ DROP CONSTRAINT ‘ + c.[name] + ‘;’ FROM sys.key_constraints […]

Moving user databases the TSQL way

Here’s a few queries I built to construct the commands needed to move user database files in SQL Server 2208 R2. The queries are based on the procedure outlined here. As with all scripts on the Internet take care with this. It worked fine for my circumstances but may not in yours. Be careful and […]

Using Hints in SQL Server

Query hints are bad right? I confess to using them on odd occasions but only when other attempts to find a solution have failed. Microsoft emphasize this themselves; Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that <join_hint>, <query_hint>, and <table_hint> be used only as a […]