SQL Server - Date Formats

-- Formatting Dates

    Declare @d datetime
        select @d = getdate()
   
        select @d as OriginalDate,
        convert(varchar,@d,100) as ConvertedDate,
        100 as FormatValue,
        'mon dd yyyy hh:miAM (or PM)' as OutputFormat
        union all
        select @d,convert(varchar,@d,101),101,'mm/dd/yyyy'
        union all
        select @d,convert(varchar,@d,102),102,'yyyy.mm.dd'
        union all
        select @d,convert(varchar,@d,103),103,'dd/mm/yyyy'
        union all
        select @d,convert(varchar,@d,104),104,'dd.mm.yyyy'
        union all
        select @d,convert(varchar,@d,105),105,'dd-mm-yyyy'
        union all
        select @d,convert(varchar,@d,106),106,'dd mon yyyy'
        union all
        select @d,convert(varchar,@d,107),107,'Mon dd, yyyy'
        union all
        select @d,convert(varchar,@d,108),108,'hh:mm:ss'
        union all
        select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
        union all
        select @d,convert(varchar,@d,110),110,'mm-dd-yyyy'
        union all
        select @d,convert(varchar,@d,111),111,'yyyy/mm/dd'
        union all
        select @d,convert(varchar,@d,112),112,'yyyymmdd'
        union all
        select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
        union all
        select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
        union all
        select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
        union all
        select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
        union all
        select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'
union all
select @d as OriginalDate,
                convert(varchar,@d,0) as ConvertedDate,
0 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,1),1,'mm/dd/yy'
union all
select @d,convert(varchar,@d,2),2,'yy.mm.dd'
union all
select @d,convert(varchar,@d,3),3,'dd/mm/yy'
union all
select @d,convert(varchar,@d,4),4,'dd.mm.yy'
union all
select @d,convert(varchar,@d,5),5,'dd-mm-yy'
union all
select @d,convert(varchar,@d,6),6,'dd mon yy'
union all
select @d,convert(varchar,@d,7),7,'Mon dd, yy'
union all
select @d,convert(varchar,@d,8),8,'hh:mm:ss'
union all
select @d,convert(varchar,@d,9),9,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,10),10,'mm-dd-yy'
union all
select @d,convert(varchar,@d,11),11,'yy/mm/dd'
union all
select @d,convert(varchar,@d,12),12,'yymmdd'
union all
select @d,convert(varchar,@d,13),13,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,14),14,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,20),20,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,21),21,'yyyy-mm-dd hh:mi:ss.mmm(24h)'      

How to delete table data based on the foreign key relationship.

with Fkeys as (

    select distinct

         OnTable       = OnTable.name
        ,AgainstTable  = AgainstTable.name

    from

        sysforeignkeys fk

        inner join sysobjects onTable
            on fk.fkeyid = onTable.id

        inner join sysobjects againstTable
            on fk.rkeyid = againstTable.id

    where 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        -- ignore self joins; they cause an infinite recursion
        and OnTable.Name <> AgainstTable.Name
    )

,MyData as (

    select
         OnTable = o.name
        ,AgainstTable = FKeys.againstTable

    from

        sys.objects o

        left join FKeys
            on  o.name = FKeys.onTable

    where 1=1
        and o.type = 'U'
        and o.name not like 'sys%'
    )

,MyRecursion as (

    -- base case
    select
         TableName    = OnTable
        ,Lvl        = 1
    from
        MyData
    where 1=1
        and AgainstTable is null

    -- recursive case
    union all select
         TableName    = OnTable
        ,Lvl        = r.Lvl + 1
    from
        MyData d
        inner join MyRecursion r
            on d.AgainstTable = r.TableName
)
select
     max(Lvl) As Lvl
    ,TableName
    ,strSql = 'delete from [' + tablename + ']'
from
    MyRecursion
group by
    TableName
order by
     1 desc
    ,2 desc

Query to get the parent level and child level hierarchy data of a particular account.

Declare @TestTable Table
(
ID int,
Name varchar(50),
ParentID int
)

