Archive for Indexes

| January 13, 2015
Comments Off on T-SQL Tuesday #62 – Primary Key Constraints Are Good For Your Database

T-SQL Tuesday #62 – Primary Key Constraints Are Good For Your Database

Robert Pearl (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.

This month’s invitation is about making sure your database is healthy. For me, that is not just making sure that patches are updated and backups restore properly. It also means the database schema is healthy.

In the beginning

One of the simplest things that we can do when creating a new table, is to make sure there is a primary key constraint. A primary key constraint is simply a specialized index. It can be a clustered or non-clustered index.  The important part is, it is created as a primary key constraint.

Why?

Two strong reasons come to mind on why you want to have primary key constraints on your tables. The first, is the accuracy of your data. The purpose of a primary key is to uniquely identify a record (just like your fingerprints). By using primary key constraints, SQL Server will guarantee that the primary keys are unique.

I’ve had application developers argue with me that the application will guarantee that the primary key is unique, so a primary key constraint is not needed. I’ve also had other developers tell me that using GUIDS or identities will guarantee uniqueness. Even if you use these data types or have checks in your application to verify the value is unique, it is still possible to go around the application to insert data and it is possible for multiple applications to simultaneously provide what they think is a unique value. A primary key constraint can will prevent duplication.

This does bring me to the second reason. Say, you do a have a spectacular way to keep all of your primary keys unique. Without primary key constraints, you can’t implement Transactional Replication. That’s right. Transactional Replication requires primary key constraints.

How can I find my straying tables?

Now that I’ve (hopefully) peeked your curiosity, let’s look at a way to find a list of tables that are missing primary key constraints.

SELECT
     t.name AS TableName
FROM
     sys.tables AS t
     LEFT JOIN sys.indexes AS i ON i.object_id = t.object_id
          AND is_primary_key = 1
WHERE
     is_primary_key IS NULL
ORDER BY
     t.name

Remember it is not a good idea to “just add” new constraints without determining the impact on the current system. Once you have the list of tables that are missing primary key constraints, you can go through the process of determining if you can add them into the environment without side affects. Some side affects can include having to deal with duplicate data, increase database size by the addition of new indexes, and better performance.

Thanks for all the fish

Thanks go out to Robert Pearl for hosting this month’s T-SQL Tuesday blog party.

Category: Data Modeling, Indexes, T-SQL, T-SQL Tuesday | Tags: , , , ,
| December 6, 2013
One comment

SQL Advent Calendar – Day 6 – What’s In All These Indexes?

AdventCalendar06First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Through the Looking Glass I see Many Indexes

Last year, I was doing quite a bit of query tuning. I was constantly looking at what indexes existed before I made new ones.  I created this snippet to Red Gates’ SQL Prompt to be able to quickly look at all the indexes for a given table. Sometimes I even found duplicate indexes when uses this snippet. It’s one of my favorites. I call it Indexes4Table.

--Use Shift + Ctrl + M to set Template Placeholders
USE <database,sysname,$dbname$>
GO
/* ***************************************************
Purpose:    Lists all indexes for a table
Author:     mickey stuewe www.mickeystuewe.com
Date:       12/6/2013
*************************************************** */
SELECT
	i.name AS IndexName
	,i.type_desc
	,STUFF(Indexed.ColumnList, 1, 1, '') AS IndexColumnList
	,STUFF(Included.ColumnList, 1, 1, '') AS IncludeColumnList
	,i.filter_definition
	,i.fill_factor
FROM
	sys.indexes AS i
	OUTER APPLY
			(
				SELECT
					',' +  CASE 
							WHEN ic.is_descending_key = 1 THEN col.name + ' DESC'
							ELSE col.name  
							END 
				FROM
					sys.index_columns AS ic 
					INNER JOIN sys.columns col ON ic.object_id = col.object_id
													AND ic.column_id = col.column_id
				WHERE
					ic.index_id = i.index_id
					AND ic.object_id =  i.object_id
					AND ic.is_included_column = 0
				ORDER BY
					ic.index_column_id
				FOR XML	PATH('')
			) AS Indexed(ColumnList)
	OUTER APPLY
			(
				SELECT
					',' +  CASE 
							WHEN ic.is_descending_key = 1 THEN col.name + ' DESC'
							ELSE col.name  
							END 
				FROM
					sys.index_columns AS ic 
					INNER JOIN sys.columns col ON ic.object_id = col.object_id
													AND ic.column_id = col.column_id
				WHERE
					ic.index_id = i.index_id
					AND ic.object_id =  i.object_id
					AND ic.is_included_column = 1
				ORDER BY
					ic.index_column_id
				FOR XML	PATH('')
			) AS Included(ColumnList)
WHERE
	i.object_id =  OBJECT_ID(N'<schema_name,sysname,dbo>.<table_name,sysname,>')
ORDER BY
	i.type_desc
	,Indexed.ColumnList

There are a couple of things I want you to notice. I’m using a string concatenation pattern based on Peter Larson’s (b) SQL, which is written about on Adam Machanic’s (b|t) blog here. I also wanted you to notice that you can use placeholders within a string. See line 57.

I ran my script against the Persons.Person table in AdventureWorks2008R2 (I also added an index so that you can see all the columns filled out. Here is the outcome.

AdventCalendar2013Day6_Img1

Now I can quickly look at all the values that I find important for my indexes on a particular table.

Previous Post In Series: SQL Advent Calendar – Day 5 – Snippet For New Indexes

Next Post In Series: SQL Advent Calendar – Day 7 – Verify All Tables and Fields Have a Definition

 

Category: Indexes, Red Gate, Script, SQL Advent Calendar, T-SQL | Tags: , , , ,
| December 5, 2013
One comment

SQL Advent Calendar – Day 5 – Snippet For New Indexes

AdventCalendar05First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

New Index

I have four snippets that are templates for new SQL Objects. The one I’m sharing today is for new indexes. The great thing about  snippets like this, is I add comments to help me or my teammates make decisions on how the SQL object is created.

Notice that I included the hint of “Use Shift + Ctrl + M to set Template Placeholders”. I wrote about them here. I start all of my “new” templates with the word new, but you can really call them whatever you want. This one is NewIndex. Notice that some of the Template Placeholders are repeated. That keeps me from having to do repetitive work. (WIN!)

USE <db,string,$dbname$>
GO

--Use Shift + Ctrl + M to set Template Placeholder
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'<schemaowner,string,dbo>.<tablename,string,>') AND name = N'IX_<tablename,string,>_<fields,string,>')
DROP INDEX IX_<tablename,string,>_<fields,string,> ON <schemaowner,string,dbo>.<tablename,string,> WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX IX_<tablename,string,>_<fields,string,> ON <schemaowner,string,dbo>.<tablename,string,>
(
	$CURSOR$<fields,string,> 
)
<optional_include,string,include()>
<optional_where,string,where()>
WITH 
	(FILLFACTOR = <fillfactor,string,95>)ON [PRIMARY] --GUID Datatype use FillFactor = 80, otherwise use FillFactor = 95 
GO

Here is what the dialog box will look like as I fill it out.

AdventCalendar2013Day5_Img1

Here is what the code will look like after I click OK. Notice that the INCLUDE clause is filled out and the WHERE clause has been omitted. One “gotcha” for this, is the list of fields. You have to either replace the underscores with commas in the list or replace the commas with underscores in the name. It still makes my life easy.

AdventCalendar2013Day5_Img2

 

Previous Post In Series: SQL Advent Calendar 2013 – Day 4–Comments Are Like Love Notes to Your Future Self

Next Post In Series: SQL Advent Calendar – Day 6 – What’s In All These Indexes?

Category: Indexes, Red Gate, Script, SQL Advent Calendar, T-SQL | Tags: , , , ,
 
%d bloggers like this: