Truncate Vs Delete

Truncate		Delete
1	It is DDL(Data Definition Language) Statement.	It is DML(Data Manipulation Language) Statement.
2	It Delete all rows from table.	You Can Delete All rows as well as specified rows using Delete.
3	You can not Rollback all raw.	Using Rollback you can retrieve all rows from the point of last commit.
4	It release all raw including Memory space, Just Structure remains there.	It delete all raw but, not Memory space.
5	Example : Truncate Table Emp	Example : Delete From Emp Where Empno=7369

Small DateTime vs DateTime

Small DateTime		DateTime
1	It Occupies 4 byte Size	It Occupies 8 byte Size
2	The precision of small datetime is one minute	The precision of datetime is 3.33 milli Seconds
3	It Stores Date From 1st January 1900 through 6th June 2079. Which is usually more than enough	It Stores Date From 1st January 1753 through 31st December 9999.

Matching Data Between Rows and Columns

DECLARE @Cols TABLE(Col INT)

INSERT INTO @Cols VALUES (1)
INSERT INTO @Cols VALUES (2)
INSERT INTO @Cols VALUES (3)
INSERT INTO @Cols VALUES (4)
INSERT INTO @Cols VALUES (5)
INSERT INTO @Cols VALUES (6)
INSERT INTO @Cols VALUES (7)
INSERT INTO @Cols VALUES (8)
INSERT INTO @Cols VALUES (9)

DECLARE @Rows TABLE(Row INT)

INSERT INTO @Rows VALUES (100)
INSERT INTO @Rows VALUES (104)
INSERT INTO @Rows VALUES (101)
INSERT INTO @Rows VALUES (99)
INSERT INTO @Rows VALUES (77)
INSERT INTO @Rows VALUES (20)
INSERT INTO @Rows VALUES (10)

Col
-----------
1
2
3
4
5
6
7
8
9

Row
-----------
100
104
101
99
77
20
10

The challenge is to mark a coordinate, with a value of X,
if and only if the row value is divisible by the col value,
i.e. it has a modulo of zero. The additional requirements are:
the final query must work with random row values.

Row         1    2    3    4    5    6    7    8    9   Total
----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
10          x    x              x                          3
20          x    x         x    x                          4
77          x                             x                2
99          x         x                             x      3
100         x    x         x    x                          4
101         x                                              1
104         x    x         x                   x           4
----------------------------------------------------------------
Total       7    4    1    3    3     0    1   1     1     21

Challenge Requirements Summary

1. Only coordinates where the column value modulo the row value equals zero should be marked, with an "X"
2. Number of rows in the table is not fixed. The query should work with variable number of rows in the table.

SQL Challange

Table A

code        aname

----------- ----------

1           Cat

2           Dog

3           Bird

Table B

code        bname

----------- ----------

1           aaa

1           bbb

2           ccc

2           ddd

Table C

code        cname

----------- ----------

1           xxx

1           yyy

1           zzz

2           www

The task is to write a query that produces the following output from the above tables. The query should run on SQL Server 2000 as well as on SQL Server 2005.

Output

code        aname      bname      cname

----------- ---------- ---------- ----------

1           Cat        aaa        xxx

1           Cat        bbb        yyy

1           Cat        NULL       zzz

2           Dog        ccc        www

2           Dog        ddd        NULL

3           Bird       NULL       NULL

Sample Data

DECLARE @a TABLE (code INT, aname VARCHAR(10))

INSERT INTO @a(code, aname) SELECT 1,'Cat'

INSERT INTO @a(code, aname) SELECT 2,'Dog'

INSERT INTO @a(code, aname) SELECT 3,'Bird'

DECLARE @b TABLE (code INT, bname VARCHAR(10))

INSERT INTO @b(code, bname) SELECT 1,'aaa'

INSERT INTO @b(code, bname) SELECT 1,'bbb'

INSERT INTO @b(code, bname) SELECT 2,'ccc'

INSERT INTO @b(code, bname) SELECT 2,'ddd'

DECLARE @c TABLE (code INT, cname VARCHAR(10))

INSERT INTO @c(code, cname) SELECT 1,'xxx'

INSERT INTO @c(code, cname) SELECT 1,'yyy'

