Archive for October 4th, 2008

Free SQL Server Ebooks/Resources

Free ebooks :) Grab them while they’re free. (BTW, I purchased Grant Fritchey’s ebook, now RedGate is offering it for free)

SQL Server 2008 MS Press Free Ebook
http://csna01.libredigital.com/?urss1q2we6

How to Become an Exceptional DBA
http://www.red-gate.com/products/sql_backup/offers/brad_exceptional_dba_ebook.htm

Best of SQL Server Central
https://www.red-gate.com/Dynamic/Downloads/DownloadForm.aspx?download=ebook1

Brad’s Sure Guide to SQL Server 2008
http://www.red-gate.com/specials/ToolbeltBradsure.htm?utm_source=ssc&utm_medium=email&utm_content=Bradsure-080925&utm_campaign=sqltoolbelt

Brad’s Sure DBA Checklist
http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/

RedGate’s SQL Server DBA Best Practices
http://www.simple-talk.com/content/file.ashx?file=1208

Dissecting SQL Server Execution Plans
http://www.red-gate.com/specials/Grant.htm

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Script to Drop/Recreate CHECK Constraints

   1: -- ===========================================================
   2: -- Check Constraints
   3: -- How to script out Check Constraints in SQL Server 2005
   4: -- ===========================================================
   5:
   6: -- view results in text, to make copying and pasting easier
   7: -- Drop Check Constraints
   8: SELECT
   9:     'ALTER TABLE  ' +
  10:     QuoteName(OBJECT_NAME(so.parent_obj)) +
  11:     CHAR(10) +
  12:     ' DROP CONSTRAINT ' +
  13:     QuoteName(CONSTRAINT_NAME)
  14: FROM
  15:     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
  16:     INNER JOIN sys.sysobjects so
  17:     ON cc.CONSTRAINT_NAME = so.[name]
  18:
  19: -- Recreate Check Constraints
  20: SELECT
  21:     'ALTER TABLE  ' +
  22:     QuoteName(OBJECT_NAME(so.parent_obj)) +
  23:     CHAR(10) +
  24:     ' ADD CONSTRAINT ' +
  25:     QuoteName(CONSTRAINT_NAME) +
  26:     ' CHECK ' +
  27:     CHECK_CLAUSE
  28: FROM
  29:     INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
  30:     INNER JOIN sys.sysobjects so
  31:     ON cc.CONSTRAINT_NAME = so.[name]

Here is a sample result:

ALTER TABLE  [ProductReview]
 DROP CONSTRAINT [CK_ProductReview_Rating]
ALTER TABLE  [ProductReview]
 ADD CONSTRAINT [CK_ProductReview_Rating] CHECK ([Rating]>=(1) AND [Rating]<=(5))
VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQL Server 2008 Jumpstart and other resources

Just a short compilation of SQL Server 2008 resources (white papers, ebooks, videos, blogs)

Blog Posts

Upgrading to SQL Server 2008
http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!1019.entry

10 Reasons Why SQL Server 2008 Is Going to Rock
http://angryhacker.com/blog/archive/2008/06/20/10-reasons-why-sql-server-2008-is-going-to-rock.aspx

The SQL Server 2008 Status Bar Custom Colors (why it’s not trivial!)
http://blog.jemm.net/2008/08/12/sql-server-2008-management-studio-tip-status-bar-custom-colors/

Notification Services Support in SQL Server 2008 :)
http://weblogs.sqlteam.com/joew/archive/2008/09/10/60708.aspx

Additional MS Training/Resources

SQL Server 2008 Jumpstart
http://sqlserver2008jumpstart.microsofttraining.com

Microsoft SQL Server 2008 – Learning Portal
http://www.microsoft.com/learning/sql/2008/default.mspx

Free SQL Server 2008 Ebook from MS Press
http://csna01.libredigital.com/?urss1q2we6

SQL Server 2008 Videos
http://go.microsoft.com/?linkid=9333556

SQL Server 2008 Virtual Labs
http://www.microsoft.com/sqlserver/2008/en/us/virtual-labs.aspx

Webcasts: 24 Hours of SQL Server 2008 for IT Professionals
http://go.microsoft.com/?linkid=9333557

Webcasts: 24 Hours of SQL Server 2008 for Developers
http://go.microsoft.com/?linkid=9333558

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Script to Drop/Recreate Default Constraints

Here is a basic script to drop and recreate default constraints in SQL Server.
   1: -- ===========================================================
   2: -- Default Constraints
   3: -- How to script out Default Constraints in SQL Server 2005
   4: -- ===========================================================
   5:
   6: -- view results in text, to make copying and pasting easier
   7: -- drop default constraints
   8: SELECT
   9:     'ALTER TABLE ' +
  10:     QuoteName(OBJECT_NAME(sc.id)) +
  11:     CHAR(10) +
  12:     ' DROP CONSTRAINT ' +
  13:     QuoteName(OBJECT_NAME(sc.cdefault))
  14: FROM
  15:     syscolumns sc
  16:     INNER JOIN
  17:     sysobjects as so on sc.cdefault = so.id
  18:     INNER JOIN
  19:     syscomments as sm on sc.cdefault = sm.id
  20: WHERE
  21:     OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1
  22:
  23: -- create default constraints
  24: SELECT
  25:     'ALTER TABLE ' +
  26:     QuoteName(OBJECT_NAME(sc.id)) +
  27:     'WITH NOCHECK ADD CONSTRAINT ' +
  28:     QuoteName(OBJECT_NAME(sc.cdefault))+
  29:     ' DEFAULT ' +
  30:     sm.text +
  31:     ' FOR ' + QuoteName(sc.name)
  32:     + CHAR(13)+CHAR(10)
  33: FROM
  34:     syscolumns sc
  35:     INNER JOIN
  36:     sysobjects as so on sc.cdefault = so.id
  37:     INNER JOIN
  38:     syscomments as sm on sc.cdefault = sm.id
  39: WHERE
  40:     OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1
