Rebuild index, when does it complete?

I have recently worked on the client site (SQL Server 2000) to help on optimization. They have a very huge table (about 250 million rows) and perfom rebuild index every day at night. That jobs has beeen completed in two hours or so till this monday. The job was in 'executin status' for more than 10 hours and imagine what happened to the LOG file. My first thing was it would be really helpfull to have any system table that shows a percentage or whatever about the rebuild index process (something similar to what we have in SQL Server 2005 for some commands (BACKUP DATABASE) to see percent's complete )as I did not intend to KILL the process.Finally we got on and the process was completed. I suggested to the client rebuilding only heavy fragmented indexes especially if you have such huge table. Also , it is unnecessary to rebuild that table every day (it depends on your business requirements) ,so in their case , it is enough once a week.

In SQL Server 2005 for below operations you are able to see completion time or percent of completion.

DBCC CHECKDB
DBCC SHRINKDATABASE
DBCC SHRINKFILE
BACKUP DATABASE
ROLLBACK


SELECT TOP 2 start_time,
percent_complete ,estimated_completion_time
FROM sys.dm_exec_requests
ORDER by start_time desc

Shrink TempDB database

I have been recently worked for some client who has huge tempdb size (SQL Server 2000). As you probably know, you cannot reduce physical size of tempdb without restart MSSQLServices. Using the command DBCC SHRINKFILE to shrink the individual tempdb files you must run DBCC SHRINKFILE command while no other activity occurs in the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single user
mode.
So I would like to show some stepS we ended up. The client completely understands the problem cause tempdb database to be growing and I hope it is not short time solution.

1) Stop SQL Server. Open a command prompt, and then start SQL Server by
typing the following command:

"sqlservr -c -f" (remove the quotation)

The -c and -f parameters force SQL Server to start in a minimum
configuration mode with a tempdb size of 1 MB for the data file and 0.5 MB
for the log file.
Connect to SQL Server with Query Analyzer, and then issue the following
TSQL commands for the tempdb database files that you need to shrink

use tempdb
go

dbcc shrinkfile (tempdev, 'target size in MB') --for the primary data file
or
dbcc shrinkfile (templog, 'target size in MB')--for the log file

As MS says
"An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to
a size smaller than its original size. A limitation of DBCC SHRINKFILE is that you cannot make the database smaller than the size of the model database."

Non-updateable views

As we know from BOL
"Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition"

But I would like to share with you one method to prevent updating view that based on single table. Let's see the following

CREATE TABLE1 (col1 int,col2 int,col3 int,col4 int,col5 int)
GO
INSERT INTO t1 VALUES (1,1,11,2,12)
INSERT INTO t1 VALUES (8,10,15,25,55)
INSERT INTO t1 VALUES (9,1,11,2,5,81)
GO
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1,
col2+0 AS col2,
col3,
col4+0 AS col4,
col5
FROM T1
GO

--Usage

UPDATE v1 SET col1=100 where col2=10
The above statement is succeed, but what happened to the next one

UPDATE v1 SET col2=1000 where col3=15
--Update or insert of view or function 'v1' failed because it contains a derived or --constant field.

You cannot derived column ,as it should be referring to the update, not the view.
In SQL Server 2000, you can handle it using an INSTEAD OF trigger.

Reading Registry

Sometimes we need to read registry by using T-SQL . I would like to show you some scripts to do the job.

--Getting MDAC
EXEC master..xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\DataAccess',
N'Version'

--Getting an account SQL Server runs under
DECLARE @serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'

SELECT @Serviceaccount

---Getting SQL Server path's installation and the data path
DECLARE @DataPath nvarchar( 512 ) , @SQLPath nvarchar( 512 ) ,
@ToolsPath nvarchar( 512 )
EXECUTE sp_MSget_setup_paths @SQLPath OUT , @DataPath OUT;
PRINT 'SQLPath : ' + QUOTENAME( @SQLPath , '"');
PRINT 'DataPath : ' + QUOTENAME( @DataPath , '"');

How many times stored procedures have recompiled?

I have recently visited our client and we found out that He has a very high number of stored procedures that have recompiled. Certainly, we have observed some perfomance decreasing. Fortunately , the client has alredy upgraded to SQL Server 2005(SP2) and apart from running SQL Server Profile to indentify those stored procedures we came out with the following query.

It gives you the top 10 stored procedures that have been recompiled.

select *
from sys.dm_exec_query_optimizer_info

select top 10
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc

Note that the plan_generation_num indicates the number of times the stored procedure has recompiled. Very cool feature.

RESTORE LOG file and WITH FILE behaviour

Recently I have been visited one of the our clients and have been asked the following question.
"What if I have 3 fileid within a backup file and when restore the log being done, I do not specify WITH FILE option,so do I get latest Log's data?". To answer the question I decide to conduct some test script.


CREATE DATABASE demo
GO
ALTER DATABASE demo SET RECOVERY FULL
GO
CREATE TABLE demo..t1(c1 INT NOT NULL PRIMARY KEY)
--insert one row
insert demo..t1 VALUES (1)
--BACKUP the data
BACKUP DATABASE demo TO DISK = 'c:\temp\demo.bak' WITH INIT
GO
insert demo..t1 VALUES (2)
--start BACKUP log data
BACKUP log demo TO DISK = 'c:\temp\demo_log.bak' WITH INIT
GO
insert demo..t1 VALUES (3)
--- BACKUP log FILE at this time WITH FILE =2
BACKUP log demo TO DISK = 'c:\temp\demo_log.bak' WITH NOINIT
GO
insert demo..t1 VALUES (4)
--- BACKUP log FILE at this time WITH FILE =3
BACKUP log demo TO DISK = 'c:\temp\demo_log.bak' WITH NOINIT

--Lets do RESTORE and see what is going on
RESTORE DATABASE demo FROM DISK = 'c:\temp\demo.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG demo FROM DISK = 'c:\temp\demo_log.bak' WITH FILE = 1, NORECOVERY
RESTORE LOG demo FROM DISK = 'c:\temp\demo_log.bak' WITH RECOVERY ----Do you expect the last one?

GO
SELECT * FROM demo..t1
c1
-----------
1
2
It did not return rows 3 and 4.

So that means if you DO NOT specify WITH FILE in RESTORE LOG file you DO NOT get latest FILEID but only the first one.

Insert data into a Text File

As you propably know that MS introduced in SQL Server 2005 a new command utility called SQLCMD. In this article I would like to show the difference between SQLCMD and "old fashioned" OSQL in terms of formating the text file's output.

If you run these two statements and look at the files , you'll see that SQL Server inserts three dotted lines in myoutput2.txt, not so good for reading. Opposite, in myoutput1.txt you see very good reading format.

EXEC master..xp_cmdshell 'SQLCMD -S URID\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput1.txt', no_output
EXEC master..xp_cmdshell 'OSQL -S URID\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput2.txt', no_output

Despite that SQL Server 2005 supports OSQL utility, I strongly recommend you using SQLCMD.

Disable all Foreign Keys

Recently I was told to clear up all user tables in the client's database SQL Server 2000 SP3.There are lots of contsrtaints and for this purpose we would like to disable them in order to delete all data. Try it on Northwind database before using on production. If you want to enable constraints ,simple replace NOCHECK with CHECK.

DECLARE @TableName nvarchar(257),
@ForeignKeyConstraintName sysname,
@SQLStatement nvarchar(4000)

DECLARE TableList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS TableName,
QUOTENAME(CONSTRAINT_NAME) AS ForeignKeyConstraintName
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = 'FOREIGN KEY' AND
OBJECTPROPERTY
(
OBJECT_ID
(
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
),
'IsMSShipped') = 0

OPEN TableList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TableList INTO
@TableName,
@ForeignKeyConstraintName
IF @@FETCH_STATUS = -1 BREAK
SET @SQLStatement =
N'ALTER TABLE ' +
@TableName +
N' NOCHECK CONSTRAINT ' +
@ForeignKeyConstraintName
RAISERROR (@SQLStatement, 0, 1) WITH NOWAIT
EXEC sp_executesql @SQLStatement
END
CLOSE TableList
DEALLOCATE TableList
GO

Getting an index's information

I was asked by one of the client to get all info about the indexes i.e name of the index , column's name, table's name etc. Please take a look at following script to return the data for SQL Server 2000.
use pubs

SELECT tbl = object_name(i.id), i.name as index_name, c.name as column_name,
isunique = indexproperty(i.id, i.name, 'IsUnique'),
isclustered = indexproperty(i.id, i.name, 'IsClustered'),
constrtype = CASE o.type
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'UQ' THEN 'UNIQUE'
END
FROM sysindexes i
JOIN syscolumns c on i.id = c.id
JOIN sysindexkeys k on i.id = k.id
and i.indid = k.indid
and c.colid = k.colid
LEFT JOIN sysobjects o ON o.name = i.name
AND o.xtype in ('PK', 'UQ')
AND o.parent_obj = i.id
WHERE indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
AND objectproperty(i.id,'IsMSShipped')=0
ORDER BY tbl, i.name, k.keyno

For SQL Server 2005 I use the script written by Kalen Delaney.Remember there is new type of index called INCLUDE.

CREATE VIEW get_index_columns
AS
SELECT object_name(ic.object_id) as object_name , index_name = i.name,
'column' = c.name,
'column usage' = CASE ic.is_included_column
WHEN 0 then 'KEY'
ELSE 'INCLUDED'
END
FROM sys.index_columns ic JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id

After creating the view, you can select from it, and it will give you the
KEY columns and the INCLUDED columns in all the indexes in all the tables.
Or, you can add a WHERE clause for your own table or index:

SELECT * FROM get_index_columns
WHERE object_name = 'mytable'

Foreign Key dependency

Some time ago I was asked to truncate lots of tables in client's database.As such,all those tables have FK and PK dependency. You get the error like that if you run TRUNCATE TABLE Categories in Nortwind database as an example.
--Cannot truncate table 'Categories' because it is being referenced by a FOREIGN KEY constraint.

So , there is no such great problem as you know the database structure and can easily indetify parent-child tables. But what if you don't?
I'd like to show the function (sadly, I don't remember by whom it is written) that returns the tables ordered by its level. Level 0 means that it is refernced table and should be truncated last.

CREATE VIEW VFKs
AS

SELECT
FK.TABLE_SCHEMA AS child_table_schema,
FK.TABLE_NAME AS child_table_name,
PK.TABLE_SCHEMA AS parent_table_schema,
PK.TABLE_NAME AS parent_table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON RC.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
GO

CREATE FUNCTION dbo.fn_get_tree_of_tables()
RETURNS @tree TABLE
(
table_schema SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
lvl INT NOT NULL,
PRIMARY KEY(table_schema, table_name)
)
AS
BEGIN

DECLARE @lvl AS INT
SET @lvl = 0

-- top level tables
INSERT INTO @tree
SELECT DISTINCT
parent_table_schema,
parent_table_name,
@lvl
FROM VFKs AS P
WHERE NOT EXISTS
(SELECT *
FROM VFKs AS C
WHERE C.child_table_schema = P.parent_table_schema
AND C.child_table_name = P.parent_table_name)

WHILE @@rowcount > 0
BEGIN

SET @lvl = @lvl + 1

-- non top level tables
INSERT INTO @tree
SELECT DISTINCT
child_table_schema,
child_table_name,
@lvl
FROM VFKs AS C
JOIN @tree AS P
ON P.table_schema = C.parent_table_schema
AND P.table_name = C.parent_table_name
AND lvl = @lvl - 1
WHERE NOT EXISTS
(SELECT *
FROM @tree AS T
WHERE T.table_schema = C.child_table_schema
AND T.table_name = C.child_table_name)

END

SET @lvl = 0

-- tables with no fks
INSERT INTO @tree
SELECT TABLE_SCHEMA, TABLE_NAME, @lvl
FROM INFORMATION_SCHEMA.TABLES AS TB
WHERE NOT EXISTS(
SELECT *
FROM @tree AS TR
WHERE TB.TABLE_SCHEMA = TR.table_schema
AND TB.TABLE_NAME = TR.table_name)
AND
TB.TABLE_TYPE = 'BASE TABLE'

RETURN

END
GO

Here's the results of an invocation of the function in Northwind:

SELECT * FROM dbo.fn_get_tree_of_tables() AS T
ORDER BY lvl

