Esoteric

The inside scoop on SQL / debugging you won't find anywhere else!

Browse by Tags

Spread the word!
Tagged Content List
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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!
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: ‘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...
  • Blog Post: 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...
  • Blog Post: 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....
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
  • Blog Post: 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...
Page 1 of 1 (18 items)