Here is a sample result:
ALTER TABLE [SalesOrderHeader]
 DROP CONSTRAINT [DF_SalesOrderHeader_Status]

ALTER TABLE [PurchaseOrderHeader] WITH NOCHECK
 ADD CONSTRAINT [DF_PurchaseOrderHeader_Status] DEFAULT ((1)) FOR [Status]
VN:F [1.9.22_1171]
Rating: 10.0/10 (4 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

Script to Drop/Recreate Unique Indexes

Here’s a basic script to drop/recreate unique indexes in SQL Server 2005.  This includes scripting out INCLUDED columns.

   1: -- ===========================================================
   2: -- Unique Indexes 
   3: -- How to script out Unique Indexes in SQL Server 2005
   4: -- Set Results to Text so you can copy and paste the result
   5: -- ===========================================================
   6: SET NOCOUNT ON
   7:
   8: -- Drop Unique Indexes
   9: SELECT
  10:     DISTINCT
  11:     ' DROP INDEX ' +
  12:     QuoteName(i.name) +
  13:     ' ON ' +
  14:     QuoteName(OBJECT_NAME(i.object_id))
  15: FROM
  16:     sys.index_columns cc
  17:     INNER JOIN sys.indexes i ON cc.object_id = i.object_id
  18:     AND cc.index_id = i.index_id
  19:     INNER JOIN sys.objects so
  20:     ON i.object_id = so.object_id
  21: WHERE
  22:     is_primary_key = 0 AND
  23:     is_unique = 1 AND
  24:     so.type = 'U'
  25:
  26: -- Recreate Unique Indexes
  27: SELECT
  28:     DISTINCT
  29:     'CREATE UNIQUE ' +
  30:     CASE OBJECTPROPERTY(so.object_id, N'CnstIsClustKey')
  31:     WHEN 1 THEN 'CLUSTERED '
  32:     ELSE ''
  33:     END +
  34:     ' INDEX ' +
  35:     QuoteName(i.name) +
  36:     ' ON ' +
  37:     QuoteName(OBJECT_NAME(i.object_id))+
  38:     '('+ LEFT(UniqueCols.col, LEN(UniqueCols.col) -1)
  39:     +')' +
  40:     CASE ISNULL(LEN(IncludedCols.col), 0)
  41:         WHEN 0 THEN ''
  42:         ELSE ' INCLUDE (' + LEFT(IncludedCols.col, LEN(IncludedCols.col) -1) + ')'
  43:     END
  44: FROM
  45:     sys.index_columns cc
  46:     INNER JOIN sys.indexes i ON cc.object_id = i.object_id
  47:     AND cc.index_id = i.index_id
  48:     INNER JOIN sys.objects so
  49:     ON i.object_id = so.object_id
  50: CROSS APPLY
  51: (
  52:     SELECT
  53:         sc.name + ','
  54:     FROM
  55:         sys.index_columns idxcol
  56:         INNER JOIN sys.columns sc
  57:         ON idxcol.column_id=sc.column_id
  58:         AND idxcol.object_id=sc.object_id
  59:     WHERE
  60:         idxcol.object_id = i.object_id
  61:         AND i.index_id = idxcol.index_id
  62:         AND is_included_column = 0
  63:     FOR XML PATH('')
  64: )UniqueCols(col)
  65: CROSS APPLY
  66: (
  67:     SELECT
  68:         sc.name + ','
  69:     FROM
  70:         sys.index_columns idxcol
  71:         INNER JOIN sys.columns sc
  72:         ON idxcol.column_id=sc.column_id
  73:         AND idxcol.object_id=sc.object_id
  74:     WHERE
  75:         idxcol.object_id = i.object_id
  76:         AND i.index_id = idxcol.index_id
  77:         AND is_included_column = 1
  78:     FOR XML PATH('')
  79: )IncludedCols(col)
  80: WHERE
  81:     is_primary_key = 0 AND
  82:     is_unique = 1 AND
  83:     so.type = 'U'
  84:
  85: SET NOCOUNT OFF

Here is a sample result:

DROP INDEX [test_idx] ON [CompanyDepartment]
DROP INDEX [test_idx2] ON [Customer]

CREATE UNIQUE  INDEX [test_idx] ON [CompanyDepartment](Name,GroupName) INCLUDE (ModifiedDate)
CREATE UNIQUE  INDEX [test_idx2] ON [Customer](FirstName,MiddleName,CustomerID)
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`