table_schema table_name lvl
------------ ----------------------------------- ---
dbo Categories 0
dbo CustomerDemographics 0
dbo Customers 0
dbo Employees 0
dbo Region 0
dbo Shippers 0
dbo Suppliers 0
dbo Products 1
dbo Territories 1
dbo Orders 1
dbo CustomerCustomerDemo 1
dbo Order Details 2
dbo EmployeeTerritories 2

Object's modified date

There are lots of businesses have already upgraded to SQL Server 2005.At this time I'd like to take a look at great feature that we have in SQL Server 2005. I have been asked so many times how to know WHEN does the object change? It is a headache in SQL Server 2000. Fortunately, now we can answer that question.

Let's say the user changed number of stored procedures.Using the following statement we can easily get the data.

SELECT name,modify_date FROM sys.procedures
ORDER BY modify_date desc

But let's take it a little bit futher. I'd like to know when does user update the table last time?

CREATE TABLE dbo.uri(c INT NOT NULL PRIMARY KEY)
GO
INSERT INTOdbo.uri VALUES(10)
--we got one entry

SELECT object_name(object_id) AS name ,last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id( 'demo' )

---try to change the data
UPDATE dbo.uri SET c =50 WHERE c =10

--you'll see that last_user_update is changed

SELECT object_name(object_id) AS name ,last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id( 'demo' )

So ,you can play with these Dynamic Management Views to obtain more info.

Columns List of Views

One of myclient asked me nice question about getting columns list for views. Well, please take a look at following solution I came up with.

The first view just holds the data about view and their columns and as you can see it is not easy to read it at all.

CREATE VIEW v_getcolumns
AS
SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME ,COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.COLUMNS
ON INFORMATION_SCHEMA.TABLES.TABLE_NAME=INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA) + '.' +
QUOTENAME(INFORMATION_SCHEMA.TABLES.TABLE_NAME)
), 'IsMSShipped') = 0


The final step I build a crosstab to contacanate the names of the columns per view based on count.I ened upi with 10 columns per view as maximum number, you can modify it for your needs.The cool thing is if you specify '*' in SELECT statement within a view and running the following query you get all columns of the table.

SELECT TABLE_NAME ,MAX(CASE WHEN rnk = 1 then COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 2 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 3 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 4 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 5 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 6 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 7 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 8 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 9 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 10 THEN ', ' + COLUMN_NAME ELSE '' end) AS columns
FROM
(
SELECT A.TABLE_NAME ,A.COLUMN_NAME,COUNT(*)rnk
FROM v_getcolumns A, v_getcolumns B
WHERE A.TABLE_NAME=B.TABLE_NAME AND
A.COLUMN_NAME>=B.COLUMN_NAME
GROUP BY a.TABLE_NAME ,a.COLUMN_NAME
) AS der
GROUP BY TABLE_NAME

T-SQL and CLR types do not match

As you know in SQL Server 2005 you can reference to .NET classes doing some operations that in T-SQL is realy hard to perform. For example if you need to format very long and 'complicated' string value , so by using .NET String method you can easily achive it. But I'd like to show you another example actually posted by Itzik Ben-Gan on his website ,so I just modified his C# code to VB.NET that is invoking the ToString method of the SqlDateTime datatype with a format string.

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Public Class Valid
"<"SqlFunction(DataAccess:=DataAccessKind.Read, IsDeterministic:=True)">" _
Public Shared Function fn_format_datetime(ByVal dt As SqlTypes.SqlDateTime, _
ByVal formatstring As SqlTypes.SqlString) As String
If dt.IsNull Or formatstring.IsNull Then
Return formatstring.IsNull()
Else
Return dt.Value.ToString(formatstring.Value)
End If

End Function

End Class

I assume you are familiar with .NET technology , so I build a solution and try to reference to this function within my database.I created ASSEMBLY without problem but when I tried to create a function I got the error "CREATE FUNCTION for "fn_format_datetime1" failed because T-SQL and CLR types for return value do not match."

CREATE ASSEMBLY dateformat1 FROM 'C:\dateformat1 .dll'
with permission_set = SAFE
CREATE FUNCTION fn_format_datetime(@dt as datetime,
@formatstring as VARCHAR(100))RETURNS VARCHAR(100)
AS EXTERNAL NAME dateformat1.[dateformat1.classname].fn_format_datetime

As you see T-SQL datatype VARCHAR(n) does not match .NET STRING class. I should use NVARCHAR(n) datatype to get it working, did not know it.

CREATE ASSEMBLY dateformat1 FROM 'C:\dateformat1 .dll'
with permission_set = SAFE
CREATE FUNCTION fn_format_datetime(@dt as datetime,
@formatstring as NVARCHAR(4000))RETURNS NVARCHAR(4000)
AS EXTERNAL NAME dateformat1.[dateformat1.classname].fn_format_datetime

--Usage
SELECT
dbo.fn_format_datetime(GETDATE(), 'yyyyMMdd'),
dbo.fn_format_datetime(GETDATE(), 'yyyymmdd');

Trigger's definition

I have had a clinet who wanted to script out the source of triggers on database level, so I'm talking about SQL Server 2005. Well as you probably know you can easily script the objects by using Tasks ---> Generate Scripts.. and moreover if you installed SP2 you will be able to script the object per file.

I would like to show you more simple way. You join two system tables on object_id column where sys.sql_modules table has a definition column which my client wanted to get.The WHERE condition restricts the ouptupt for triggers that were designed on database level.

SELECT definition FROM sys.sql_modules sm
INNER JOIN sys.triggers st ON
sm.object_id=st.object_id
WHERE parent_class_desc ='DATABASE'

Is Linked Server available?

In SQL Server 2000 we have been checking an availability of linked server by using
xp_cmdshell along with 'ping' command.It worked fine but what does SQL Server 2005 propose to us?
There is a system stored procedure (sp_testlinkedserver )that accepts name of linked server as a parameter and returns the status of server.

Take a look at the script below.

DECLARE @srvr NVARCHAR(128), @statusval INT;
SET @srvr = 'Linked_Server';
BEGIN TRY
EXEC @statusval = sp_testlinkedserver @srvr;
END TRY
BEGIN CATCH
SET @statusval = SIGN(@@ERROR);
END CATCH;
IF @statusval <> 0
RAISERROR('Unable to connect to linked server. Try later!', 16, 2 );

