The Recalcitrant DBA

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