Script Data in SQL SERVER 2008

 

The script data is the new add-on feature on SQL SERVER 2008. Which helps to generate data along with the structure of database.

Steps to follow :

Step 1 : Right click on Database –> select Tasks –> Generate Script –> Select script options

-> select script data as True (as shown in figure) –> Do the rest of operation and Click Finish.It will generate the insert script along with the script generaed.

Here is the example of AdventureWorks –> table -> [HumanResources].[Employee]

USE [AdventureWorks]
GO
/****** Object:  Table [HumanResources].[Employee]    Script Date: 11/15/2009 08:00:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [HumanResources].[Employee](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[NationalIDNumber] [nvarchar](15) NOT NULL,
	[ContactID] [int] NOT NULL,
	[LoginID] [nvarchar](256) NOT NULL,
	[ManagerID] [int] NULL,
	[Title] [nvarchar](50) NOT NULL,
	[BirthDate] [datetime] NOT NULL,
	[MaritalStatus] [nchar](1) NOT NULL,
	[Gender] [nchar](1) NOT NULL,
	[HireDate] [datetime] NOT NULL,
	[SalariedFlag] [dbo].[Flag] NOT NULL,
	[VacationHours] [smallint] NOT NULL,
	[SickLeaveHours] [smallint] NOT NULL,
	[CurrentFlag] [dbo].[Flag] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for Employee records.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'EmployeeID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique national identification number such as a social security number.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'NationalIDNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the employee in the Contact table. Foreign key to Contact.ContactID.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'ContactID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Network login.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'LoginID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Manager to whom the employee is assigned. Foreign Key to Employee.M' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'ManagerID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Work title such as Buyer or Sales Representative.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Title'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date of birth.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'BirthDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'M = Married, S = Single' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'MaritalStatus'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'M = Male, F = Female' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Gender'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee hired on this date.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'HireDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'SalariedFlag'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of available vacation hours.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'VacationHours'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of available sick leave hours.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'SickLeaveHours'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 = Inactive, 1 = Active' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'CurrentFlag'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'rowguid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee information such as salary, department, and title.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'PK_Employee_EmployeeID'
GO
SET IDENTITY_INSERT [HumanResources].[Employee] ON
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (1, N'14417807', 1209, N'adventure-works\guy1', 16, N'Production Technician - WC60', CAST(0x0000674000000000 AS DateTime), N'M', N'M', CAST(0x000089CB00000000 AS DateTime), 0, 21, 30, 1, N'aae1d04a-c237-4974-b4d5-935247737718', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (2, N'253022876', 1030, N'adventure-works\kevin0', 6, N'Marketing Assistant', CAST(0x00006E7500000000 AS DateTime), N'S', N'M', CAST(0x00008A9D00000000 AS DateTime), 0, 42, 41, 1, N'1b480240-95c0-410f-a717-eb29943c8886', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (3, N'509647174', 1002, N'adventure-works\roberto0', 12, N'Engineering Manager', CAST(0x00005CAA00000000 AS DateTime), N'M', N'M', CAST(0x00008BBE00000000 AS DateTime), 1, 2, 21, 1, N'9bbbfb2c-efbb-4217-9ab7-f97689328841', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (4, N'112457891', 1290, N'adventure-works\rob0', 3, N'Senior Tool Designer', CAST(0x00005CD300000000 AS DateTime), N'S', N'M', CAST(0x00008BD600000000 AS DateTime), 0, 48, 80, 1, N'59747955-87b8-443f-8ed4-f8ad3afdf3a9', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (5, N'480168528', 1009, N'adventure-works\thierry0', 263, N'Tool Designer', CAST(0x000046D900000000 AS DateTime), N'M', N'M', CAST(0x00008BDC00000000 AS DateTime), 0, 9, 24, 1, N'1d955171-e773-4fad-8382-40fd898d5d4d', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (6, N'24756624', 1028, N'adventure-works\david0', 109, N'Marketing Manager', CAST(0x00005D2900000000 AS DateTime), N'S', N'M', CAST(0x00008BE500000000 AS DateTime), 1, 40, 40, 1, N'e87029aa-2cba-4c03-b948-d83af0313e28', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (7, N'309738752', 1070, N'adventure-works\jolynn0', 21, N'Production Supervisor - WC60', CAST(0x000041CF00000000 AS DateTime), N'S', N'F', CAST(0x00008BEB00000000 AS DateTime), 0, 82, 61, 1, N'2cc71b96-f421-485e-9832-8723337749bb', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (8, N'690627818', 1071, N'adventure-works\ruth0', 185, N'Production Technician - WC10', CAST(0x0000425B00000000 AS DateTime), N'M', N'F', CAST(0x00008BF600000000 AS DateTime), 0, 83, 61, 1, N'3e3b6905-209e-442e-b8a8-9a0980241c6a', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (9, N'695256908', 1005, N'adventure-works\gail0', 3, N'Design Engineer', CAST(0x00003D1900000000 AS DateTime), N'M', N'F', CAST(0x00008BF600000000 AS DateTime), 1, 5, 22, 1, N'ec84ae09-f9b8-4a15-b4a9-6ccbab919b08', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (10, N'912265825', 1076, N'adventure-works\barry0', 185, N'Production Technician - WC10', CAST(0x0000421500000000 AS DateTime), N'S', N'M', CAST(0x00008BF700000000 AS DateTime), 0, 88, 64, 1, N'756a60ae-378f-43d8-9f93-1e821d1eaf52', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (11, N'998320692', 1006, N'adventure-works\jossef0', 3, N'Design Engineer', CAST(0x0000464D00000000 AS DateTime), N'M', N'M', CAST(0x00008C0800000000 AS DateTime), 1, 6, 23, 1, N'e39056f1-9cd5-478d-8945-14aca7fbdcdd', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (12, N'245797967', 1001, N'adventure-works\terri0', 109, N'Vice President of Engineering', CAST(0x000057FB00000000 AS DateTime), N'S', N'F', CAST(0x00008C0F00000000 AS DateTime), 1, 1, 20, 1, N'45e8f437-670d-4409-93cb-f9424a40d6ee', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (13, N'844973625', 1072, N'adventure-works\sidney0', 185, N'Production Technician - WC10', CAST(0x000042B200000000 AS DateTime), N'M', N'M', CAST(0x00008C1100000000 AS DateTime), 0, 84, 62, 1, N'40d603d9-7f99-48b7-a580-b17cf429bed2', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (14, N'233069302', 1067, N'adventure-works\taylor0', 21, N'Production Supervisor - WC50', CAST(0x0000421B00000000 AS DateTime), N'M', N'M', CAST(0x00008C1700000000 AS DateTime), 0, 79, 59, 1, N'28f7ef89-2793-4989-b67d-25046543c1e3', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (15, N'132674823', 1073, N'adventure-works\jeffrey0', 185, N'Production Technician - WC10', CAST(0x0000428000000000 AS DateTime), N'S', N'M', CAST(0x00008C2300000000 AS DateTime), 0, 85, 62, 1, N'6e086f41-b81d-4bdc-9f13-0eec488dc43e', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (16, N'446466105', 1068, N'adventure-works\jo0', 21, N'Production Supervisor - WC60', CAST(0x000042D900000000 AS DateTime), N'S', N'F', CAST(0x00008C2A00000000 AS DateTime), 0, 80, 60, 1, N'83ffaac6-b895-481f-b897-14f965d4da47', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (17, N'565090917', 1074, N'adventure-works\doris0', 185, N'Production Technician - WC10', CAST(0x0000421E00000000 AS DateTime), N'M', N'F', CAST(0x00008C3600000000 AS DateTime), 0, 86, 63, 1, N'692b8826-03b4-4c3b-82fc-1fc6f1409689', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (18, N'494170342', 1069, N'adventure-works\john0', 21, N'Production Supervisor - WC60', CAST(0x0000429B00000000 AS DateTime), N'M', N'M', CAST(0x00008C3D00000000 AS DateTime), 0, 81, 60, 1, N'd4ed1f78-7c28-479b-bfef-a73228ba2aaa', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (19, N'9659517', 1075, N'adventure-works\diane0', 185, N'Production Technician - WC10', CAST(0x0000421800000000 AS DateTime), N'M', N'F', CAST(0x00008C4800000000 AS DateTime), 0, 87, 63, 1, N'c334b2d2-0c56-4906-9095-f1d07a98cbec', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (20, N'443968955', 1129, N'adventure-works\steven0', 173, N'Production Technician - WC30', CAST(0x0000603C00000000 AS DateTime), N'M', N'M', CAST(0x00008D4000000000 AS DateTime), 0, 41, 40, 1, N'0a8937c9-68aa-4d48-ba4e-b40493d764aa', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (21, N'277173473', 1231, N'adventure-works\peter0', 148, N'Production Control Manager', CAST(0x0000680B00000000 AS DateTime), N'M', N'M', CAST(0x00008D4000000000 AS DateTime), 1, 43, 41, 1, N'69d5d162-e817-45e7-9dec-5d9b8310e7b1', CAST(0x000095E80099062F AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (22, N'835460180', 1172, N'adventure-works\stuart0', 197, N'Production Technician - WC45', CAST(0x00004B4F00000000 AS DateTime), N'S', N'M', CAST(0x00008D4100000000 AS DateTime), 0, 84, 62, 1, N'4f361b4f-7920-4037-9a0a-46d616f9b9c7', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (23, N'687685941', 1173, N'adventure-works\greg0', 197, N'Production Technician - WC45', CAST(0x000056DC00000000 AS DateTime), N'S', N'M', CAST(0x00008D4100000000 AS DateTime), 0, 85, 62, 1, N'a36b5d6b-72a5-47f8-9f6b-5d922130e760', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (24, N'498138869', 1113, N'adventure-works\david1', 184, N'Production Technician - WC30', CAST(0x000063C200000000 AS DateTime), N'S', N'M', CAST(0x00008D4100000000 AS DateTime), 0, 25, 32, 1, N'ed7ee92e-a9d0-4e3a-8dee-4b143e459f7e', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (25, N'360868122', 1054, N'adventure-works\zheng0', 21, N'Production Supervisor - WC10', CAST(0x0000697000000000 AS DateTime), N'S', N'M', CAST(0x00008D4200000000 AS DateTime), 0, 66, 53, 1, N'c75f2740-ace3-4ebf-91c5-7ab352e1095f', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (26, N'964089218', 1097, N'adventure-works\ivo0', 108, N'Production Technician - WC20', CAST(0x000066DB00000000 AS DateTime), N'M', N'M', CAST(0x00008D4300000000 AS DateTime), 0, 9, 24, 1, N'd83a26f3-7ea7-477b-a5bb-7eaadfc13a09', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (27, N'384162788', 1156, N'adventure-works\paul0', 87, N'Production Technician - WC40', CAST(0x0000653B00000000 AS DateTime), N'S', N'M', CAST(0x00008D4300000000 AS DateTime), 0, 68, 54, 1, N'0217049e-1566-42b6-8027-41b751e2b00b', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (28, N'749389530', 1258, N'adventure-works\ashvini0', 150, N'Network Administrator', CAST(0x0000600C00000000 AS DateTime), N'S', N'M', CAST(0x00008D4300000000 AS DateTime), 0, 70, 55, 1, N'2e680c1c-7c02-4343-9626-6f97273414c0', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (29, N'571658797', 1199, N'adventure-works\kendall0', 14, N'Production Technician - WC50', CAST(0x00006D2300000000 AS DateTime), N'M', N'M', CAST(0x00008D4400000000 AS DateTime), 0, 11, 25, 1, N'65207627-5521-4a8e-ad3c-b2f1b5226b85', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (30, N'535145551', 1242, N'adventure-works\paula0', 140, N'Human Resources Manager', CAST(0x00005E7200000000 AS DateTime), N'M', N'F', CAST(0x00008D4500000000 AS DateTime), 1, 54, 47, 1, N'65028f15-4149-4de5-b203-9d7ec86baf87', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (31, N'761597760', 1140, N'adventure-works\alejandro0', 210, N'Production Technician - WC40', CAST(0x000070BB00000000 AS DateTime), N'S', N'M', CAST(0x00008D4500000000 AS DateTime), 0, 52, 46, 1, N'8c7321d2-2c64-4902-9d64-8f5c1eb7f747', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (32, N'271438431', 1122, N'adventure-works\garrett0', 184, N'Production Technician - WC30', CAST(0x00006AA000000000 AS DateTime), N'S', N'M', CAST(0x00008D4600000000 AS DateTime), 0, 34, 37, 1, N'a6f5f72f-aa03-41b7-8b6e-b123bd360753', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (33, N'160739235', 1124, N'adventure-works\jianshuo0', 135, N'Production Technician - WC30', CAST(0x0000691500000000 AS DateTime), N'S', N'M', CAST(0x00008D4600000000 AS DateTime), 0, 36, 38, 1, N'6fff136a-9664-4eb9-9243-fac76e21b9dd', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (34, N'586486572', 1285, N'adventure-works\susan0', 85, N'Stocker', CAST(0x0000615300000000 AS DateTime), N'S', N'F', CAST(0x00008D4600000000 AS DateTime), 0, 97, 68, 1, N'923ecfd6-e202-429a-9141-6cb183531439', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (35, N'337752649', 1283, N'adventure-works\vamsi0', 85, N'Shipping and Receiving Clerk', CAST(0x0000600300000000 AS DateTime), N'M', N'M', CAST(0x00008D4600000000 AS DateTime), 0, 95, 67, 1, N'a8838ab2-99de-415c-8675-d82ebf1c4752', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (36, N'113695504', 1183, N'adventure-works\alice0', 38, N'Production Technician - WC50', CAST(0x0000613D00000000 AS DateTime), N'M', N'F', CAST(0x00008D4600000000 AS DateTime), 0, 95, 67, 1, N'7e632b21-0d11-4bba-8a68-8cae14c20ae6', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (37, N'276751903', 1226, N'adventure-works\simon0', 7, N'Production Technician - WC60', CAST(0x000072CB00000000 AS DateTime), N'S', N'M', CAST(0x00008D4700000000 AS DateTime), 0, 38, 39, 1, N'f734d38a-56b5-471e-8ded-a682e958a9b3', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (38, N'630184120', 1065, N'adventure-works\jinghao0', 21, N'Production Supervisor - WC50', CAST(0x000070F900000000 AS DateTime), N'S', N'M', CAST(0x00008D4700000000 AS DateTime), 0, 77, 58, 1, N'2bca07d3-f2ac-4406-904f-e09156f3eb3e', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (39, N'545337468', 1108, N'adventure-works\michael0', 182, N'Production Technician - WC20', CAST(0x00006AF400000000 AS DateTime), N'S', N'M', CAST(0x00008D4800000000 AS DateTime), 0, 20, 30, 1, N'4fd6fe82-535a-4dd1-beb1-154a8b5e42f0', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (40, N'713403643', 1167, N'adventure-works\yvonne0', 159, N'Production Technician - WC45', CAST(0x0000713E00000000 AS DateTime), N'M', N'F', CAST(0x00008D4800000000 AS DateTime), 0, 79, 59, 1, N'83c6dabf-fd7d-4f5b-bb22-eb7eebb9a772', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (41, N'885055826', 1269, N'adventure-works\peng0', 200, N'Quality Assurance Supervisor', CAST(0x00005E9600000000 AS DateTime), N'M', N'M', CAST(0x00008D4800000000 AS DateTime), 1, 81, 60, 1, N'e249d613-36c9-4544-9b6f-6ce50e5e0da5', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (42, N'441044382', 1253, N'adventure-works\jean0', 109, N'Information Services Manager', CAST(0x00005E3600000000 AS DateTime), N'S', N'F', CAST(0x00008D4A00000000 AS DateTime), 1, 65, 52, 1, N'794a0b1f-c46a-401c-984d-008996fc7092', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (43, N'718299860', 1194, N'adventure-works\russell0', 74, N'Production Technician - WC50', CAST(0x000059DD00000000 AS DateTime), N'M', N'M', CAST(0x00008D4B00000000 AS DateTime), 0, 6, 23, 1, N'6b10192f-d570-47c4-82c9-3d979b1efdc1', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (44, N'685233686', 1232, N'adventure-works\ascott0', 148, N'Master Scheduler', CAST(0x000053E300000000 AS DateTime), N'S', N'M', CAST(0x00008D4B00000000 AS DateTime), 0, 44, 42, 1, N'13909262-4136-492f-bca3-0b0e3773b03e', CAST(0x000095E80099062F AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (45, N'295971920', 1135, N'adventure-works\fred0', 210, N'Production Technician - WC40', CAST(0x0000718500000000 AS DateTime), N'S', N'M', CAST(0x00008D4B00000000 AS DateTime), 0, 47, 43, 1, N'45358ae8-0b0e-4c11-90bb-dac3ec0d5c82', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (46, N'95958330', 1033, N'adventure-works\sariya0', 6, N'Marketing Specialist', CAST(0x00006E8700000000 AS DateTime), N'S', N'M', CAST(0x00008D4B00000000 AS DateTime), 0, 45, 42, 1, N'af21f1b7-0691-48ad-b325-b8f2d7b2268a', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (47, N'332040978', 1237, N'adventure-works\willis0', 30, N'Recruiter', CAST(0x000061EA00000000 AS DateTime), N'S', N'M', CAST(0x00008D4C00000000 AS DateTime), 0, 49, 44, 1, N'a1d86ecc-ea17-4b1b-8369-f1d07ab0a469', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (48, N'857651804', 1178, N'adventure-works\jun0', 38, N'Production Technician - WC50', CAST(0x0000634B00000000 AS DateTime), N'S', N'M', CAST(0x00008D4D00000000 AS DateTime), 0, 90, 65, 1, N'9c5f84d1-fb96-4d82-92a1-f932903deaad', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (49, N'553069203', 1280, N'adventure-works\christian0', 218, N'Maintenance Supervisor', CAST(0x00005E5A00000000 AS DateTime), N'M', N'M', CAST(0x00008D4D00000000 AS DateTime), 1, 92, 66, 1, N'4611c7c5-90a0-407f-b054-93bd51533609', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[Employee] ([EmployeeID], [NationalIDNumber], [ContactID], [LoginID], [ManagerID], [Title], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate]) VALUES (50, N'351069889', 1119, N'adventure-works\susan1', 184, N'Production Technician - WC30', CAST(0x000068A100000000 AS DateTime), N'S', N'F', CAST(0x00008D4D00000000 AS DateTime), 0, 31, 35, 1, N'f73d75d5-b47b-46ee-ad11-7bf13dd6c55d', CAST(0x0000953500000000 AS DateTime))
INSERT [HumanResources].[E

ALTER ALL TABLE,Procedure and UDF SCHEMA IN A SINGLE QUERY

 

By using this query we can alter the schema to SCHEMANAME .

select 'ALTER SCHEMA SCHEMANAME transfer ['+SCHEMA_NAME(schema_id)+'].['+name +']' from sys.tables

OUTPUT

select all the query and execute in query window.All the schema will automatically changes to SCHEMANAME

Note :sys.tables – Returns a row for each table object ,currently only with

sys.objects.type =U

SCHEMA_NAME(schema_id) – Returns the schema name associated with schema_id

schema_id – ID of the schema.Schema_id is a int. If schema is not defined schema name of default will return

E.g : select SCHEMA_NAME() from sys.tables

OUTPUT

For Procedure

select 'ALTER SCHEMA SCHEMANAME transfer ['+SCHEMA_NAME(schema_id)+'].['+name +']' from sys.procedures

For UDF

select 'ALTER SCHEMA SCHEMANAME transfer ['+specific_schema+'].['+specific_name +']'
FROM information_schema.routines
WHERE routine_type='function'

Note : routines - Returns one row for each stored procedure and function that can be accessed by the current user in the current database.

To find server address using sql server 2005

@@ServerName :returns the name of the local server that is running in sql server.

more details

Common Table Expression(CTE) and ROW_NUMBER()

can be thought of as a temporary result set that is defined with in the execution of the scope of a single select,insert,update,delete or create view statement.

A CTE can be used to :

• create a recursive query .
• substitute for a view when the general use of a view is not required ,that is you dont have to store the definition in metadata
• enable grouping by a column that is derived from a scalar subset ,or a function that is not deterministic or has extrenal access.
• reference the resulting table multiple time in the same statement.

Syntax

with expression_name [(column name [,....n])]
AS
(CTE query definition)

E.g :with [EMP_SCHED order by rowid] as
(select row_number() over (order by emp_id asc) as rowid ,* from EMP_SCHED)
select * from [EMP_SCHED order by rowid] where rowid=3

Row_Number()

Returns the sequential number of a row with in a partition of a result set ,starting at 1 for the first in each partition

Syntax
ROW_NUMBER() OVER ([ ] )

E.g :with [EMP_SCHED order by rowid] as
(select row_number() over (order by emp_id asc) as rowid ,* from EMP_SCHED)
select * from [EMP_SCHED order by rowid] where rowid=3

Set single user mode in sql server 2oo5

The single user-mode specifies that only one user at a time can access the database and is generally used for maintenance actions

To set by using SQL query
Set single user mode

ALTER DATABASE [DATABASENAME] SET SINGLE_USER WITH NO_WAIT

Set back to multi user

ALTER DATABASE [DATABASENAME] SET MULTI_USER WITH NO_WAIT

Change schema on table and proc in SQL SERVER 2005

To change the schema table from one to another is by executing the query

select 'ALTER SCHEMA dbo TRANSFER ' + s.Name+ '.' + p.Name
FROM sys.tables p
inner join
sys.Schemas s on p.schema_id = s.schema_id


Result :

• ALTER SCHEMA dbo TRANSFER aniSchema.video_master
• ALTER SCHEMA dbo TRANSFER aniSchema.enquiry_master
• ALTER SCHEMA dbo TRANSFER aniSchema.blog_master

Copy the result and execute the result in new query then the schema(aniSchema) is changed to dbo .

OR
For tables and views

SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'aniSchema'

And do the above step after getting the result

ER Diagram in Sql server 2005

To see the ER diagram for sql server 2005.

• Right click the Database Diagrams on the particular table and select new database diagram

Error i Got

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

How i Solved

• Right on database ,Choose properties
• Go to options menu
• check whether the compatibility level :SQL Server 2005(90)
• Click ok

Still i got the same error . After that i executed
ALTER AUTHORIZATION ON DATABASE::DatabaseName TO sa

Every thing working fine and i got ER diagram

Joining two tables Using UNION

two tables

//No Error

select Fname,Lname from tbl_Master

union

select Fname,Lname from tbl_Master

Two proc

//Error

exec SPtbl_Master1

union

exec SPtbl_Master2

Two Table-valued Functions

//No Error

select * from Fntbl_Master1('p')

union

select * from Fntbl_Master2('p')

charindex Vs patindex

charindex : Returns the starting position of the first occurrence of a pattern in a specified expression ,or zero if the pattern is not matched

E.g: select charindex('+','anish+varghese')

Result : 6

patindex:Returns the starting position if found.And it can use wildcard characters

E.g: select patindex('%+%','anish+varghese')

Result : 6

Where is the difference ?

Actually patindex act as a like operator .

% : any string of zero or more characters

E.g : select charindex('%+%','anish+varghese')

Result : 0

E.g: select patindex('%+%','anish+varghese')

Result : 6

E.g :select patindex('%+','anishvarghese+')

Result :14

E.g:select patindex('+%','+anishvarghese')

Result :1

_(underScore) :any single character

E.g:select patindex('%_r%','anishvarghese')

Result :7

[ ] : Any single character with in the specified range [a-z] or ser [abcd]

E.g:select patindex('%[0-9]%','anish4varghese')

Result:6

[^] :Any sigle character not with in the specified range [^a-z] or set [^abcd]

E.g:select patindex('%h%','anish4varghese')

Result :5

E.g:select patindex('%h[^4]%','anish4varghese')

Result :11

sp_spaceused system procedure

This will tells whatt is the physical space used by the system and the objects

exec sp_spaceused 'databasename'

for more details Click Here

Difference between Primary key and unique key?

Primary Key : Creates uniqueness for the column.And also creates clustered index on the column,by default

Unique Key :Creates uniqueness for the column.By default it creates a Non clustered index

To know more about Clustered index and non Clustered index.

Note : try to avoid using GUID as primaryKey (it will waste of your space (16 bytes)).If the database volume is high it will affect performance.if use int it will take less space(4 bytes).

Union and UnionAll

Union:its Like a join Command.When using the union all the selected columns need to be of the same datatype.Only distinct values are selected.

Union All :it almost like union.There is no distinct operation so it will take all the values.

Rules for Union
a union must be composed of two or more select statement and each is separated by a keyword union
Each union must contain same column,expression or aggregate function.
Column name need not be same ,but they must be the type that the sql can implicitly convert.

NEWSEQUENTIALID()

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer.

CREATE TABLE myGUIDTable (myCol uniqueidentifier DEFAULT NEWSEQUENTIALID())

this is grate to see the id is created based on the machine id .

E.g: id created for me B0DD13B4-FB66-DE11-A148-001CC031A29A

my Physical address of my machine is 00-1C-C0-31-A2-9A

To know you ip address this will help you click here

to see your machine address type ipconfig/all

you can see physical address : that is same as the last 12 digits of your id

They introduced this to improve the performance of uniqueidentifiers when inserting

to get more information click here

Temporary table in SQL

There are two types of temporary tables are available in sql server

1) Local Temporary table

2) Global Temporary table

Local Temporary table: They are visible to only the current connection of the user.For each user it will create a temporary table along with a session value and delete from the system temp table once the inistance of the user to the particular table has gone.To add this table add a sign(#) before the table.

E.g:CREATE TABLE #tblTemp

(

col1 int,etc

)

2) Global Temporary table:They are visible to any user after they are created and delete when the user references the table disconnect from the sql server.To add this table add a sign(##) before the table.

E.g:CREATE TABLE ##tblTemp

(

col1 int,etc

)

Advantages :by using this we can avoid cursors and can do as a prameterized views.

Limitations :dont use properly it will reduce the overall perfromance.

Using Temporary Tables Effectively

If you do not have any option other than to use temporary tables, use them affectively. There are few steps to be taken.

• Only include the necessary columns and rows rather than using all the columns and all the data which will not make sense of using temporary tables. Always filter your data into the temporary tables.
• When creating temporary tables, do not use SELECT INTO statements, Instead of SELECT INTO statements, create the table using DDL statement and use INSERT INTO to populate the temporary table.

e.g :select a into #tblani from dbo.table_execplan --Dont use this one

e.g:

CREATE TABLE #tbl_ani1 (col1 nchar) INSERT #tbl_ani1 (col1) (select a from dbo.table_execplan)--try to use this one

• After you finish the using your temporary table, delete them. This will free the tempdb resources. Temporary tables are deleted when connection is ended. but do not wait until such time.
• When creating a temporary table do not create them with a transaction. If you create it with a transaction, it will lock some system tables (syscolumns, sysindexes, syscomments). This will prevent others from executing the same query.

linked server

Linked server is used to execute command against OLEDB datasource on different server.

Advantages :

· Remote server access

· Distibuted query ,update,command and transaction on heterogeneous datasource across the enterprice.

· Ability to address diverse datasource similarity

Linked Server components :

Oledb provider and oledb datasource

Oledb provider-is a dll that manages and interact with specific

Linked server details

When a client appln execute a distributed query through a linked server ,sql server break down into command and send it to rowset and req to oledb. The row set exec and open the table from the provider.

Managing linked server Definition

To work in linked server ,register the connection formula and datasource information to sql server .After registration datasource ll always reffer to the logical name provided.

e.g

EXEC

sp_addlinkedserver @server='74.55.278.251',
@srvproduct='',
@provider='SQLNCLI',
@provstr = 'DRIVER={SQL server }; server=databasename;UID=username;PWD=password;'
@datasrc='74.55.278.251'

by using proc we can add and delete the linked server

sp_addlinkedserver – to add the linked server.

Sp_linkedservers –to see the information in the linked serer.

Sp_addlinkedserver and sp_linkedservers to see more information

The tables affected are

For server details:

select * from sys.servers

For linked server Login Details :

select * from sys.linked_logins

sp_dropserver – used to delete the added server and also we can remove the remote server.

By using sql server enterprise Manager

I am not explaining this.

Identifying a Data Source Using a Linked Server Name

After registered a linked server four part name that help for the transaction.

Part Name Desc

Linked_server_name Linked server referencing the the oleDB data source

Catalog Catalog in oledb datasource Containing object

Schema Schema in the catalog contain the object

Object_name Data object in the schema

What is Execution plan? in sql server 2005

Some of the times your proc is running slow. Because you are not looked into the execution plan.Here i explain how the execution plan is created.

Execution plan: Result of an query optimizer(that calculate the most efficient way to implement the T-sql query you submitted).By looking into this u can find out what is the problem in your query, if there is any problem.

What happens when a query is submitted?

The Query split into two stages

1) Processes that occur in relational engine.

2) Process that occur in Storage Engine.

Relational Engine

Relational engine parse the query and processed by query optimizer and produce an Execution plan.

Storage Engine

The result produce from the execution plan is sent(is a binary data) to storage engine, where data are stored and do operations like (locking, indexing transactions etc occur).

Execution plan

Execution plan is happens in relational Engine.

The following steps are happened in relational engine to produce the Execution plan.

a) Query parsing

Process: Checks the syntax

Output: Parse tree or query tree or process tree

Note: the parse tree contain the logical steps to the necessary operations for query.

If T-Sql is not a DML. So there are no opportunities to improve the performance. if T-sql is a DML So the parse tree is passing through the algebrizer.

DML: which helps developers to retrieve, insert, delete and update data in database.

Algebrizer: (process in query parsing)

Process: Resolves various name object, tables and columns.

Note: identify the each column fields (e.g.: varchar (200), int etc).It also determines the location of aggregate (group by and max etc) in the query and the process is called aggregate binding.

Algebrizer output: binary tree called query processor tree, which passed on to query optimizer.

b) Query optimizer

Which is an essential process model and says which relational engine works. Based on query processor tree and relational engine it produces the execution plan.

Cost based plan

This steps analyze which joins are used. Which calculate the cost of the execution plan, in terms of CPU.

In this step the optimizer plans which is the best plan to reduce the execution time.based on the cpu and i/o it produces the new query.

Trivial plan

If you submit a very simple query – for example, a single table with no indexes and with no aggregates or calculations within the query – then rather than spend time trying to calculate the absolute optimal plan, the optimizer will simply apply a single, trivial plan to these types of queries

Non Trivial plan

If it is a non trivial plan it performs cost based calculation. For this looks statistics maintained by sql.

Statistics: collection of data columns and indexers in data base and data distribution among them. The information for statistic is maintained by a histogram.

Histogram: tabulation of occurrence of particular values.

Plan cache: once optimizer creates execution plan this plan stored in a memory known as plan cache. if the identical plan is not there in the cache it goes to generate potential plan.

Query Execution

Once the execution plan is created it goes to storage engine and execute according to the plan.

Estimated and actual Execution plan

Two types of execution plans are there

Estimated plan: output from the query optimizer.

Actual plan: Actual query is execution.

Reuse of execution plan

The execution plan is stored in plan cache. The plan won’t store forever .as they aged out based on the age formula, it calculates the number of times this has been used. Lazywirter process(as garbage collector in .net) internally checks the all types of cache periodically scans the objects in the cache.

Following criteria removes the memory from the cache .

· More memory required by the system.

· The “age” plan reaches zero

· Plan is not being referred by the existing collection.

Plan can cause recompiled in the following ways

• Changing the structure or schema of a table referred by the query.
• Changing the index used by the query.
• Dropping index used by the query.
• Updating statistics used by the query.
• Calling sp_recompile
• Mixing DDL and DML .

Note: DDL (data definition language) descries the records, fields and sets makeup by the user. E.g.: create table alter table etc.