Convert Existing Column to Computed Column or Create New One

Posted: July 30, 2012 | Author: | Filed under: SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2012 | Leave a comment

use mytest4;

go

–Option 1 to create the computed column during table creation

IF OBJECT_ID (N'[dbo].[mytable1]’, N’U’) IS NOT NULL

DROP TABLE [dbo].[mytable1];

GO

CREATE TABLE [dbo].[mytable1](

[id] [int] IDENTITY(1,1) NOT NULL,

[col1] [varchar](500) NULL,

[myComputedColumn] AS (‘SM’+CONVERT([varchar](10),[id],(0)))

,[myComputedColumn2] [varchar](500) NULL

) ON [PRIMARY]

GO

–Option 2.1 Check for an existing column to drop and create new

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘mytable1’ AND COLUMN_NAME = ‘myComputedColumn2’)

BEGIN

ALTER TABLE [dbo].[mytable1]

Drop column [myComputedColumn2]

ALTER TABLE [dbo].[mytable1]

Add [myComputedColumn2] as (‘SM’+CONVERT([varchar] (10),[Id],(0)))

END

–Or Option 2.2 Check for an existing column to drop and create new

IF COL_LENGTH(‘mytable1′,’myComputedColumn2’) IS Not NULL

BEGIN

ALTER TABLE [dbo].[mytable1]

Drop column [myComputedColumn2]

ALTER TABLE [dbo].[mytable1]

Add [myComputedColumn2] as (‘SM’+CONVERT([varchar] (10),[Id],(0)))

END

Passing Multiple Values in One Parameter to Query a Table

Posted: July 19, 2012 | Author: | Filed under: SQL Server 2005, SQL Server 2008, SQL Server 2012, Uncategorized | Leave a comment

This is a frequently asked question from users at ASP.NET forum. The solution to this problem to SPLIT the multiple values into a single column table and use this table to JOIN the target table we are querying. Here is a simple stored procedure to do this:

Create Procedure [dbo].[myPROCEDURE]

( @ID varchar(50)=NULL)

AS

Begin

WITH cte

AS (SELECT Cast(‘<M>’ + Replace( @id , ‘,’ ,'</M><M>’) + ‘</M>’ AS XML) AS id)

,mycte2 as

(SELECT S.a.value(‘.’, ‘VARCHAR(100)’) AS id FROM cte CROSS APPLY id.nodes(‘/M’) S(a))

Select * from mytable m INNER JOIN mycte2 t on m.id=t.id

End

 

Move Filestream Filegroup to New Location

Posted: July 10, 2012 | Author: | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment

There are a few ways to move Filestream data to new location but the easier way to do is to do a full backup and restore of the whole database with MOVE command.

Here is a sample snippet:

RESTORE DATABASE [FilestreamTestRestore2] FROM DISK = N’D:\MSSQL\FilestreamTest_FULL.bak’

WITH

–FILE = 1,

MOVE N’FilestreamTest’ TO N’D:\MSSQL\DATA\FilestreamTest.mdf’,

MOVE N’FilestreamTest_log’ TO N’D:\MSSQL\DATA\FilestreamTest_log.ldf’,

MOVE N’FilestreamFileGroup’ TO N’D:\MSSQL\DATA\myfileGroupNew’,

NOUNLOAD, REPLACE, STATS = 10

GO

Split a comma delimited column values into multiple rows with XML (No UDF Split function needed)

Posted: July 9, 2012 | Author: | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment

I have misreaded a post at ASP.NET forum (http://forums.asp.net/t/1821649.aspx/1?how+to+split+a+comma+seperated+value+in+a+table+into+diffrent+column+) and thought it should use a UDF function to get the answer but there is a handy solution with link in the post by Matt to point to http://mangalpardeshi.blogspot.com/2009/03/how-to-split-comma-delimited-string.html by Mangal Pardeshi .

Here is the sample from the thread:


CREATE TABLE #temptable2(firstlevel VARCHAR(200),ThirdLevel VARCHAR(8000));
INSERT INTO #temptable2 (firstlevel, ThirdLevel) VALUES ('count1', 'serv,dan,ton'),('count2','wers,tdan,tondrer,fhhgj,tern');

;WITH mycte
AS (SELECT FirstLevel, Cast('<M>' + Replace( ThirdLevel,',','</M><M>') +'</M>' AS XML) AS ThirdLevel
FROM #temptable2)

SELECT FirstLevel,S.a.value('.','VARCHAR(100)') AS ThirdLevel
FROM mycte
CROSS APPLY ThirdLevel.nodes('/M') S(a);

DROP TABLE #temptable2;