Insert into @TestTable values
(1,'A',null),
(2,'Aa',1),
(3,'Aaa',1),
(4,'Ab',2),
(5,'Abb',4),
(6,'B',null),
(7,'Ba',6),
(8,'Bbb',7),
(9,'Abc',4),
(10,'Acc',9)


Declare @ID int = 4

;with cte
(
ID,Name,ParentID
)
As
(
Select ID,Name,ParentID
From @TestTable  where ID = @ID

Union All

Select a.ID , a.Name,a.ParentID
from cte c inner join @TestTable a on c.ParentID = a.ID
)
,
cte1
(
ID,Name,ParentID
)
As
(
Select ID,Name,ParentID
From cte where ParentID is null

Union All

Select a.ID , a.Name,a.ParentID
from cte1 c inner join @TestTable a on a.ParentID = c.ID
)

Select * from Cte1 Option (MAXRECURSION 32767)

Query to get the child level hierarchy data of a particular account.

Declare @TestTable Table
(
ID int,
Name varchar(50),
ParentID int
)

Insert into @TestTable values
(1,'A',null),
(2,'Aa',1),
(3,'Aaa',1),
(4,'Ab',2),
(5,'Abb',4),
(6,'B',null),
(7,'Ba',6),
(8,'Bbb',7),
(9,'Abc',4),
(10,'Acc',9)


Declare @ID int = 4

;with cte

(

ID , Name,ParentID,ParentName

)

As

(

 Select ID , Name,ParentID,
(select Name from @TestTable where ID = a.ParentID) parentName
 From @TestTable a where ID = @ID

Union All

Select m.ID , m.Name,c.ID,c.Name
from cte c inner join @TestTable m on m.ParentID = c.ID

)

Select * from cte Option (MAXRECURSION 32767)

How to stop or disable Recursive Trigger/Nested Trigger in SQL Server.

A trigger execution may occured another trigger execution or same trigger execution. This trigger called Nested Trigger Or Recursive Trigger. SQL Server allows maximum 32 Level recursion.

Disable Nesting/Recursing Triggers

Method 1:

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

Method 2:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Method 3:




Restrict Trigger Nesting to certain level.

Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.

IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

Comma seperated date splitting without using loop.

--Comma seperated date splitting without using loop.

--// Table variable declarion
Declare @TableVar Table
(
    ID            int,
    CustomDates varchar(max)
)

--// Data insertion
Insert into @TableVar values(1,'May 01 2012  4:00AM,May 05 2012  4:00AM,Apr 13 2012 10:00AM')

select * from @TableVar

;with cte
(
    ID,CustomDates,strDt,pos
)
As
(
    select ID,
        CustomDates,
        substring(
                    CustomDates,1,case when (charindex(',',CustomDates) -1) = -1 then Len(CustomDates) else charindex(',',CustomDates) -1 end
                 ) strDt,charindex(',',CustomDates) as pos
       
    from @TableVar
    union all
    select s.ID,
        s.CustomDates,
       
             substring(s.CustomDates,cte.pos+1,
                    case when (charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1) = -1 then len(s.CustomDates) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1 end)       

           ,pos + case when charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) = 0 then len(substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) end as pos


    from @TableVar s inner join cte on cte.ID = s.ID
    where cte.pos < len(s.CustomDates)
)

select strDt from cte OPTION (MAXRECURSION 32767);

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

Another Method:

Declare @TableVar Table

(

ID int,

Data varchar(max)

)

Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')


DECLARE @XML XML

SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar

SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data

FROM @XML.nodes('/Root/Child') DOC(COL)

Normal query instead of MERGE operation

