The inside scoop on SQL / debugging you won't find anywhere else!
SQL High Availability
SQL Server 2014
Browse by Tags
Spread the word!
Tagged Content List
Tracking TEMPDB internal object space usage in SQL 2012
It is a documented fact that using the MAX specifier can result in TEMPDB usage in specific cases. From the TEMPDB whitepaper , some of these can use ‘internal object’ space within TEMPDB: “all of the large object types: text , image , ntext , varchar(max) , varbinary(max) , and all others.” Now, a FAQ...
24 Feb 2014
Normalizing T-SQL text, part 2: using the TransactSql.ScriptDom classes
Happy New Year 2014 to all of you! With SQL Server 2014 set to release this year, I’m sure you are all excited about the months to come. In my previous post I had reviewed the classic ways of obtaining ‘normalized’ text for ad-hoc SQL queries. Do take a minute to glance at that post in case you have...
2 Jan 2014
Normalizing T-SQL text, part 1: using the RML Utilities and the DMVs
A common problem when dealing with workloads which issue ad-hoc SQL commands (i.e. without parameterization) is to find out the ‘normalized’ version of the pattern. For example, these three statements are essentially the same ‘template’: SELECT BusinessEntityId FROM Person.Person WHERE LastName = ‘Smith...
30 Dec 2013
SQL PASS ScriptDom talk: Recording now available!
The SQL PASS AppDev team has uploaded the recording of my session. You can click on the embedded player to view the HD recording. And if you have not looked at the slides and demos, please do review my previous post which refers to those!
11 Nov 2013
XML ‘Visualizer’ for the TransactSql.ScriptDom AST
I’ve described the ScriptDom parser previously on this blog . The visitor pattern is great when you know exactly what you are looking for in the parse tree, but as you would have seen, due to the polymorphic nature of the ScriptDom classes, it is quite difficult for a truly generic tree walk when...
30 Oct 2013
PowerShell script to extract T-SQL task code from SSIS packages
Requirement Some time back I presented a PowerShell script which extracts T-SQL code from RDL files. Remember that I created this script to assist me in doing code reviews of ad-hoc SQL snippets which were embedded in the report definition files. Another common usage of ad-hoc T-SQL code is within SSIS...
14 May 2013
PowerShell script to extract T-SQL code from RDL files
One of the things I do in my day job at Microsoft Services is to review database design and T-SQL code. Now, when it comes to code, there is a challenge just to find ALL the possible code which hits our database engine. This is because a non-trivial amount of T-SQL code is not in the form of Stored Procedures...
30 Apr 2013
‘Cannot resolve the collation conflict’ error message with temp tables
Scenario Today my customer told me that he is facing an error message ‘Cannot resolve the collation conflict’ when executing an JOIN between two tables: a base table and a temporary table. They had recently created the database afresh from scripts and were facing this issue since. On the existing ‘good...
23 Apr 2013
Using the TransactSql.ScriptDOM parser to get statement counts
Today there was a question on the #sqlhelp Twitter hashtag: “Is it possible to get UPDATE/SELECT/INSERT/DELETE statement counts from SQL Server?” Implementation This is a perfect use case for the SQLDOM parser a.k.a. Microsoft.SqlServer.TransactSql.ScriptDom. I put together a sample C# application to...
4 Apr 2013
TechEd India 2013 - ‘T-SQL Horrors’ slides
TechEd 2013 was a grand success! Thank you – those of you who stayed till 6:15PM at the Pune session – and even more to those who engaged me in Q&A till 7PM that evening I was very impressed and happy to see people interested in my talk, even though it was at the end of a very long day for most....
3 Apr 2013
Considerations when using the TransactSql.ScriptDOM parsers
Some of you might be aware of the above namespace , which holds an implementation of a first-class T-SQL parser. In this post I would like to explain some of the complexity you will face when dealing with the ScriptDOM yourselves, typically using Visitor pattern. Case Study Our objective in this case...
27 Mar 2013
Named constraints: two sides to the story!
Background Constraints in SQL Server are of the following types: CHECK constraints DEFAULT constraints Foreign key constraints NULLable constraint UNIQUE constraint PRIMARY KEY constraint BTW, if you are wondering what a NULLable constraint is, it is the formal representation of a NULL / NOT NULL definition...
21 Feb 2013
Opinion Poll: Are PRINT statements considered harmful?
Today during a discussion a point came up around the role of PRINT statements in production code. While most data access today is routed through a data access layer (typically .NET or JDBC) and is focussed on consuming result sets (or executing U/I/D nonquery stuff) we were wondering on what you use...
24 May 2012
t-SQL Anti-Pattern: Index Key Order and Query Expression Order
This is really not a T-SQL anti-pattern as much as it is a database design issue, but we see it so often that it’s worthwhile bringing it up and clarifying things. For illustrating the scenario, let’s examine the table Person.Contact in the AdventureWorks database. It has 2 columns called FirstName and...
6 Jul 2009
T-SQL Anti-pattern of the day: 'all-in-one' queries
Scenario A common requirement for enquiry queries on an OLTP database is to have search criteria which are very specific ('get me details for for OrderID = NNNN') and also the occasional reports which ask for all the orders ('get me all the orders, no questions asked'.) Here is a sample from AdventureWorks...
19 Dec 2008
T-SQL Anti-pattern of the day: comparing DATETIME field with date-only literal
Scenario It is a pretty common situation to have transaction date-time stored in a DATETIME field. The problems start with the fact most applications used GETDATE() or some such equivalent at the client side to record the order date-time stamp. So a typical entry for an OrderDate would actually end up...
2 Dec 2008
T-SQL Anti-pattern of the day: UPPER() and LOWER()
This one is even more common and therefore has a high impact: the usage of the above functions in WHERE clause predicates. I've examined each in some detail below. Here's a typical example I see, based off the AdventureWorks database. Scenario To illustrate the effect of this example, I have added an...
24 Nov 2008
T-SQL Anti-pattern of the day: Prefix codes stored in (n)varchar fields
In the course of my work as a SQL Server Premier Field Engineer, we tend to see a lot of patterns in code. Some of these patterns do not lend themselves to optimal performance. This post is the first in a series of such 'how not to code' tips which would hopefully help some T-SQL developers out there...
24 Nov 2008
Page 1 of 1 (18 items)
© 2014 Microsoft Corporation.
Privacy & Cookies