The Recalcitrant DBA

Identity columns are nothing new in T-SQL. They are great when you want a unique number to be automatically assigned every time you insert a new record. The trouble is that while they are unique within the table, they are probably the same between tables. Every table with an identity column has a 1 for the first record, a 2 for the second, etc. Of course you can set the seed and increment for the identity column to something other than (1,1), but you still have to manually track which table has which seed. Trying to track that is destined for failure. What we really need is a way to have automatically assigned numbers for new records, but ones which are guaranteed to be unique across any table that uses them. Of course, you know I wouldn’t be writing this if there weren’t a solution. In SQL Server 2012, Microsoft introduced something called the SEQUENCE object that does this very thing. ENTER THE SEQUENCE… There are two steps involved here. First we create the SEQUENCE obje...

When a Check Constraint exists on a column, it checks the insertion of new records to determine that the value for that column conforms to the limitations of that constraint. Check Constraints allow us to define what are acceptable values for that column. They are of great importance for ensuring Data Integrity. For instance, a sales manager may determine that discount levels cannot be greater than 30%, so the DBA puts a Check Constraint on the Discount column to only allow values less than or equal to 30. CREATE TABLE Customers (CustID int Identity ( 1 , 1 ) , Name nvarchar ( 50 ) , Discount int CONSTRAINT chkDiscount CHECK (Discount <= 30 )); Now, if a salesperson attempts to enter a record and give their new customer a big discount, the record will not be inserted and an error will be returned indicating that “The INSERT statement conflicted with the CHECK Constraint “chkDiscount”. Perfect, we have accomplished our goal of not allowing big discounts. However,...

Yesterday, I had the privilege of presenting a webinar for Quickstart , the company for which I work.  The topic I was asked to address was SQL Databases vs. SQL Server in Azure.  I went a bit longer than I expected to, but there was good turnout and a lot of interest and some good questions. If you're interested, take a look , it is on YouTube .  It looks like there is about a 2 minute dead spot at the beginning where there is no sound.  I'll try to get that fixed, but for now, just jump to about 2:15 mark. Enjoy, Jeff

For some of you, this will be fairly basic, but I have found that there is a good deal of misunderstanding about this subject, so I thought I’d clear some of them up. SQL Server ships with tons of Stored PROCedures (SPROCS), but as you are probably aware, you can create your own. So, let’s take a look at how to create a SPROC that contains Input parameters, Output parameters and Return values. First, let’s create a simple SPROC: CREATE PROC Production.sp_LongLeadProducts AS SELECT Name, ProductNumber FROM Production.Product WHERE DaysToManufacture >= 1 GO There are a couple of things to note about this SPROC. The first is that it violates several Best Practices: You should SET NOCOUNT ON in all SPROCS You should not name SPROCS with a sp_ prefix. When the DB engine encounters a call to an object with this prefix, it first looks in Master to find the object. Avoid this by using a different prefix, or don’t use a prefix at all. Some naming convention experts r...

SQL Server Management Studio (SSMS) has been around since SQL Server 2005 and is a surprisingly nice environment from which to manage SQL Server.  Of course it has its shortcomings and there are 3d party tools that supply either replacements or add-ins to it.  However, if you have not given it a through look over, it might be easy to ignore some of its best features, so I thought I would take you on a quick tour.  Everything I list here is for the version of SSMS that shipped with SQL 2012, but most of the items mentioned are available in any version.  So, let’s start with some basics and then move on to some more sophisticated things.   SSMS Settings   From the Tools menu, click Options .  You now have a wealth of ways to customized SSMS at your fingertips.  Too many to go over in detail, so a few of my favorites, then.  Environment \ Fonts and Colors gives old geeks like me the chance to adjust the font size for query w...