Category Archives: SqlServer

How to find Expr#### in Execution Plan

Questions: When looking at the actual execution plan for a query in SQL Server Management Studio (SSMS), how do I determine what an expression such as Expr1052 represents? When I identify the costly parts of the query and look at the properties of that operation, there are often references only to these Expressions, or scalar… Read More »

Getting .NET schema for a stored procedure result

Questions: I have a couple of stored procedures in T-SQL where each stored procedure has a fixed schema for the result set. I need to map the result sets for each procedure to a POCO object and need the column name and type for each column in the result set. Is there a quick way… Read More »

Clustered and nonclustered indexes performance

Questions: I have a huge table (~ 10 million rows) with clustered PK on a random uniqueidentifier column. The most operations I do with this table is inserting a new row if there is not yet a row with the same pk. (To improve performance of it I use IGNORE_DUP_KEY = ON option) My question… Read More »

NVARCHAR(?) for Email addresses in SQL Server

Questions: For Email addresses, how much space should I give the columns in SQL Server. I found this definition on Wikipedia: http://en.wikipedia.org/wiki/Email_address The format of email addresses is local-part@domain where the local-part may be up to 64 characters long and the domain name may have a maximum of 253 characters – but the maximum 256… Read More »

Convert from UniqueIdentifier to BigInt and Back?

Questions: declare @uu uniqueidentifier = ‘C50B0567-F8CC-4219-A1E1-91C97BD9AE1B’ select @uu declare @zaza bigint = ( select convert(bigint, convert (varbinary(8), @uu, 1)) ) select @zaza select CONVERT( uniqueidentifier , convert( varbinary(16) , @zaza , 1 ) ) I thought I had a fast way to convert Unique Identifier values to a Big Int, and back. But there is… Read More »

handshaking a SQL server with Javascript

Questions: I want to try, as a learning excersise, get my javascript to chat to sql. var ws = new WebSocket(“ws://127.0.0.1:1433”); doesn’t seem to be a blocked port, so it should in theory work. I am looking for a breakdown of how to handshake with the sql server, and chat with it. A pointer in… Read More »

Enabling CLR Integration on SQL Server 2008-r2

Questions: Looking for Enabling CLR Integration I found this document: http://msdn.microsoft.com/en-us/library/ms131048.aspx that said to use the following code for setting to 1 the “crl enabled” variable. sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘clr enabled’, 1; GO RECONFIGURE; GO I want know If a reboot of SQL Server is required? Or, more generaly,… Read More »

Tool for detecting copy/pasted code in TSQL?

Questions: I’m working on a SQL Server 2005 system with a lot of copy-pasted code, lots of repeated calculations instead of functions, many stored procedures that differ only very slightly instead of being parameterized. Also lots of SSIS packages that are very similar. Moving forward, I’d like to be able to to detect this type… Read More »