Posts

Showing posts with the label nonclustered

Migrate SQLPrompt Snippets to VSCode

 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr...

Primary Key columns in INCLUDE list

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 ...

Covering Indices

Image
I had a question posed to me where a person was working on a procedure which was running great until he added one new column to the table and query. To start at the end and work my way forwards, the issue was that the new column cause the query to no longer be "covered". What the heck does that mean? In short, it means that if you look up something in a given index, every piece of information SQL Server needs to return that data can be found in the index. To understand how that's accomplished you'll need a little primer on index structure. This is not an in-depth review of indices, but the concepts will be good enough. Take the following table, with some test data put in: if object_id('tempdb.dbo.#Users') is not null drop table #Users create table #Users ( UserID int identity(1,1) primary key clustered, FirstName varchar(100), LastName varchar(100), Birthday date ) insert into #Users (FirstName, LastName, Birthday) values ('Gabe',...