/****** Object:  StoredProcedure [dbo].[spBlockHr_SAVE]    Script Date: 04/11/2012 15:32:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Author    : Nikhildas
    Date    : July 01 2011 10:45AM
    Purpose : To Save Block HR value.
    Execute : Exec [spBlockHr_SAVE] <xml Value>,@P_RET_VAL    OUTPUT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Modified By                On                Remarks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    <Next Entry>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
 ALTER PROCEDURE [dbo].[spBlockHr_SAVE]
 (
    @P_XML        XML,
    @P_RET_VAL    INT OUTPUT
 )
 AS
    SET NOCOUNT ON

    DECLARE @DocHandle        INT
   
    DECLARE @BlockHr TABLE
    (
        ID            int ,
        SECTOR        nvarchar(255) ,
        Route        nvarchar(255) ,
        CODE        nvarchar(255) ,
        Month01     float ,
        Month02     float ,
        Month03     float ,
        Month04     float ,
        Month05     float ,
        Month06     float ,
        Month07     float ,
        Month08     float ,
        Month09     float ,
        Month10     float ,
        Month11     float ,
        Month12     float ,
        Total        float
    )   
           
    BEGIN TRY
        BEGIN TRANSACTION
        EXEC sp_xml_preparedocument @DocHandle OUTPUT,@P_XML
       
        INSERT INTO @BlockHr
        SELECT
            ID,
            SECTOR    ,
            Route,
            CODE,
            Month01    ,
            Month02    ,
            Month03 ,
            Month04    ,
            Month05    ,
            Month06    ,
            Month07    ,
            Month08    ,
            Month09    ,
            Month10    ,
            Month11    ,
            Month12    ,
            Total   
        FROM    OPENXML(@DocHandle,'/Root',2)
        WITH
        (
            ID            INT,
            SECTOR        NVARCHAR(1000),
            Route        NVARCHAR(1000),
            CODE        nvarchar(255),
            Month01        FLOAT(53),
            Month02        FLOAT(53),
            Month03        FLOAT(53),
            Month04        FLOAT(53),
            Month05        FLOAT(53),
            Month06        FLOAT(53),
            Month07        FLOAT(53),
            Month08        FLOAT(53),
            Month09        FLOAT(53),
            Month10        FLOAT(53),
            Month11        FLOAT(53),
            Month12        FLOAT(53),
            Total        FLOAT(53)

        )
                   
        EXEC sp_xml_removedocument @DocHandle
/*    Commented on 11-Jul-2011 (For Avoid Merge statement)       
        MERGE BlockHr AS T
        USING @BlockHr    AS S
        ON S.ID = T.ID
        WHEN MATCHED
        THEN
            UPDATE
                SET SECTOR    = S.SECTOR,
                    Route    = S.Route,
                    CODE    = S.CODE,
                    Month01    = S.Month01,
                    Month02    = S.Month02,
                    Month03 = S.Month03,
                    Month04    = S.Month04,
                    Month05    = S.Month05,
                    Month06    = S.Month06,
                    Month07    = S.Month07,
                    Month08    = S.Month08,
                    Month09    = S.Month09,
                    Month10    = S.Month10,
                    Month11    = S.Month11,
                    Month12    = S.Month12,
                    Total    = S.Total
        WHEN NOT MATCHED BY TARGET
        THEN
            INSERT
            (
                SECTOR    ,
                Route,
                CODE,
                Month01    ,
                Month02    ,
                Month03 ,
                Month04    ,
                Month05    ,
                Month06    ,
                Month07    ,
                Month08    ,
                Month09    ,
                Month10    ,
                Month11    ,
                Month12    ,
                Total           
            )
            VALUES
            (
                S.SECTOR    ,
                S.Route        ,
                S.CODE        ,
                S.Month01    ,
                S.Month02    ,
                S.Month03 ,
                S.Month04    ,
                S.Month05    ,
                S.Month06    ,
                S.Month07    ,
                S.Month08    ,
                S.Month09    ,
                S.Month10    ,
                S.Month11    ,
                S.Month12    ,
                S.Total   
            );
*/
            --// Updation
            UPDATE T
                SET SECTOR    = S.SECTOR,
                    Route    = S.Route,
                    CODE    = S.CODE,
                    Month01    = S.Month01,
                    Month02    = S.Month02,
                    Month03 = S.Month03,
                    Month04    = S.Month04,
                    Month05    = S.Month05,
                    Month06    = S.Month06,
                    Month07    = S.Month07,
                    Month08    = S.Month08,
                    Month09    = S.Month09,
                    Month10    = S.Month10,
                    Month11    = S.Month11,
                    Month12    = S.Month12,
                    Total    = S.Total
            FROM [BlockHr] T INNER JOIN @BlockHr S
            ON S.ID = T.ID

            --// Insertion
            INSERT INTO [BlockHr]
            SELECT                
                S.SECTOR    ,
                S.Route        ,
                S.CODE        ,
                S.Month01    ,
                S.Month02    ,
                S.Month03    ,
                S.Month04    ,
                S.Month05    ,
                S.Month06    ,
                S.Month07    ,
                S.Month08    ,
                S.Month09    ,
                S.Month10    ,
                S.Month11    ,
                S.Month12    ,
                S.Total        ,
                1
            FROM  [BlockHr] T RIGHT OUTER JOIN @BlockHr S
            ON S.ID = T.ID   
            WHERE (T.ID IS NULL)
                                           
                   
            EXEC [SPAD_TRX_LOG_SAVE] 0,'USER-COST CENTER','','MODIFY',1,'',NULL
            SET @P_RET_VAL =   1
      COMMIT TRANSACTION
      SET @P_RET_VAL = 1
    END TRY
    BEGIN CATCH
        SET @P_RET_VAL = -1
        ROLLBACK TRANSACTION
    END CATCH

