Validate Email Id in sqlserver2008

ALTER TABLE UserRegistration
WITH CHECK ADD
CONSTRAINT chkUserEmail
CHECK
(
CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 -- No embedded spaces
AND CHARINDEX('''', Email)=0
AND charindex('(', Email)=0 AND charindex(')', Email)=0
AND LEFT(LTRIM([Email]),1) <> '@' -- '@' can't be the first character of an email address
AND RIGHT(RTRIM([Email]),1) <> '.' -- '.' can't be the last character of an email address
AND CHARINDEX('.',[Email],CHARINDEX('@',[Email])) - CHARINDEX('@',[Email]) > 1 -- There must be a '.' after '@'
AND LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1 -- Only one '@' sign is allowed
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3 -- Domain name should end with at least 2 character extension
AND (CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0) -- can't have patterns like '.@' and '..'

)

BackUp database in sqlserver 2008

create procedure [dbo].[backupdb]

as

BACKUP DATABASE [Demo] TO DISK = N'C:\backup\mybackup.sql'

WITH NOFORMAT

, NOINIT

, NAME = N'test copy'

, SKIP

, NOREWIND

, NOUNLOAD

, STATS = 10

exec backupdb

MERGE statement in SQL Server 2008

MERGE is really a fantastic improvement in SQL Server 2008 which is really underutilized, I have seen many time recently that developers are still using separate DML statement for Insert / Update and Delete where there is a chance they can use MERGE statement of they can use condition based Insert / Update and Delete in one shot.

Example :

Create Table MemberPersonalDetail

(

MemberID INT Identity(1,1),

MemberName Varchar(20),

RegisterDate date,

ExpirationDate date

)

GO

INSERT INTO MemberPersonalDetail

SELECT 'Company','01/01/2000','12/31/2015' Union ALL

SELECT 'ItCompany','02/07/2005','06/20/2011' Union ALL

SELECT 'Marketing','06/22/2011','12/31/2015'

GO

SELECT * FROM MemberPersonalDetail

go

--create Member's login detail table and insert data in it.

CREATE TABLE MemberLoginDetail

(

MemberID INT,

UserName varchar(20),

UserPassword varchar(20)

)

GO

INSERT INTO MemberLoginDetail

SELECT 1,'Company','TestPassword' UNION ALL

SELECT 2,'ItCompany','goodluck'

GO

SELECT * FROM MemberLoginDetail

go

--MERGE statement with Insert / Update / Delete.....

--if you just need Insert / update or Insert / delete or Update / Delete anyting

-- you can use any combo

-- I have explained all three DML in one MERGE statement to demonstrate it.

MERGE MemberLoginDetail AS mld

USING (SELECT MemberID,MemberName,ExpirationDate FROM MemberPersonalDetail) AS mpd

ON mld.MemberID = mpd.MemberID

WHEN MATCHED AND mpd.ExpirationDate<getdate() THEN DELETE

WHEN MATCHED THEN UPDATE SET mld.UserPassword = 'DefaultPassword'

WHEN NOT MATCHED THEN

INSERT(MemberID,UserName,UserPassword)

VALUES(mpd.memberID,mpd.MemberName,'DefaultPassword');

GO

--check the table whether operation is successfully done or not.

SELECT * FROM MemberLoginDetail

go

Session Variable – Context_Info

Session is a powerful tool in any of the programming language.

--SQL-Server is not a full fledge programming language but it do supports session variable for current session or connection.

--It stores value of session in 128 byte of binary information.

--You can set or retrieve its value from CONTEXT_INFO column of the following system views

1. sys.dm_exec_requests

2. sys.dm_exec_sessions

3 .sys.sysprocesses

Now let’s have a look at example.

DECLARE @Fname VARCHAR(10), @Lname VARCHAR(10), @bVar binary(128)

SET @Fname='Company'

SET @Lname='IT'

SELECT @bVar=CONVERT(binary(6),@Fname)+CONVERT(binary(4),@Lname)

----- Set @bVar In CONTEXT_INFO Session-----

SET CONTEXT_INFO @bVar

---Now Select First Name And Last Name From Session Variable -

SELECT Fname = convert(varchar(10), substring(context_info, 1, 6))

FROM master..sysprocesses WHERE spid = @@spid

SELECT Lname = convert(varchar(10), substring(context_info, 7, 4))

FROM master..sysprocesses

WHERE spid = @@spid

@@spid is used to get the exact session ID. As I explained previously also, you can get the CONTEXT_INFO column in three system views so that you can query any of the views like:

SELECT context_info

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID;

SELECT context_info

FROM sys.dm_exec_requests

WHERE session_id = @@SPID;

SELECT context_info

FROM sys.sysprocesses

WHERE spid = @@SPID;

Dynamic Title and Meta Data on Content Page having Master Page attached

This is the most simple way to assign Title and Meta data on content page having Master Page attached.
First add a Master Page in your .net website. The removes the title tag from the master page.
Then add new page to your website and attach the master page with removed title tags. Now in the Page Load event of content page add the following line.

Below is the simple code on Content Page.

First Import using System.Web.UI.HtmlControls Namespace

and Than

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
HtmlTitle title = new HtmlTitle();
title.Text = "Dynamic Page";
Header.Controls.Add(title);

HtmlMeta tag = new HtmlMeta();
tag.Name = "keywords";
tag.Content = "key1, key2, key3, key4";
Header.Controls.Add(tag);

HtmlMeta des = new HtmlMeta();
des.Name = "desription";
des.Content = "This is a dynamic description allocated to the page.";
Header.Controls.Add(des);
}

}

How to delete duplicate records from a table in sql server

It is not possible to insert duplicate records in a table if using primary key constraints.But in relational database when foreign key is used it is possible that a duplicate records can insert in foreign key column.So solving that type of condition use that statement.
Example:

set rowcount

delete from table_name

where column_name=values

set rowcount 0

To delete all the duplicate rows you need to write a cursor as

DECLARE @id int
DECLARE @name nvarchar(50)
DECLARE @count int

DECLARE CUR_DELETE CURSOR FOR
SELECT [id],[name],COUNT([id]) FROM [table_name] GROUP BY [id],[name] HAVING COUNT([id]) > 1

OPEN CUR_DELETE

FETCH NEXT FROM CUR_DELETE INTO @id,@name,@count
/* Loop through cursor for remaining ID */
WHILE @@FETCH_STATUS = 0
BEGIN

DELETE TOP(@COUNT -1) FROM [table_name] WHERE ID = @id

FETCH NEXT FROM CUR_DELETE INTO @id,@name,@count
END

CLOSE CUR_DELETE
DEALLOCATE CUR_DELETE

How can select columns from table

Its very intersting question if we have 30 columns in sql table.And want write a sql statement to access only 29 columns from that table.But we don’t like write all column names.

DECLARE @ColumnList Varchar(1000), @SQLStatment VARCHAR(4000)

SET @ColumnList = ''

select @ColumnList = @ColumnList + Name + ' , ' from syscolumns where id =

object_id(‘your_table_name’) AND Name != 'your_column_nmae'

SELECT @SQLStatment = 'SELECT ' + Substring(@ColumnList,1,len(@ColumnList)-1)

+ ' From ‘your_table_name’

EXEC(@SQLStatment)