Enabling AWE on Cluster environment

I know many people ask this question. Recently I had a client wants me to enable AWE on the cluster which has two nodes (Active/Passive). He had only one instance of SQL Server 2000 (SP4) and 8 GB RAM and /3GB in the Boot.INI.

1)
Add /PAE parameter to Boot.INI file. PAE is the added ability of the IA32 processor to address more than 4 GB of physical memory.
2)
You need to install hotfix on SQL Server instance from Microsoft (http://support.microsoft.com/kb/899761) that fixs the bug "Not all memory is available when AWE is enabled".
3)
Make sure that domain account SQL Server is running under is added to "Lock pages in memory" local group policy.
4)
Finally we configured 6 GB for SQL Server as it was business requirements.

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE

5)Restart OS

Note: we need to run those steps on Active node where the SQL Server instance is running on. When it failed over but the binaries are loaded on all
nodes.

Linked servers and an Identity property

Someone in sqlserver.programming group asked nice question about getting an IDENTITY from the linked server. I came up with the following solution. I assume that there is a table on the linked server that has one column defined as indentity property.

CREATE FUNCTION dbo.fn_get_remoteidentity()
RETURNS INT
AS
BEGIN
RETURN(
SELECT row_id
FROM OPENQUERY(
[put here your server name],
'SET NOCOUNT ON;
INSERT INTO dbname..usertable DEFAULT VALUES
ROLLBACK;
SELECT SCOPE_IDENTITY() AS row_id;') AS Der)
END
Another question was how to insert an INDENTITY property in the table on remote(linked) server?
Well in that case you will need to build dynamic SQL along with SET IDENTITY_INSERT
command.

DECLARE @sql NVARCHAR(400)
SELECT @sql = '
CREATE TABLE test(id INT IDENTITY(1,1))
SET IDENTITY_INSERT test ON
INSERT test DEFAULT VALUES
SET IDENTITY_INSERT test OFF
SELECT id FROM test'
--Usage
EXEC [LinkedServer].master.dbo.sp_executesql @sql

Global stored procedure

I have my doubts that many of us have been used this system stored procedure(sp_MS_marksystemobject) to resolve the problems. But it exists and gives you an ability to return the data in the scope of selected database. Well, across number of databases having one table with the same structure and being in the scope of desired database you'll return the data (by using the following stored procedure) from the table located in desired database.To make the stored procedure to recognize the 'desired' data we use sp_MS_marksystemobject system stored procedure. BTW, it works as well as on SQL Server 2000 and on SQL Server 2005(SP2).

USE master
CREATE TABLE test (c1 VARCHAR(50))
INSERT test VALUES('master')
go
CREATE PROC sp_test AS
SELECT * FROM test
GO
USE northwind
CREATE TABLE test (c1 VARCHAR(50))
INSERT test VALUES('northwind')
USE pubs
CREATE TABLE test(c1 VARCHAR(50))
INSERT test VALUES('pubs')
USE pubs
EXEC sp_test --returns 'master'
USE master
EXEC sp_MS_marksystemobject sp_test
USE pubs
EXEC sp_test --returns 'pubs'
USE northwind
EXEC sp_test --returns 'northwind'

Find specific data type of the column

Recently i was told to return tables which have FLOAT datatype defined on columns.The version of the product was SQL Server 2000 ,however it will work on SQL Server 2005 as well, so take a look at very very simple script.

SELECT columns.TABLE_NAME ,COLUMN_NAME
FROM information_schema.columns columns
JOIN information_schema.tables tables
ON tables.table_name = columns.table_name
WHERE tables.table_type = 'base table'
AND columns.data_type = 'float'
ORDER BY columns.table_name, columns.column_name

Can we look into LOG file?

In SQL Server 2005 with fn_dblog function you can get more info about what's happening in SQL Server. MS has added a new data like 'partitionid' column for example.

Let us do some testing

USE Demo
--create a table for testing
CREATE TABLE dbo.Test1 (c INT)
--populate the table with data

WITH numbers AS
(SELECT 1 as Num
UNION ALL
SELECT Num + 1 FROM numbers WHERE Num <10)
INSERT INTO dbo.Test1 SELECT Num FROM numbers OPTION(Maxrecursion 10)

--delete the data
DELETE FROM dbo.Test1

In order to identify how many 'DELETE' operation and when does it start? Run the followinq query.

SELECT [Transaction Name],[begin time FROM ::fn_dblog( NULL,NULL)WHERE [Transaction Name] ='DELETE'

Ok, the next step I'd like to know how long does DELETION take? So we have [end time column] for this purpose. We need to write the below query which gets little complicated.

WITH cte_delete
AS
(
SELECT [Transaction Name],[begin time],[end time]
FROM ::fn_dblog( NULL,NULL)d
WHERE [begin time] IS NOT NULL OR [end time]IS NOT NULL
) ,cte_d
AS
(
SELECT [Transaction Name],[begin time],(SELECT MIN([end time]) FROM cte_delete C
WHERE cte_delete.[begin time]<=C.[end time])AS [end time]
FROM cte_delete WHERE [Transaction Name] ='DELETE' OR
[Transaction Name] IS NULL
)
SELECT * FROM cte_d
WHERE [begin time] IS NOT NULL OR [end time]IS NOT NULL

Also take a look at 'Operation' column.

SELECT Operation,AllocUnitName,[Transaction Name],[begin time],[end time]
FROM ::fn_dblog( NULL,NULL)
WHERE Operation ='LOP_DELETE_ROWS' OR [Transaction Name]='DELETE'

To identify which tables are being hit by DELETE operation.

SELECT AllocUnitName, COUNT([Current LSN])
FROM ::fn_dblog(NULL,NULL )
WHERE Operation = N'LOP_DELETE_ROWS'
GROUP BY AllocUnitName

String manipulation (extracting)

I probably should create some group of articles for string manuipulation.At this time I'd like to show you an example to extract portion of text prior/after to some charcter/symbol.An idea is simple to iterate through string and return the text prior (in my example) to 'N' appearance of specified symbol.


CREATE FUNCTION dbo.udf_Extract(
@target varchar(8000),
@string varchar(8000),
@i int
) RETURNS varchar(8000) AS BEGIN
DECLARE @pos int
SET @pos = 0
WHILE @i > 0 BEGIN
SET @pos = CHARINDEX(@target,@string,@pos+1)
SET @i = @i - 1
IF @pos = 0 RETURN '0'
END
RETURN SUBSTRING (@string,1,@pos-1)
END
GO

--Usage
SELECT dbo.udf_Extract('@','hjdhjdhj@hjjsd@hjdfhdfj@jjj,3)

Which tables have a trigger/s?

I have been recently helping my fried on very large database ,which has hundred od tables to determine which table has a trigger. So I came out with the below script that may help others to identify triggers define on the tables.

--SQL Server 2000(SP3)

SELECT
name
, CASE WHEN instrig <> 0 then OBJECT_NAME (instrig) ELSE 'None' END as
'Insert Trigger'
, CASE WHEN updtrig <> 0 then OBJECT_NAME (updtrig) ELSE 'None' END as
'Update Trigger'
, CASE WHEN deltrig <> 0 then OBJECT_NAME (deltrig) ELSE 'None' END as
'Delete Trigger'
FROM
sysobjects
WHERE
type = 'U'
AND
( instrig <> 0
OR
updtrig <> 0
OR
deltrig <> 0
)

What's the version of SQL Server?

I have seen this question (see post's subject) so many times in newsgroup asking people, so I decided to post some usefull info about SQL Server by using SERVERPROPERTY command.

SELECT @@VERSION AS [Server Information]

SELECT SERVERPROPERTY('ServerName') AS [ServerName]
SELECT SERVERPROPERTY('MachineName') AS [MachineName]
SELECT SERVERPROPERTY('InstanceName') AS [InstanceName]

SELECT SERVERPROPERTY('productversion') AS [ProductVersion]
SELECT SERVERPROPERTY('ProductLevel') AS [ProductLevel]
SELECT SERVERPROPERTY('Edition') AS [Edition]

SELECT SERVERPROPERTY('Collation') AS [Collation]
SELECT SERVERPROPERTY('IsClustered') AS [IsClustered]
SELECT SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled]
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS
[IsIntegratedSecurityOnly]
SELECT SERVERPROPERTY('LicenseType') AS [LicenseType]
SELECT SERVERPROPERTY('NumLicenses') AS [NumLicenses]

Mixed or Windows Authentication Only

Ok, at this time I would to discuss about the one but realy painful issue we have to face where we use SQL Authentication (Mixed).
Consider the following situation. You are using SQL Server (I speak for SQL Server 2000 SP4) Authentication(Mixed).
You also have a database and user within which is mapped to the SQL login. Now , you have been told to move the database on the new server. So you created a backup of the database , copied it to the new server as well as create a SQL Login with the same name as you had on the old one. Ok, now it is time to perform restore database, so everything went just fine but when theh users tried to connnect to the database (through application) on the new server they get the error. What happened? The thing is that SQL Server is 'losing' SIDs that mapped between a login and user when you created a new SQL Login on the new server (master..syslogins) and a database user (dbname..sysuser)that you brought within a database. So we have sp_change_users_login system stored procedure that takes care of re-mapping SID's.

However, that DOES NOT happen if you use Windows Authentication Only.That means SQL Server keeps SID's realtionship between Windows Login and User database although you move the database on the new server.

Just one more argument to use Windows Authentication Only.

In SQL Server 2005 SP2 MS has introduced some greate features on subject.
http://blogs.msdn.com/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx.

Drop all indexes

Well, sometimes we have to do that. A large INSERT statement may run faster for instance. The below script just generate DROP command of all indexes in current dataabse. You can add a filter to specify a table name for example.(SQL Server 2000)

SELECT
'DROP INDEX ' +
QUOTENAME(USER_NAME(o.uid)) +
'.' +
QUOTENAME(o.name) +
'.' +
QUOTENAME(i.name)
FROM sysobjects o
JOIN sysindexes i ON
i.id = o.id
WHERE i.indid BETWEEN 1 AND 254 AND
INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 0 AND
INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 0 AND
OBJECTPROPERTY(o.id, 'IsMSShipped') = 0

Some third parties break DIFFERENTIAL backup chains

I'd like to share some things I have seen on my client's machine. He has a full backup database set up once a night and every four hours differential back during the work day. But at the same time ( it should be done at night) the network administrator has ran tird party software backup to copy .MDF/LDF files. When we started to restore database it throws the error that SQL Server cannot apply this bakup file to the full backup file created at night. We did lots of testing on the developing machine and did not het the error. And only when we asked the net.admin not to run this software then we were able to restore the database succefully.

Note, I have not seen that doing LOG files backup does break the chains.

So please do lots of testing before you find your self without properly created disaster recovery strategy.


I found that MS has this KB explaining the behaviour.
http://support.microsoft.com/kb/903643.

Output clause and Auditing of data

There is very common scenario to auditing of data in a table using triggers. In this case, the trigger uses information from the inserted and updated tables to add rows into the audit tables. MS has introduced a new OUTPUT clause in SQL Server 2005 to audit data instead of using triggers to insert rows into an audit table.

CREATE TABLE test (col INT NOT NULL);
CREATE TABLE test_audit ( old_col INT NOT NULL, new_col INT NULL);
INSERT INTO test (col) values( 1 );
INSERT INTO test (col) values( 2 );

UPDATE test
SET col = col + 1
OUTPUT deleted.col ,inserted.col into test_audit
WHERE col = 1;

DELETE FROM test
OUTPUT deleted.col, NULL into test_audit
WHERE col = 2;

SELECT * FROM test
SELECT * FROM test_audit;

Detaching and Attaching database

At this time I'd like to share some scripts that used undocumented (sorry) but very popular among DBA's sp_MSforeachdb stored procedure. If you have hundred of databases on your server and you was told to move those databases to another server the quick way to do the job is performing the following scripts. First of all we detach all user databases from the server and the second query will output an attach
script. The thing is that the second query will output the script that attach the database that has more than one LOG file.

I'm sure that you have backuped the user databases before performing such tasks.

--Detaching
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''master'', ''msdb'', ''tempdb'')
BEGIN
SELECT ''EXEC sp_detach_db ''''?'''',''''true''''''
END'


--Attaching
EXEC sp_msforeachdb
'SELECT ''EXEC sp_attach_db ''''?'''',
'''''' + RTRIM(filename) + '''''',''
FROM ?..sysfiles
WHERE fileid = (SELECT MIN(fileid) FROM ?..sysfiles)
UNION ALL
SELECT '''''''' + RTRIM(filename) + ''''''''
FROM ?..sysfiles
WHERE fileid > (SELECT MIN(fileid) FROM ?..sysfiles) AND
fileid < (SELECT MAX(fileid) FROM ?..sysfiles)
UNION ALL
SELECT '''''''' + RTRIM(filename) + ''''''''
FROM ?..sysfiles
WHERE fileid = (SELECT MAX(fileid) FROM ?..sysfiles)'

String manipulation (searching)

Recently I have seen some post in the news group regarding seraching a string in the table or even part of string. Yes , there are lots of resources in the internet how to do that. I would like to shere some ideas also here.


Consider the following structure. We need to find in col1 a part of string. Let us find occurence of '23'.

CREATE TABLE #Test
(
col INT,
col1 VARCHAR(100)
)
INSERT INTO #Test VALUES (1,'12,23,44,56')
INSERT INTO #Test VALUES (2,'26,45,22,32')
INSERT INTO #Test VALUES (3,'45,22,34,23')

DECLARE @st VARCHAR(10)
SET @st='23'
SELECT *
FROM #test
WHERE ','+col1+',' LIKE '%,'+@st+',%'


Now, take a look at following table
CREATE TABLE #Test (contact VARCHAR(20) PRIMARY KEY)

INSERT INTO #Test VALUES ('Bill Clinton')
INSERT INTO #Test VALUES ('George W. Bush')
INSERT INTO #Test VALUES ('John F Kennedy')
INSERT INTO #Test VALUES ('Smith')

1)
SELECT contact
FROM #Test
WHERE contact LIKE '%[A-Z] [A-Z][^A-Z]%[A-Z]'
----
2)
SELECT contact
FROM #Test
WHERE contact LIKE '% _ %' or contact LIKE '% _. %'

The first one returns all people that have a middle name. The second one returns
people who has not '.' in this name after middle name.

Script Objects SS2K

Well, there are still lots of people/businesses use SQL Server 2000 I'd like to put the following script using SQL DMO object library. You have to open a new project with VB6 and put the code in the new form.
The cool thing is that you get the script without default COLLATION to be specify.
See if you need to run the script on another server/s or database/s that have different collation.

Also, Tibor Karaszi has great info at his web site
http://www.karaszi.com/SQLServer/info_generate_script.asp


Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)


Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj

Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")

Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
' No collation to be specify
Const SQLDMOScript2_NoCollation As Long = 8388608

' We need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation

' Connect to server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")

' Script User Defined Data Types
For Each genObj In db.UserDefinedDatatypes
genObj.Script intOptions, StrFilePath
Next

' Script Tables and Triggers, ignoring system
' tables and system generated triggers
For Each genObj In db.Tables
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath,,SQLDMOScript2_NoCollation
For Each objTrigger In genObj.Triggers
If objTrigger.SystemObject = False Then
objTrigger.Script intOptions, StrFilePath
End If
Next
End If
Next

' Script Rules
For Each genObj In db.Rules
genObj.Script intOptions, StrFilePath
Next

' Script Defaults
For Each genObj In db.Defaults
genObj.Script intOptions, StrFilePath
Next

' Script Sprocs, ignoring system sprocs
For Each genObj In db.StoredProcedures
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next

' Script Views, ignoring system views and informational schemas
For Each genObj In db.Views
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next


End Sub

To call the procedure save the module and supply the parameters.
Call ScriptDB("UserName","Password","DatabaseName","C:\DBScript.SQL")

Getting the value of Constraint

Recently I was asked by my colleague to return tha actual value of CHECK constaint that he has defined on the table.
I think it can be usefull for some reports that we've done on the database.
Tested on SQL Server 2000(SP3a) and SQL Server 2005(SP2a)

CREATE TABLE Test
(
col INT,
coldatetime DATETIME
)

ALTER TABLE Test
ADD CONSTRAINT coldatetime_check
CHECK ( coldatetime > '20070101')

SELECT text FROM syscomments
WHERE id =OBJECT_ID('coldatetime_check')

UNIQUE Constraint vs NULL's

This is another well known question get asked , how to insert more than one NULL value in the column which has an unique constraint defined on?

Ok, you can create a view WITH SCHEMABINDING that issues SELECT column FROM Table WHERE column IS NOT NULL. Then you need to create an unique index on view (column).

Another method I learned from Steve Kass a long time ago and would like to publish here is that we create a computed column called nullbuster that based on primary key and another column. Now that we have this column so we create a UNIQUE Constraint on two columns. See how it works.

CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)

INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls

Sort techniques

I have seen very often people ask for sorting alphabetically and moreover if it has a number as a part of string that also should be sorted .
Take a look at this example. I used PATINDEX function to find a position at which 'numeric' part begins along with SUBSTRING started from first postion thrue the whole string.RIGHT function just adds '00000' to string , so if it has '0' at the beginnig then it will be sorted by ascending for example.

CREATE TABLE #Test
(
col VARCHAR(10)not null primary key
)
INSERT INTO #Test VALUES ('A104')
INSERT INTO #Test VALUES ('A152')
INSERT INTO #Test VALUES ('A1010')
INSERT INTO #Test VALUES ('A15')
INSERT INTO #Test VALUES ('A17')
INSERT INTO #Test VALUES ('AA130')
INSERT INTO #Test VALUES ('B11')
INSERT INTO #Test VALUES ('B30')

SELECT * FROM #test ORDER BY
SUBSTRING (col, 0,PATINDEX('%[0-9]%',col))+RIGHT('00000' + SUBSTRING (col,
PATINDEX('%[0-9]%',col) , LEN(col)),5)

Column Description

I have seen many people asking how can I scipt out column's description. Well, there is fn_listextendedproperty system function , you can read about in the BOL.

Here is another example how to return column desciption.

--SQL Server 2000
SELECT s2.id, s1.name,
( SELECT s3.value
FROM sysproperties s3
WHERE s3.id = s1.id
AND s3.smallid = s1.colid ) AS "col desc"
FROM syscolumns s1
INNER JOIN sysobjects s2
ON s1.id = s2.id
WHERE s2.name = 'tablename';

I know that sysproperties is undocumented and it is no longer exists in SQL Server 2005.
We can use either the system TVF fn_listextendedproperty or
SELECT value
FROM sys.extended_properties
WHERE major_id=OBJECT_ID('dbo.tbl')

Try ..Catch constructs

There are lots of companies,business have already upgraded to SQL Server 2005 ,definetly it makes sense because MS has introduced lots of new featuers. One of the many is how we can handle errors in SQL Server 2005


This procedure can be executed in your CATCH block.

CREATE PROCEDURE ErrorDetails
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GO


Connect to Object Explorer and execute the following code

BEGIN TRY
SELECT GETDATE()
SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
EXEC ErrorDetails
END CATCH;

Great thing that we can catch more errors by usig TRY ...CATCH block and what's more important to get an error message out.

Find gaps

Well ,there are many questions in the newgroup I have seen with this request
You can have a table with sequential numbers and LEFT join with your table to find the gaps.
The following techniques I collected from Steve Kass,Joe Celko, Itzik Ben-Gan.

CREATE TABLE X (C INT NOT NULL PRIMARY KEY)
INSERT X SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION ALL
SELECT 10

SELECT * FROM x
SELECT
c+n FROM x, (
SELECT 0 n UNION ALL SELECT 1
) T
GROUP BY c+n
HAVING MIN(n) = 1 and c+n < (SELECT MAX(c) FROM x)
-----------------------------------

CREATE TABLE Foobar
(seq INTEGER NOT NULL PRIMARY KEY
CHECK(seq > 0));
INSERT INTO Foobar VALUES (3);
INSERT INTO Foobar VALUES (7);
INSERT INTO Foobar VALUES (8);
INSERT INTO Foobar VALUES (10);
INSERT INTO Foobar VALUES (11);


SELECT (F1.seq +1) AS start, (F2.seq -1) AS fini
FROM (SELECT seq FROM Foobar
UNION ALL
SELECT 0) AS F1,
Foobar AS F2
WHERE (F1.seq +1) BETWEEN 0 AND (F2.seq-1)
AND NOT EXISTs
(SELECT *
FROM Foobar AS F3
WHERE F3.seq BETWEEN (F1.seq+1) AND (F2.seq-1));
-----------------------
SELECT n+1 as startgap, nextn-1 as endgap
FROM(SELECT n,
(SELECT MIN(n)
FROM t1 as b
WHERE b.n > a.n) as nextn
FROM t1 as a) as d
WHERE nextn - n > 1
----------------------------------
SELECT
MIN(i) as low,
MAX(i) as high
FROM(
SELECT
N1.num,
COUNT(N2.num) - N1.num
FROM Numbers AS N1, Numbers AS N2
WHERE N2.num <= N1.num
GROUP BY N1.num
) AS N(i,gp)
GROUP BY gp

UPDATE Nulls

Recently I got a 'specific' request from one of our customers to update a column (which allows NULLs).Let see the following script

CREATE TABLE test (col1 INT NOT NULL PRIMARY KEY,col2 DECIMAL(5,2))
GO
INSERT INTO test (col1, col2) VALUES (1, 3.5)
INSERT INTO test (col1, col2) VALUES (2, null)
INSERT INTO test (col1, col2) VALUES (3, null)
INSERT INTO test (col1, col2) VALUES (4, null)
INSERT INTO test (col1, col2) VALUES (5, null)
INSERT INTO test (col1, col2) VALUES (6, 7.4)
INSERT INTO test (col1, col2) VALUES (7, null)
INSERT INTO test (col1, col2) VALUES (8, null)
INSERT INTO test (col1, col2) VALUES (9, null)
INSERT INTO test (col1, col2) VALUES (10, 2.9)
INSERT INTO test (col1, col2) VALUES (11, null)
INSERT INTO test (col1, col2) VALUES (12, null)

So we would like to update NULL's with value till first non-null value
UPDATE test
SET col2 = (SELECT r.col2
FROM test AS r
WHERE r.col2 IS NOT NULL
AND r.col1 = (SELECT MAX(rr.col1)
FROM test AS rr WHERE
rr.col2 IS NOT NULL AND rr.col1 < test.col1))
WHERE col2 IS NULL


SELECT * FROM test

Compare tables

I have no doubt that you've seen many ways/or third products to compare tables. I'd like to show a couple of examples that the first one I learned from Itzik Ben-Gan and the second one I used BINARY_CHECKSUM function.
1)
IF(SELECT CHECKSUM_AGG(checksum(*)) FROM t1)
<> (select CHECKSUM_AGG(checksum(*)) FROM t2)
Print'different'
ELSE
Print'probably the same'

/*
CHECKSUM_AGG
Returns the checksum of the values in a group. Null values are ignored.
*/

2)
SELECT a.Col, a.CheckSum
From (Select colid as "Col", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.TableA ) a
Inner Join (
Select colid as "Col", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.TableB) b
On a.Col= b.Col
Where a.CheckSum <> b.CheckSum

Actually , the second example may or may not return you inaccurate value, so please test it before using.

UDF vs CHECK Constraint

As you probably know there are more accurate/efficient ways to create a CHECK constrain without using UDF. However, there under some circumstances we have to.


CREATE TABLE Test (col VARCHAR(20))
GO

This UDF returns 0 when string is an empty or NULL

CREATE FUNCTION dbo.fn_check_null_or_blank
(
@StringToCheck VARCHAR(250)
)
RETURNS INT
AS
Begin
RETURN 1-SIGN(LEN(COALESCE(@StringToCheck,'')))
End

ALTER TABLE Test
ADD CONSTRAINT df_col CHECK (dbo.fn_check_null_or_blank(col)=0)

Usage

INSERT INTO Test VALUES (NULL)--Failed
INSERT INTO Test VALUES ('Hello World')--Works
INSERT INTO Test VALUES (' ')--Failed


DROP TABLE Test
DROP FUNCTION dbo.fn_check_null_or_blank

In the above example I wanted just to show you how you can define a CHECK constraint by using UDF.

Compressing .BAK file

There are many third products that do backup database and compressing file for you. I'd like to share some ideas about how to compress a huge .BAK file if you don't have a budget to buy third products.

Let me see we do a full backup of the database that creates a .BAK file plus a date when backup have been taken.

DECLARE FileName VARCHAR(100),@Date VARCHAR(10)
SET @Date =CONVERT(VARCHAR(10),GETDATE(),112)
SELECT @FileName = 'C:\BackupSQL\MyDataBase' + @Date+'.bak'

BACKUP DATABASE EXM_IscarData
TO DISK = @FileName

I forgot to mention that you need WINRAR program to be installed on the server.
Now that we have .BAK file ,create two batch files (one for compression, second one for restore) that contain
---To compress
WINRAR a C:\BackupSQL\MyDataBase%1 C:\BackupSQL\MyDataBase%1.BAK
DEL "C:\BackupSQL\MyDataBase%1.BAK " ---Delete .BAK file

---to restore from .RAR file
WINRAR x C:\BackupSQL\MyDataBase%1 c:\

Put those files in C:\Windows\system32 directory.
The last thing is to call this command at Job's step
Decide what you are going to do restore or backup operations.

DECLARE @Str VARCHAR(100)
SET @Str = 'BatchFileName.bat ' + @Date
EXEC master.dbo.XP_CMDSHELL @Str, NO_OUTPUT

I'm sure that you need to put in a logic to restore or backup database.So finally you will have compresed file with .RAR extention which saves disk's space.

CASE expression techniques

I'm sure that all of us know and do CASE expression in their projects and reports. I'd like to share a couple of methodes that I used rarely , but it looks useful

Using Case in JOIN query
CASE expression checks title's type column and assign an appropriate column to be referenced

SELECT a.au_lname, a.au_fname, a.address,
t.title, t.type
FROM authors a INNER JOIN
titleauthor ta ON ta.au_id = a.au_id INNER JOIN
titles t ON t.title_id = ta.title_id
INNER JOIN publishers p on t.pub_id =
CASE WHEN t.type = 'Business' THEN p.pub_id ELSE null END
INNER JOIN stores s on s.stor_id =
CASE WHEN t.type = 'Popular_comp' THEN t.title_id ELSE null END


---Or

SELECT FROM Territory AS t JOIN Customers AS c
ON
CASE WHEN t.countrycode = 'US' OR t.countrycode = 'CA' then t.Areacode ELSE t.countrycode END=
CASE WHEN c.countrycode = 'US' OR c.countrycode = 'CA' then c.Areacode ELSE c.countrycode END

The following CASE expression we use more 'frequently'
This CASE expression returns 1 or 0 (True ,False)
SELECT
columname,
CASE
WHEN EXISTS (
SELECT * FROM Table1 t1
WHERE t1.col1= 1 AND t1.col2= t2.col2)
THEN 1
ELSE 0
END AS Alias
FROM Table2 t2
ORDER BY columname

Date tricks and tips

At this time I'd like to share some techniques to deal with dates. Many of us have been dealing with dates , and for example this question I have seen in the newsgroup and was asked by many developers "How do I get rid of time?"

Well, first thing is I am going to use CONVERT system function to do that

1)SELECT CAST(CONVERT(VARCHAR(20),GETDATE(),112)AS DATETIME)
2)SELECT CAST(FLOOR(CAST(GETDATE() AS REAL)) AS DATETIME)

It's OK, we got it. In the above statement we need to convert the date twice in the first one and more than twice in the second one to get the result.


The following technique I learned from Steve Kass

SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)

Pretty simple and work faster, believe me.

Also,by using this technique , you can a last /first day of current/next... month

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)-1

Also, as I have seen we have been rarely using these functions, but they do exist.It also works on SQL Server 2005 (sp2)

SELECT {fn extract(minute FROM getdate())}
SELECT {fn dayname( GetDate()) }
SELECT {fn CURRENT_DATE()}
SELECT {fn CURRENT_time()}

Week of Month

I have been experiencing many time with such requests like 'Give me number of week fo given date' , do you?
Actually we use here a simple logic.
Week number for given date minus a formated given date to the first date of the month (20070201) plus 1

CREATE FUNCTION dbo.WeekOfMonth (@now_day DATETIME)
RETURNS INT
AS
BEGIN
RETURN DATEPART(week, @now_day) - DATEPART(week, CONVERT(CHAR(6), @now_day, 112)+'01') + 1
END
GO
--Usage
SET DATEFIRST 1
SELECT dbo.WeekOfMonth('20070201') AS [1st]
SELECT dbo.WeekOfMonth('20070209') AS [2nd]

Dealing with duplicates

A this time I'd like to show some techniques to deal with duplicates
CREATE TABLE TableWithDups
(
row_id int identity(1,1),
col1 int,
col2 int
)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (1,10)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (1,10)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (4,2)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (3,3)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (3,3)
INSERT INTO TableWithDups (col1 ,col2 ) VALUES (9,20)

PRINT 'Duplicates in Table'
SELECT * FROM TableWithDups
WHERE EXISTS
(SELECT * FROM TableWithDups TWD
WHERE TWD.row_id <> TableWithDups.row_id
AND TWD.col1 = TableWithDups.col1
AND TWD.col2 = TableWithDups.col2)

PRINT 'Duplicates to be Deleted'
SELECT * FROM TableWithDups
WHERE EXISTS
(SELECT * FROM TableWithDups TWD
WHERE TWD.row_id < TableWithDups.row_id
AND TWD.col1 = TableWithDups.col1
AND TWD.col2 = TableWithDups.col2 )


PRINT 'Executig Deletion of Duplicates '
DELETE FROM TableWithDups
WHERE EXISTS
(SELECT * FROM TableWithDups TWD
WHERE TWD.row_id < TableWithDups.row_id
AND TWD.col1 = TableWithDups.col1
AND TWD.col2 = TableWithDups.col2 )

SELECT * FROM TableWithDups

If you are using SQL Server 2005 take a look at the below script
WITH Duplicates
AS
( SELECT *, ROW_NUMBER()OVER(PARTITION BY col1 , col2 ORDER BY col1 , col2 ) AS cnt FROM TableWithDups)
DELETE FROM Duplicates WHERE cnt> 1;

Recently my good friend Doron Farber wrote great article about how efficiently remove duplicates on huge tables based on real world example.
http://www.dfarber.com/computer-consulting-blog/2011/12/26/remove-duplicate-records-in-sql.aspx