Bookmark : Naming standardization scripts

Michael Søndergaard has written some excellent Naming standardization scripts that he has published on his site.

They cover the naming of Indexes , Check Constraints, Default Constraints and Foreign Keys and can potentially save you a lot of time.
This of course is only if you can agree a Naming convention with your colleagues...

r

dbautils.spFixIndexNaming
dbautils.spFixColumnCheckNaming
dbautils.spFixColumnDefaultNaming
dbautils.spFixForeignKeyNaming

Quindecillion

Am currently using a SHA-1 hash generating function to generate unique indexes over large text columns.
The NVARCHAR(1024) columns I am encoding could cost as much as 2048 bytes each to store before compression (as they’re UNICODE).
The idea is to reduce my storage and to be able to index large text columns using HASHBYTES. The function allows me to use VARBINARY(20) to hold the Hash calculation. 20 bytes being a lot more friendly than 2048.

Links :
1) SHA Hash functions
2) MSSQLTips.com : Unique constraints for large text columns (using hashbytes)

To quote that article,

The odds of a duplicate hash value being generated are 1 in 1,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,00

1 in 1,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 is ‘one in a quindecillion’.

Here’s how I found out this amazing fact...
Really Big Numbers

(you can all go back to sleep now...)

Bookmark : Constraints on Large Text Columns

An interesting way of enforcing unique constraints which tackles the problem of index size by using HASHBYTES (like CHECKSUM but better)

SQL Server Unique Constraints for Large Text Columns

Unique Constraint Failure Message

I hadn't noticed this before, the error message tells as where the rogue data is...

Msg 1505, Level 16, State 1, Line 3
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.TrackingData' and the index name 'UniqueTrigger'. The duplicate key value is (2008-06-09 07:05:19, 350824225).
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.
The statement has been terminated.


http://www.sql-server-performance.com/faq/create_unique_index_terminated_p1.aspx

USP_DropTableConstraints_2005

Drops all constraints of the specified type from a table.
Takes database name, schema name, table name and constraint type as parameters.
Valid constraint types are ; 'PRIMARY KEY', 'FOREIGN KEY', 'CHECK'

Usage :
EXEC USP_DropTableConstraints_2005 'AdventureWorksTarget', 'Person', 'Contact', 'CHECK'

CREATE PROCEDURE USP_DropTableConstraints_2005
 @dbname VARCHAR(128),
 @schemaname VARCHAR(128),
 @tablename VARCHAR(128),
@constrainttype VARCHAR(128)
       
AS
-- USP_DropTableConstraints_2005 by sql solace
DECLARE  @sqlstring NVARCHAR(500)

SET @constrainttype = UPPER(LTRIM(RTRIM(@constrainttype)))

WHILE EXISTS (SELECT *
            FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE  CONSTRAINT_CATALOG = @dbname
            AND    TABLE_SCHEMA = @schemaname
            AND    TABLE_NAME = @tablename
     AND    CONSTRAINT_TYPE = @constrainttype)
BEGIN
  SELECT @sqlstring = 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] DROP CONSTRAINT ' + CONSTRAINT_NAME
  FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  WHERE  CONSTRAINT_CATALOG = @dbname
  AND    TABLE_SCHEMA = @schemaname
  AND    TABLE_NAME = @tablename
  AND    CONSTRAINT_TYPE = @constrainttype
  PRINT @sqlstring
  EXECUTE sp_executesql  @sqlstring
END
GO

USP_DropTableConstraints (SQL 2000 Version)

Drops Constraints for given table name (SQL 2000 Version) -

CREATE PROCEDURE USP_DropTableConstraints @tablename VARCHAR(128)    
AS

 SET NOCOUNT ON

 DECLARE  @constraintname VARCHAR(128),
          @sqlcmd         VARCHAR(1024)
                    
 DECLARE CONSTRAINTSCURSOR CURSOR  FOR
 SELECT NAME
 FROM   SYSOBJECTS
 WHERE  XTYPE IN ('C','F','PK','UQ',
                  'D')
        AND (STATUS & 64) = 0
        AND PARENT_OBJ = OBJECT_ID(@tablename)
                        
 -- nb : xtype refers to CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and DEFAULT constraints.

 OPEN CONSTRAINTSCURSOR

 FETCH NEXT FROM CONSTRAINTSCURSOR
 INTO @constraintname

 WHILE (@@FETCH_STATUS = 0)
   BEGIN
     SELECT @sqlcmd = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constraintname
     EXEC( @sqlcmd)
     FETCH NEXT FROM CONSTRAINTSCURSOR
     INTO @constraintname
   END
  
 CLOSE CONSTRAINTSCURSOR

 DEALLOCATE CONSTRAINTSCURSOR

 RETURN 0
       
GO