Quick T-SQL Tip #2: Identity Columns Seed Value

This post really deals with high volume tables with an identity column, but the idea is something that everyone should at least be familiar with.

Quite often I see table creation scripts that start like this:

Create Table ATable
(
ID int not null identity(1,1)
...

And while that is perfectly legal and will work with no issues, if this table is going to be a high volume table, you should consider using the entire Int, not just half of one.

What??

The Int data type spans -2,147,483,648 thru 2,147,483,647.  If your seed is 1, you lose 2,147,483,649 values that could be used.

I have seen production situations where they have run out of identity values because the seed was 1.
So, next time you create an Identity, ask yourself: "Two billion, or four billion?"

and then 

Create Table ATable
(
ID int not null identity(-2147483648,1)
...

peace

Quick T-SQL Tip #1 - Formatting SQL

I am going to start a series of posts for quick T-SQL tips.  In my current position, I support a group of business report developers that are not too familiar with T-SQL.  This will help them learn some of the tips and tricks that I use frequently.

So, tip #1, Formatting SQL.

This tip does not just apply to T-SQL, but any kind of code in general.
I am sure most other developers have been here:  You inherit some code and the first hour is spent trying to figure out what it's doing!

Well, here is my basic formatting.  My goal is to let anyone look at it and understand it quickly!


Select tab Field1 As Field1Name,
tab tab Field2 as Field2Name
From tab Table1 T1
Inner join
tab tab Table2 T2
on tab tab t1.Field = T2.Field
Where tab T1.Field = 'AA'
Order by
tab tab T2.Field1 ASC

Doesn't look that pretty there, but in SQL Management Studio, it works like a charm!  :)

My goal is to have enough whitespace that the SQL does not look cluttered.
No matter how you format, always keep the next developer in mind!!

peace