DateTime Formats in SQL Server

The conversion of datetime data into many formats is everyday requirement. Here is a table of commonly used formats, to deal with datetime data.

For Eg., Use Convert function as follow

SELECT CONVERT(VARCHAR(20), GETDATE(), 108) As dtm

SQL Query to select a random row from a table

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic,but each database server requires different SQL syntax.

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Select a random row with IBM DB2

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Microsoft SQL Server 2008 Sys.Objects Catalog View object types

For Eg.,    Select * From Sys.Objects where type = 'P'

It returns the all the user defined stored procedure in the database.

I have listed below the various objects types of sysojects table.

type='AF'  'Aggregate function (CLR)'

type='C'  'CHECK constraint'

type='D'  'DEFAULT (constraint or stand-alone)'

type='F'  'FOREIGN KEY constraint'

type='FN'  'SQL scalar function'

type='FS'  'Assembly (CLR) scalar-function'

type='FT'  'Assembly (CLR) table-valued function'

type='IF'  'SQL inline table-valued function'

type='IT'  'Internal table'

type='P'  'SQL Stored Procedure'

type='PC'  'Assembly (CLR) stored-procedure'

type='PG'  'Plan guide'

type='PK'  'PRIMARY KEY constraint'

type='R'  'Rule (old-style, stand-alone)'

type='RF'  'Replication-filter-procedure'

type='S'  'System base table'

type='SN'  'Synonym'

type='SQ'  'Service queue'

type='TA'  'Assembly (CLR) DML trigger'

type='TF'  'SQL table-valued-function'

type='TR'  'SQL DML trigger'

type='U'  'Table (user-defined)'

type='UQ'  'UNIQUE constraint'

type='V'  'View'

type='X'  'Extended stored procedure'

How to find the SQL Server version?

The Microsoft SQL Server T-SQL system variable @@version contains the version signature of the server and also SERVERPROPERTY provides specific information.

USE master;
GO

Select @@Version As SQLServerVersion

GO

SELECT

'Microsoft SQL Server ' +

convert(varchar, SERVERPROPERTY('ProductVersion') ) + ' -- ' +

convert(varchar, SERVERPROPERTY('ProductLevel') ) + ' -- ' +

convert(varchar, SERVERPROPERTY('Edition') ) As SQLServerVersion;

How to import and export image/file column data in SQL Server?

Several methods are available for importing & exporting images / photos / videos / documents / BLOBs.
Here i have used "OPENROWSET" for importing image to database and BCP command (Bulk copy) for exporting. We can also use BCP command for import images/documents.

VARBINARY(MAX) Datatype using  for store images that consisting approximately 2 GB.

USE AdventureWorks;

GO

-- Create image warehouse for importing image into sql server database

CREATE TABLE dbo.PhotoLibrary
(

  PhotoLibraryID       INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  ImageName            VARCHAR(100),

  Photo                     VARBINARY(MAX)
)

GO

-- SQL Server import image - storing images to sql server database

INSERT INTO dbo.PhotoLibrary

           ([ImageName])

VALUES     ('nature.JPG')


GO

UPDATE dbo.PhotoLibrary

SET    Photo = (SELECT *

                FROM   OPENROWSET(BULK 'D:\image\photo\nature.JPG',

                       SINGLE_BLOB) AS x)

WHERE  [ImageName] = 'nature.JPG'

GO

-- Check table population

SELECT *

FROM   dbo.PhotoLibrary

GO


-- SQL Server export image - Using BCP Command (Bulk Copy)

DECLARE  @Command NVARCHAR(4000)


-- Keep the command on ONE LINE - SINGLE LINE!!!

SET @Command = 'bcp "SELECT Photo FROM AdventureWorks.dbo.PhotoLibrary" queryout "D:\image\photo\natureNew.jpg" -T -n -S <Server name\Instance name>'


-- PRINT @Command -- debugging

EXEC xp_cmdshell  @Command

GO

Len() and DataLength() function in SQL Server

Let me show the difference of the Len() and DataLength() function in sql server through a simple example.

Declare @TestTable Table
(
    ID        int,
    Name    varchar(50)
)

Insert Into @TestTable
values(1,'SQL Server')
     ,(2,'SQL Server ')
     ,(3,' SQL Server')
     ,(4,' SQL Server ')
   
Select Len(Name) As LengthOfString From @TestTable
Select DataLength(Name) As DataLengthOfString From @TestTable

Regarding the above sample query, the Len() function just avoiding the trailing space from the column data and returns the length of the string.But DataLength function returns the full length of column data , it won't avoid the trailing space.

How to get indexes on a database

The following query returns the indexes used on tables in a database. I think it's very useful query for all the sequel server guys.We can use the query result of  'IndexRebuild' column for rebuild index.

SELECT
    OBJECT_SCHEMA_NAME(OBJECT_ID) As [Schema],
    OBJECT_NAME(OBJECT_ID) [Table],
    Name,
    'ALTER INDEX ' + name +  ' ON '+object_schema_name(OBJECT_ID)+'.'+OBJECT_NAME(OBJECT_ID)+' REBUILD;' As IndexRebuild
    ,*
FROM    sys.indexes
WHERE   TYPE > 0
ORDER BY
OBJECT_SCHEMA_NAME(OBJECT_ID),
OBJECT_NAME(OBJECT_ID)

The following system stored procedure returns the index information of a single table.

EXEC sp_helpindex 'TableName'

GO Command in SQL Server

GO Command indicates the batch ending signal of Transact-SQL statements to the SQL Server utilities.

Syntax:

GO [count]

Is a positive integer. The batch preceding GO will execute the specified number of times.

GO is not a Transact-SQL statement, it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

Here, i'm giving a tip using GO command to insert same data multiple times wthout a loop.

CREATE TABLE TestTbl
(
    id INT IDENTITY(1,1),
    name VARCHAR(255),
    address VARCHAR(255)
)

SELECT * FROM Northwind..TestTbl

INSERT INTO Northwind..TestTbl VALUES ('johnson','Street view')
GO 5

SELECT * FROM Northwind..TestTbl 

How to Enable xp_cmdshell in SQL Server

–  To allow advanced options to be changed

EXEC sp_configure 'show advanced options', 1

GO

- To update the currently configured value for advanced options.

RECONFIGURE

GO

- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

- To update the currently configured value for this feature.

RECONFIGURE

GO


Alternative way

1. Click the Start button.

2. Select All Programs.

3. Navigate to the Microsoft SQL Server 2008 folder.

4. Right Click on Server name then click facets options

5. In the Facets Option choose Surface Area Configuration in dropdown

6. At the bottom of the window, mark True for xp_cmdshell.

7. Click OK.

SQL Server - Detach and Attach

As the name indicates, the detach and attach method for using database disconnect and connect on a sql server system.We can simply detach a database on sql server with ssms interface or using query.But while attaching database on server system,we need to give the mdf and ldf file path with query.Mainly using these method for moving file path of database from one location to other.

We can determine the name and the current location of all files that a database uses by using the
sp_helpfile stored procedure.

use master
go
sp_helpfile
go

-- Detach the database as follows:

use master
go
sp_detach_db 'mydb'
go

Next, copy the data files and the log files from the current location (D:\DataFile\) to the new location (E:\DataFile\).
Re-attach the database. Point to the files in the new location as follows:

use master
go
sp_attach_db 'mydb','E:\DataFile\mydb.mdf','E:\DataFile\mydb.ldf'
go

Verify the change in file locations by using the sp_helpfile stored procedure:

use mydb
go
sp_helpfile
go

The filename column values should reflect the new locations.

How to get week of given date

In many scenario we need to prepare records weekly basis.Such a cases we want to find week of the given date.I wrote a t-sql tip for to get week of given date.

For Eg , consider 19th november 2011 is given date.The Datepart (wk,<Date>) function will returns the week of the year.So we get result is 47 then take first day (01-Nov-2011) of the given date and applying the same Datepart function formula so we get result 45. Then subtract the result from earlier result with this (47 - 45) after subtraction result add with 1. Finally we get the result is 3. So we can say  the 19th november 2011 is in the third week.

Declare @Dt    DateTime = '19 nov 2011'


Select Datepart(wk, @Dt) - Datepart(wk, Dateadd(Day, - Day(@Dt) + 1,@Dt)) + 1

How to Disable Foreign Keys and Triggers

Sometimes it's just necessary to get rid of foreign key constraints and triggers in order to do maintenance on a database.  Imagine trying to delete all records from 100+ tables, but you now have to put them in dependency-order in order to do it.  While foreign key constraints help maintain relational integrity, it can be a huge pain in the rear when you are trying to do basic operations.  Of course doing this should only be done when you know-for-sure that you won't ruin the overall integrity of the database.

Disable all Constraints on a table

ALTER TABLE yourtable NOCHECK CONSTRAINT ALL

This will disable, but not remove all of your constraints. It will not, however, remove Primary Key and uniqueness constraints.  If you want to only remove a specific constraint, you can replace the word ALL with the name of the constraint - no quotes.  To re-enable replace NOCHECK with CHECK
Sometimes we need to disable triggers because they just get in our way.


To Enable all Constraints

ALTER TABLE yourtable WITH CHECK
CHECK CONSTRAINT ALL


To disable all triggers on a table:

DISABLE Trigger ALL ON yourtable

Replace the word ALL with a specific trigger name to disable a specific trigger.

Some notes:
1) Trigger enabling/disabling is only available for Sql Server 2005
2) In order to truncate a table, you must DROP foreign key constraints rather than disable them.  Use Delete and change your identity seed with DBCC CHECKIDENT if that will solve your problem.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in an indexed view.

How to Encrypt/Decrypt data in SQL Server

Data encryption in SQL Server 2005 can be done either by using password mechanism or by making use of keys and certificates. In this article, we will look at both mechanisms for data encryption for storage, and then the decryption of the encrypted data for information retrieval.

Encryption by PassPhrase
Doing encryption by passphrase is the easiest approach. But let's first create a table in a demo database, say demodata, for employing encryption/decryption. Name the table as 'users', and create four columns-id, name, card_num and encrypt_num in it. The following SQL script creates the table:

Create Table Users
(
    id            varchar(16) not null primary key ,
    name        varchar(50) not null,
    card_num    varchar(50) not null,
    encrypt_num varbinary(MAX)
)

Now, with the EncryptByPassPhrase() method we can encrypt the data in card_num column. While using this method we require a “PassPhrase”, which is nothing but a password that is required to encrypt the data. During decryption the same password has to be passed for data retrieval. The syntax for the method is EncryptByPassPhrase('password','data'), where password is the passphrase and data is the information that is to be encrypted. Suppose we have entries for uuid, name and card_num in the users table, we can run the following script to update the table to encrypt the card_num, and store the converted data in the encrypt_num column. The encrypt_num column is of type varbinary, which holds the data that is encrypted from varchar datatype.

After encrypting the card_num column, we can see the
result of encrypted card number by running the select query

use datademo

UPDATE [users] set [encrypt_num] =
EncryptByPassPhrase('password', [card_num])

The above script converts the data from the card_num column and stores the result in encrypt_num column.

During encryption we used the password as the PassPhrase. Now for decryption, we will have to pass the same PassPhrase.

Supplying a different PassPhrase would result in non-retrieval of the result.

Using DecryptByPassPhrase() method, we will decrypt the data in the column encrypt_num to retrieve the original data. The following script shows a new column, which has decrypted the data from the encrypt_num column, and displays that in the new Decrypted Card Num column.

use [datademo]

select
[id],name,
convert(bigint,convert(varchar(100),
decryptbypassphrase('password',[encrypt_num]))) as
[Decrypted Card Num] from [users]

Decrypted' shows decrypted info from the 'encrypt_num'. The same symmetric key is used for encryption and decryption

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In real scenarios, we would be employing the EncryptByPassPhrase() method directly through data access object codes either in Java or .NET, and we won't be saving original card number in the table as we did here in this article. Similarly, for decryption the DecryptByPassPhrase() method will be done at program level and not at the database level. This mechanism is convenient for data encryption and decryption, but each time we require PassPhrase for both the processes. This means that PassPhrase is still vulnerable, as we have to store it in some procedure for data access objects.

Encryption by Keys
The limitation of encryption by passphrase methods is that we have to supply the password or passphrase each time the data has to be accessed. But, if we encrypt our symmetric key with a certificate then we won't have to pass the passphrase each time. To create a key or its certificate, we must first create or open the master key for the database. The following command creates a master key:

create master key encryption by password = 'password';

Now, we can create a certificate and then a symmetric key that is attached to that certificate. The following SQL script creates the certificate 'DemoCert' and a key 'DemoKey' associated with that certificate.

create certificate DemoCert with subject = 'Demo Certificate';

create symmetric key DemoKey with
algorithm=AES_256 encryption by certificate DemoCert;

Now that we possess a key, we can do encryption using the EncryptByKey() method. First of all delete contents from the encrypt_num column in the 'users' table that we earlier used for storing the encrypted data of the column card_num. Once we have deleted the contents, we can again encrypt the data from the card_num column and store the result in encrypt_num column by using the EncryptByKey() method.

open symmetric key DemoKey decryption by certificate DemoCert;
use datademo
update [users] set [encrypt_num] =
EncryptByKey(Key_GUID('DemoKey'), card_num)

The above script opens up the symmetric key 'DemoKey' that is associated with the certificate DemoCert. While updating the column for encryption we pass the same key as a parameter to the Key_GUID variable, which is one of the parameters of the EncryptByKey() method. Now while decrypting, all you have to do is to open a session for the key DemoKey, and decrypt the encrypted data. Same key is needed for the encryption and the corresponding decryption process.

open symmetric key DemoKey decryption by certificate DemoCert;
use datademo
select ID,
encrypt_num,
cast(DecryptByKey(encrypt_num)
as varchar(16)) as "Decrypted"
from users;

This is a better method, as we do not have to pass the password for the process of encryption/decryption. All we have to do is to add an 'open symmetric key' phrase to each data manipulation command while programming, i.e. to each update, insert, or select statement. The key remains open till we explicitly close it. Here also, in real world scenario, we won't be saving any original data in the database table, but will be saving the encrypted data by use of keys. The DB admin may have created these keys.

Data encryption is the key to data security. With SQL Server 2005 you can secure your data by using the encryption or decryption functions that it provides.