INSERT INTO @c(code, cname) SELECT 1,'zzz'

INSERT INTO @c(code, cname) SELECT 2,'www'

Write a Single Query that should run on SQL Server 2000 and SQL Server 2005

Get Branch and Complete Tree in SQL Server

This post is useful for developers who want to build a tree as per parent id, And a programmer who want a all child node from parent id. and All tree structure in SQL Server.

Using this code u can develop a tree structure in SQL Server.

CREATE TABLE TestTable ( ID int primary key NOT NULL, CatName varchar(100), ParentID int )
INSERT INTO TestTable VALUES (0,NULL,null)
INSERT INTO TestTable VALUES (1,'A', 0)
INSERT INTO TestTable VALUES (2,'B', 0)
INSERT INTO TestTable VALUES (3,'A1', 1)
INSERT INTO TestTable VALUES (4,'B1', 2)
INSERT INTO TestTable VALUES (5,'A11',3)
INSERT INTO TestTable VALUES (6,'A12',3)

-- Get branch

WITH TreeRecCTE AS
(
SELECT ID, CatName, ParentID, CONVERT(varchar(MAX), ID) AS IDPath
FROM TestTable
WHERE (ParentID IS NULL)
UNION ALL
SELECT Child.ID, Child.CatName, Child.ParentID,
Parent.IDPath + ',' + CONVERT(varchar(100), Child.ID) AS IDPath
FROM TestTable AS Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
)

SELECT ID, CatName, ParentID, IDPath
FROM TreeRecCTE AS TreeRecCTE_1
WHERE (IDPath LIKE '%,1,%')
ORDER BY ParentID

-- Get complete tree:

WITH TreeRecCTE AS
(
SELECT ID, CatName, ParentID, CONVERT(varchar(MAX), ID) AS IDPath
FROM TestTable
WHERE (ParentID IS NULL)
UNION ALL
SELECT Child.ID, Child.CatName, Child.ParentID,
Parent.IDPath + ',' + CONVERT(varchar(100), Child.ID) AS IDPath
FROM TestTable AS Child INNER JOIN TreeRecCTE AS Parent ON Child.ParentID = Parent.ID
)

SELECT ID, CatName, ParentID, IDPath
FROM TreeRecCTE AS TreeRecCTE_1
ORDER BY ParentID

Script all data of a table

A script to script all data of a table. The gains of this script are:

• The performance
• Support for VARBINARY and IMAGE columns
• Almost every standard data-type is convered
• Flexibility to specify which data shall be scripted
• Security for SQL Injection

Usage

Open the script on the database to script data from.

• Configure the name of the table to be scripted (@table_name)
• Configure if the udf_varbintohexstr_big is available (only needed for IMAGE and VARBINARY data with more than 3998 bytes)
• Configure if the column names shall be scripted for destination database. This brings more flexibility because the destination table has more columns than the source table or the column order is different. Do not script the column names to save space.
• Execute the script once.
• Take the execution result as statement to script your data (maybe change something withi)
• Execute the result from first execution again
• Take the scripted data to insert them on another database/server.

Performance

Due to the fact that this script works without any cursors for data collection it is by design much faster than many other.

DATETIME, DATE and TIME columns will not be scripted as strings but as binary data which are much faster handled by SQL Server.

Support for VARBINARY and IMAGE

Most scripts to export data into SQL do not support data from type VARBINARY and IMAGE or cut them. Data with less than 3998 bytes can be scripted without any other requirements. If you want to script really huge binary data just install the plain TSQL user defined function udf_varbintohexstr_big which is also available here. Now you can script any size of binary data.

Data-Types

Supported data-types are:

• BIGINT
• BINARY
• BIT
• CHAR
• DATE
• DATETIME
• DATETIME2
• DECIMAL
• FLOAT
• IMAGE
• INT
• MONEY
• NCHAR
• NUMERIC
• NTEXT
• NVARCHAR
• REAL
• SMALLDATETIME
• SMALLINT
• SMALLMONEY
• TEXT
• TIME
• TIMESTAMP
• TINYINT
• UNIQUEIDENTIFYER
• VARBINARY
• VARCHAR
• XML

Flexibility

Because the first step of the script just creates another script which will script the data you are able to specify any WHERE/JOIN criteria for the data to be scripted if not the complete table needs to be.

It is also possible to remove specific columns from the generated script.

Security

All character data will be masked to avoid SQL injection. All column names will be quoted to ensure a valid SQL output.

Remarks

To be able to script huge VARBINARY or IMAGE data you need the udf_varbintohexstr_big which is published here. The function is plain TSQL.

Issues

Here the known issues of the script.

udf_varbintohexstr_big

Sometimes a gain becomes an issue... It is only possible to script large IMAGE data with the udf_varbintohexstr_big so need the rights to install a user defined function on the source server. The destination server does not need this function.

SSMS restrictions

The SQL Server Management Studio (SSMS) restricts the output for each cell to 40kb. The script can script any size of data but the data may be cutted by SSMS. To script any size of data just use a small program, perl/php/ps1 script to execute the script and write directly into a file.

Not supported data-types

Currently not supported data-types are:

• HIERARCHYID
• SQL_VARIANT
• User defined types

/******************************************************************

Author
======
Parag Shukla

Summary
=======
Script to create a SELECT statement to script all data of a specified table

Parameters
==========

@table_name
The name of the table to be scripted

@handle_big_binary
If set to 1 the user defined function udf_varbintohexstr_big will be used
to convert BINARY, VARBINARY and IMAGE data. For futher information see remarks.

@column_names
If set to 0 only the values to be inserted will be scripted; the column names wont.
This saves memory but the destination tables needs exactly the same columns in
same order.
If set to 1 also the names of the columns to insert the values into will be scripted.

******************************************************************/

SET NOCOUNT ON

DECLARE @table_name SYSNAME
DECLARE @handle_big_binary BIT
DECLARE @column_names BIT

-- ////////////////////
-- -> Configuration
SET @table_name = 'dbo.Categories'
SET @handle_big_binary = 1
SET @column_names = 1
-- <- Configuration
-- ////////////////////

DECLARE @object_id INT
DECLARE @schema_id INT

--SELECT * FROM sys.all_objects
SELECT @object_id = object_id, @schema_id = schema_id
FROM sys.tables
WHERE object_id = OBJECT_ID(@table_name)


DECLARE @columns TABLE (column_name SYSNAME, ordinal_position INT, data_type SYSNAME, data_length INT, is_nullable BIT)

-- Get all column information
INSERT INTO @columns
SELECT column_name, ordinal_position, data_type, character_maximum_length, CASE WHEN is_nullable = 'YES' THEN 1 ELSE 0 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA_NAME(@schema_id)
AND TABLE_NAME = OBJECT_NAME(@object_id)

DECLARE @select VARCHAR(MAX)
DECLARE @insert VARCHAR(MAX)
DECLARE @crlf CHAR(2)
DECLARE @sql VARCHAR(MAX)
DECLARE @first BIT
DECLARE @pos INT
SET @pos = 1

SET @crlf = CHAR(13) + CHAR(10)

WHILE EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position >= @pos)
BEGIN
DECLARE @column_name SYSNAME
DECLARE @data_type SYSNAME
DECLARE @data_length INT
DECLARE @is_nullable BIT

-- Get information for the current column
SELECT @column_name = column_name, @data_type = data_type, @data_length = data_length, @is_nullable = is_nullable
FROM @columns
WHERE ordinal_position = @pos

-- Create column select information to script the name of the source/destination column if configured
IF (@select IS NULL)
SET @select = ' ''' + QUOTENAME(@column_name)
ELSE
SET @select = @select + ','' + ' + @crlf + ' ''' + QUOTENAME(@column_name)

-- Handle NULL values
SET @sql = ' '
SET @sql = @sql + 'CASE WHEN ' + QUOTENAME(@column_name) + ' IS NULL THEN ''NULL'' ELSE '

