SQL Server Index Analysis

A query for the level of fragmentation of indexes in the current database, with usage since last restart of SQL.

I can’t take credit for this code, but it has proven useful, so I’m posting it here.

SQL Server: Example Naming Convention

An example of a SQL Server naming convention I have encountered:

Tables

  • tcTableName (e.g. tcAsset) for core tables, i.e. tables that contain regularly changing data and have insert/update/delete queries run against them.
  • trTableName (e.g. trAssetType) for reference tables, i.e. tables that contain static/unchanging data, usually used in reference to data in core tables.
  • tjTableName (e.g. tjUserRole) for join tables, i.e. tables that provide a many-to-many join between two other tables.

Columns

  • tableNameId (e.g. assetId) for primary keys.
  • foreignTableNameId (e.g. assetTypeId) for foreign keys.
  • name” for text fields in simple ID/Name tables, e.g. in trAssetType (assetTypeID int, name nvrachar(100)).
  • lowerUpperUpper (e.g. propertyEntranceStorey) for all other columns, with no particular convention on the column names other than the lowerUpperUpper case.

Views

  • vwTableNameSubset (e.g. vwAsset, vwWindowHistory, vwWindowHistoryLatest)

Other

  • Stored Procedures: uspTableAction (e.g. uspAssetDelete) (usp = User Stored Procedure – don’t use “sp” as its reserved for system stored procedures and SQL Server always checks the Master database for the SP first before checking the current database, meaning its bad for performance).
  • Functions: fnXXX
  • User-defined Data Types: uddtXXX
  • ID Columns: tableID / foreignTableID (e.g. assetID / assetTypeID)

SQL Server: Disable All Constraints

To disable all constraints on all tables in a database:

To enable all constraints on all tables in a database:

SQL Server: Drop all Foreign Keys that Reference a Table

Introduction

Before you drop a table in SQL Server, you must first drop all the foreign keys that reference that table. The following SQL scripts may help with this task.

Step 1:  Run the following to create a Stored Procedure that will perform the drop:

Step 2: Run the following to drop all FKs that reference the target table:

Step 3: Run the following to delete the Stored Procedure that was created in step 1:

SQL Server: Show all the FOREIGN KEYS that reference a TABLE

For SQL 2005+

Notes:

  • Change line (1) to specify the table name.

SQL Server: Show all the PRIMARY KEYS for a TABLE

For SQL Server 2005 +

Notes:

  • Change line (1) to specify the table name. Removing this line will list PRIMARY KEYS for all TABLES.

SQL: WHEN CASE NULL Fails

Imagine you want to find all the null values in a column in a database table (SQL Server).

x
1
2
NULL
4
5

Here is the SQL that performs the task as required:
[sourcecode language=”sql”]
SELECT x,
CASE x
WHEN NULL THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]
The result he expected was:

x result
1 no
2 no
NULL yes
4 no
5 no

But that isn’t what he got. His result was like this:

x result
1 no
2 no
NULL no
4 no
5 no

Curiously, if you run this:
[sourcecode language=”sql”]
SELECT x,
CASE x
WHEN 1 THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]
You do get this:

x result
1 yes
2 no
NULL no
4 no
5 no

So, why didn’t the original work?

  • Because NULL means unknown
  • Because NULL does not equal NULL
  • Because NULL is just weird, weird, weird

Anyway, here is the SQL that gives the expected answer:
[sourcecode language=”sql”]
SELECT x,
CASE
WHEN x IS NULL THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]

SQL Server: Case Sensitive Query

Someone at work sent this out to the team.
From LW:

Dear All, you might find this bit of code quite useful.  I was trying to find data within a table where the data was like lower case letters e.g a, b as opposed to A, B.  TSQL was not differentiating between lower and upper case so the following line forced it to do this substring(hradminblockcode,1,1) COLLATE SQL_Latin1_General_CP1_CS_AS

I used it in the following query

SELECT hrrefno FROM hrrepairmaster WHERE substring(hradminblockcode,1,1) COLLATE SQL_Latin1_General_CP1_CS_AS�
IN  (‘a’,’b’,’c’,’d’,’e’,’f’,’g’,’h’,’i’,’j’,’k’,’l’,’m’,’n’,’o’,’p’,’q’,’r’,’s’,’t’,’u’,’v’,’w’,’x’,’y’,’z’)

That might be useful. There is more information here: http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm