Bookmark : Listing Common SQL code smells

A great list of things we love to hate
Listing Common SQL code smells

Updating Statistics Manually

To update statistics manually -

exec sp_updatestats

Without parameters this updates all statistics in current database.

Run in all databases like this -

exec sp_MSForeachdb 'use [?];exec sp_updatestats'

Indexed views

sp_updatestats does not update indexed view stats

SQL login passwords and Pwdcompare

I'll spare the details but after a a little investigation, the HASHBYTES function cannot be used to generate a SQL login password. There are 2 undocumented functions pwdencrypt and pwdcompare that Management Studio uses for this.

If you were really interested in how to form the passwords, then the login migration script here will give more insight.

Link : How to transfer the logins and the passwords between instances of SQL Server 2005/8

The use of pwdcompare can be demonstrated as follows -

If I was daft enough to use my cat's name as a password (I'm not), the following query would return the username I had used the password with.

select name from sys.syslogins where pwdcompare('coco', password) = 1

Links:
cannot engineer sql password hash using HASHBYTES, have to use the undocumented stored procedure 'pwdencrypt'
SQL Server undocumented password hashing builtins: pwdcompare and pwdencrypt

Bookmark : WhoIsActive 10.0 / WhoIsActiveUI

Adam Machanic’s FREE WhoIsActive script has reached version 10.0

Download : WhoIsActive 10.0

and SchemaSolutions have produced a FREE SSMS plugin for it, called WhoIsActiveUI .

Download : WhoIsActiveUI

r

Bookmark : Project Lucy

Quest Software have launched Project Lucy to analyse SQL Profiler trace (.trc) files.

Get their opinion on your servers for free at https://www.projectlucy.com/

MCM Program changes

Most announcements from PASS reaching me via twitter are about Denali, but this news re; the MCM programme is exciting and makes it far more accessable to the majority.

Changes to the Microsoft Certified Master of SQL Server Program

SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos 

Amazon : Official SQL Server 2008 Certified Master Readiness List

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

Missing IDs in an integer sequence (the gap problem)

I encountered a missing integer ID problem today and rather than reinventing the wheel found the following script to deal with it.

Finding (all the) gaps in an identity column (or any integer based column for that matter) using Sql 2005

I have added OPTION (MAXRECURSION 0) to the end of the SELECT statement so that the CTE can be called as many times as necessary.

declare @i int;
 SELECT @i = MAX(pkid) FROM t1;
 WITH tmp (gapId) AS (
   SELECT DISTINCT a.pkid + 1
   FROM t1 a
   WHERE NOT EXISTS( SELECT * FROM t1 b
        WHERE b.pkid  = a.pkid + 1)
   AND a.pkid < @i
   UNION ALL
   SELECT a.gapId + 1
   FROM tmp a
   WHERE NOT EXISTS( SELECT * FROM t1 b
        WHERE b.pkid  = a.gapId + 1)
   AND a.gapId < @i
 )
 SELECT gapId
 FROM tmp
 ORDER BY gapId
OPTION (MAXRECURSION 0) ;

A good article on other sequence generation scripts is Creating a Number (Sequentially incrementing values) table in T-SQL

Bookmark : CPU Pressure

Nagaraj Venkatesan has provided a great article on finding CPU Pressure from a DMV query.

Link : Finding CPU Pressure using waitstats

Tool : CPU-Z

I just read a post of Brent Ozar’s about the SQL Server installation at Stackoverflow.com and how the power-saving feature of the Nehalem processor was causing some fun.

Anyway, he points to an excellent FREE tool, CPU-Z which is new to me.
As Brent's post details, compare the 'specification' of the processor with the 'core speed' it is achieving.

Simples...

Download CPU-Z : http://www.cpuid.com/