Towering Queries

I was reviewing someone's code the other day and I saw they had put primary key columns in the INCLUDE list of a non-clustered index -- Create a table if object_id('tempdb.dbo.#PhoneBook') is not null drop table #PhoneBook create table #PhoneBook ( FirstName nvarchar(500), LastName nvarchar(500), PhoneNumber varchar(20), DOB datetime ) create clustered index #IXC__#PhoneBook__LastName on #PhoneBook (LastName) create nonclustered index #IXN__#PhoneBook__PhoneNumber on #PhoneBook (PhoneNumber)   A little background on the physical structure of non-clustered indexes for those who might not know. A non-clustered index consists of at least two, and up to three components. All non-clustered indexes store the index key (in my example above, this would be [PhoneNumber] in the nonclustered index) as well as a pointer back to the clustered index (in this case, the columns [LastName]). The Phone Book example is the classic indexing example thrown around. The ...

I hear from many people (my wife included) that they “just don’t have a mind for programming”. Now maybe that’s just a polite excuse to say they have no interest in programming, which, too, is fine. And while, like with music, there are some people who really have (or do not have) structures in their brains making it difficult if not impossible to do, I think the majority of the skills are generic enough to be learned with practice and more importantly, a drive to do so. One of the skills you pick up along the way is what, by analogy, I’d like to think of as rolling an object around in your hand. There are few things in our daily lives that we’ve never seen before, let alone something you can hold in your hand. But there are some. Imagine an unsolved Mirror Cube . Or maybe you’ve decided you want to work on your car, and you start removing parts you never knew existed. Or maybe you get a Christmas present still in the packaging and you’re trying to figure out what it might be? ...

Imagine you have a column of data which is supposed to contain numbers, but you don't know which ones are numbers. Imaging you also live in a world where SQL Server 2012 and above don't exist. Now imagine you stumble across the function ISNUMERIC one day and think to yourself "huzzah! My worries are over!". Now imagine you put that check in to your code, push it to production, and low and behold, it doesn't do what you expect. This is documented elsewhere as well but ISNUMERIC might not do what you expect. It includes characters which aren't strictly speaking numeric values. For instance, it contains several currency signs. It allows some white space characters, and it allows commas, pluses, minuses and more. Consider the following. select Idx = num, Chr = char(num), IsNum = isnumeric(char(num)), IsNum_e0 = isnumeric(char(num) + 'e0') from (select top 255 num = row_number() over (order by (select null)) from sys.all_objects...

4597643839206139798834394184759123627791606542999044065434955006850467973888487369646743179134539924511398190750442203331208226857500559468414748729869089525893226953200795906903714832067821898907042261629562728969942119810014613789793907457345248217125680762482968429028968939623699293199693990805806760060083861925862374452062687514806778782280596095497203919252435260157455617162094230854524019588079203409134421869785206499300227993925963914895037051314258884162026719316085920116208512607769964983729984223110401900292801383099981541017532169507507681124468931938419338579838214678528787775049750261691770671966431262173069310693622479464912917015256994752289419408900432979739095957483066465378825509399001405061840629568788641562545240398182200693952819414127599029255936602043937937432667723682069384711335292603164311632276466199034110175386513375870207080042797827150133881229745175742324393298120067283358746413408824972192268005011421045053657631165662129060077428969674686340281707871260...

SSIS is a bit of a touchy subject where I work, and the more I use it, the more I sort of understand why. While SQL Server is my background and I consider myself an expert in TSQL (not like, MVP level or anything), I don't do too much in SSIS. We have a home grown architecture which, in many ways, mimics functionality SSIS can do, and, for our purposes, usually works better and comes with fewer headaches. That said, there are some operations SSIS is simply hands down the best tool for the job. But I didn't come here to talk to you to day about when to or not to use SSIS. No dear reader, I come with a warning/complaint about how it surfaces error. The description of how it surfaces errors is "not very well". It's undoubtedly something you get better at teasing out over time, but I think largely because of the breadth of systems it needs to interact with, surfacing meaningful errors for specific stages can often seem to have absolutely nothing to do with the e...

I got bored and was fiddling around with a statement I'd been doing manually in several steps to try to make it as easy to use (not read) and as quick as possible. The rundown is this:     I have a query I want to run which spans two databases.     The relevant schema for these two servers is as follows: -- Server 1     -- Has about 500m rows     create table dbo.orders     (         order_id varchar(55) primary key clusterd,         Data varchar(8000) -- just a placeholder to show that there's other stuff in here     )     -- Server 2     create table dbo.orderLog     (         order_id varchar(55) primary key clustered,         PayloadId int unique,         OtherData varc...

This just executes sp_spaceused on every table in a database, and plops the results into a temp table so you can query the data altogether. declare @TableName varchar(128), @RID int, @MaxRID int, @SQL nvarchar(max) if object_id('tempdb.dbo.#LoopSrc') is not null drop table #LoopSrc create table #LoopSrc ( RID int identity(1,1) primary key clustered, TableName varchar(128) ) if object_id('tempdb.dbo.#Tabs') is not null drop table #Tabs create table #Tabs ( TableName varchar(128), nRows int, nReserved as cast(replace(sReserved, ' KB', '') as int), nData as cast(replace(sData, ' KB', '') as int), nIndexSize as cast(replace(sIndexSize, ' KB', '') as int), nUnused as cast(replace(sUnused, ' KB', '') as int), sReserved varchar(30), sData varchar(30), sIndexSize varchar(30), sUnused varchar(30) ) /***************************** *** INSERT LOOP ITEMS...