Monthly Archives: September 2006
New SQL Blog – Optimise Optimize Optimise
Love query optimisation? The SQL Server Query Processor team have started a new blog "Tips, Tricks, and Advice from the SQL Server Query Processing Team". They also link to an old blog I hadn’t come across before which appears to … Continue reading
Two helpful TFS posts…
These two posts would have come in handy about 6 months ago! TFS V1 doesn’t make a lot of things easy. Here are solutions to two of the most frustrating ‘features’: Determining Whether Tests Passed in Team Build and Building … Continue reading
#Tables, Table Variables, CTE’s – what’s the difference?
Here is a handy table that explains some of the differences between the three. The full sized version is available here. I’d say that in the past I’ve used Table Variables about 60 to 70% of the time when I’ve … Continue reading
CREATE INDEX ….. [ ASC | DESC ]
I came across a good post the other day that highlighted something I’d not seen or used in the CREATE INDEX statement. Columns can be specified to be sorted either ASC or DESC. From BOL: [ ASC | DESC ] … Continue reading
Using .WRITE on VARCHAR(MAX)? – Part 2
I’ve been delving into the .WRITE operator a bit more, and in particular it’s more complex execution plan when replacing data. Here’s the setup code for the tests I’ve been running: SET NOCOUNT ONUSE tempdbGO – CONFIGURE THE NUMS TABLEIF … Continue reading
Using .WRITE on VARCHAR(MAX)? – Part 1
Are you using .WRITE when updating the contents of a VARCHAR(MAX) or NVARCHAR(MAX) column and are you replacing the whole contents (using @Offset = 0 and @Length = NULL)? UPDATE MyTableSET col1.WRITE (‘Lorem ipsum’, 0, NULL) or simply replace the … Continue reading
Microsoft SQL Server 2005 Query DeskSheet
I’ve been doing some reading lately and made some notes (to myself) on various querying aspects of SQL 2005. As a reference, I’ve created a ‘DeskSheet’ so I have something to quicky refer back to… Here’s the first version of … Continue reading
Security Bits
Taking a small detour away from TFS & SQL, go here for a free pdf (4.8Mb) of the The Developer Highway Code. There are also some great resources for security training here. [Category updated to 'Security'.]
The rise of TOP, and fall of ROWCOUNT
In SQL Server 2005, TOP has gained a few new capabilities. SELECT TOP (scalar expression | variable | subquery) [PERCENT] *FROM aTable TOP can also be used in DML statements. Previously you may have used ROWCOUNT to limit the affected … Continue reading
Toggle Actual Execution Plan [CTRL+M]
According to SQL Server 2005 Books Online (July 2006 Refresh), there is no keyboard shortcut to display the actual execution plan for a query. The documentation states: Include actual execution plan in the query output -> No equivalent. This appears to … Continue reading