-- Handle the different data types
IF (@data_type IN ('bigint', 'bit', 'decimal', 'float', 'int', 'money', 'numeric',
'real', 'smallint', 'smallmoney', 'tinyint'))
BEGIN
SET @sql = @sql + 'CONVERT(VARCHAR(40), ' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type IN ('char', 'nchar', 'nvarchar', 'varchar'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(' + QUOTENAME(@column_name) + ', '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type = 'date')
BEGIN
SET @sql = @sql + '''CONVERT(DATE, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(3), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'time')
BEGIN
SET @sql = @sql + '''CONVERT(TIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(5), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime2')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME2, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'smalldatetime')
BEGIN
SET @sql = @sql + '''CONVERT(SMALLDATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(4), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'text')
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(VARCHAR(MAX), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('ntext', 'xml'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(NVARCHAR(MAX), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('binary', 'varbinary'))
BEGIN
-- Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ' dbo.udf_varbintohexstr_big (' + QUOTENAME(@column_name) + ')'
ELSE
SET @sql = @sql + ' master.sys.fn_varbintohexstr (' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type = 'timestamp')
BEGIN
SET @sql = @sql + '''CONVERT(TIMESTAMP, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'uniqueidentifier')
BEGIN
SET @sql = @sql + '''CONVERT(UNIQUEIDENTIFIER, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(16), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'image')
BEGIN
-- Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ' dbo.udf_varbintohexstr_big (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
ELSE
SET @sql = @sql + ' master.sys.fn_varbintohexstr (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
END
ELSE
BEGIN
PRINT 'ERROR: Not supported data type: ' + @data_type
RETURN
END

SET @sql = @sql + ' END'

-- Script line end for finish or next column
IF EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position > @pos)
SET @sql = @sql + ' + '', '' +'
ELSE
SET @sql = @sql + ' + '

-- Remember the data script
IF (@insert IS NULL)
SET @insert = @sql
ELSE
SET @insert = @insert + @crlf + @sql

SET @pos = @pos + 1
END

-- Close the column names select
SET @select = @select + ''' +'

-- Print the INSERT INTO part
PRINT 'SELECT ''INSERT INTO ' + @table_name + ''' + '

-- Print the column names if configured
IF (@column_names = 1)
BEGIN
PRINT ' ''('' + '
PRINT @select
PRINT ' '')'' + '
END

PRINT ' ''VALUES ('' +'

-- Print the data scripting
PRINT @insert

-- Script the end of the statement
PRINT ' '')'''
PRINT ' FROM ' + @table_name

FOR XML PATH - How to generate a Delimited String using FOR XML PATH?

There are two common string operations where I used to write a TSQL loop in the SQL server 2000 era.

1. To split a delimited string and return a set
2. To generate a delimited string from a set

The XML enhancements added to SQL Server 2005 made both these operations easier with XML. I think, most of the times these operations are done in small pieces of data. Though you can do these operations on extremely large data, I don't think it is advisable. There are other ways to handle large chunks of data.

In this post, lets see how we could generate a delimited string using FOR XML PATH.

Let us first see the source data.

DECLARE @companies Table( 
 CompanyID INT, 
 CompanyCode  int
)

insert into @companies(CompanyID, CompanyCode) values(1,1)
insert into @companies(CompanyID, CompanyCode) values(1,2)
insert into @companies(CompanyID, CompanyCode) values(2,1)
insert into @companies(CompanyID, CompanyCode) values(2,2)
insert into @companies(CompanyID, CompanyCode) values(2,3)
insert into @companies(CompanyID, CompanyCode) values(2,4)
insert into @companies(CompanyID, CompanyCode) values(3,1)
insert into @companies(CompanyID, CompanyCode) values(3,2)

SELECT * FROM @companies
/*
CompanyID   CompanyCode
----------- -----------
1           1
1           2
2           1
2           2
2           3
2           4
3           1
3           2
*/

This is the result that we need.

/*
CompanyID   CompanyString
----------- -------------------------
1           1,2
2           1,2,3,4
3           1,2
*/

One option is to run a loop that constructs a delimited string for each CompanyID. Another option is to create a function that returns a delimited string for each company ID. I am presenting a third option using FOR XML PATH.

SELECT CompanyID,
 REPLACE((SELECT
     CompanyCode AS 'data()'
  FROM @companies c2
  WHERE c2.CompanyID = c1.CompanyID
  FOR XML PATH('')), ' ', ',') AS CompanyString
FROM @companies c1
GROUP BY CompanyID

/*
CompanyID   CompanyString
----------- -------------------------
1           1,2
2           1,2,3,4
3           1,2

The above query uses FOR XML PATH to return a comma delimited string containing the company
code of each row.
*/