Created a Table Called Temp :

CREATE TABLE TEMP (ID Int, Name Varchar(50))

Insert some values into the Table Temp :

INSERT INTO TEMP (ID, Name)
SELECT 1, 'VASANTH' UNION ALL
SELECT 2, 'RAJA' UNION ALL
SELECT 3, 'KARTIK'

To View the Inserted Records in the Table Temp :

SELECT * FROM TEMP

Stored procedure to Insert the Record in the Table Temp with XML Value as Input Parameter

CREATE PROCEDURE usp_InsertTemp
@XMLValue VARCHAR(MAX)
AS
BEGIN

DECLARE @XmlVar INT

EXEC sp_xml_preparedocument @XmlVar OUTPUT, @XMLValue

SELECT ID, Name INTO #TEMP FROM OPENXML(@Xmlvar, '/InsertTemp/Val', 2)
WITH(ID int, Name varchar(50))

EXEC sp_xml_removedocument @XmlVar

INSERT INTO temp (ID, Name) SELECT ID, Name From #TEMP

END

To execute the Stored Procedure :

EXEC usp_InsertTemp
'<InsertTemp>

<Val><ID>4</ID><Name>Singh</Name></Val>

<Val><ID>5</ID><Name>Vinoth</Name></Val>

<Val><ID>6</ID><Name>prakash</Name></Val>

<Val><ID>7</ID><Name>Karu</Name></Val>

</InsertTemp>'

The XML is framed like

In the Open XML Function the RootNode and ParentNode has to be defined clearly.

when an error occurs the processing in the TRY block stops and processing is then picked up in the CATCH block.

* Each TRY block is associated with only one CATCH block and must be followed immediately by an associated CATCH block.

* The CATCH block is executed only when any error occurs. If no errors, then the next statement after the END CATCH is executed.

* TRY-CATCH constructs can be nested.

* On ERROR, the control passes to the first statement in the associated CATCH block.

Here it is done with an example :

Step 1 : Created a stored procedure for a non-existent table - Temp

CREATE PROCEDURE usp_TestingException
as
BEGIN
SELECT * FROM Temp
END

Step 2 : Run the stored procedure using the below code, the error is sent to the CATCH block and an error message is returned. At this point processing can continue without a problem.

Example 1:

BEGIN TRY
EXECUTE usp_TestingException
END TRY

BEGIN CATCH
SELECT
Error_Number() as ErrorNumber,
Error_Severity() as ErrorSeverity,
Error_State() as ErrorState,
Error_procedure() as ErrorProcedure,
Error_Line() as ErrorLine,
Error_Message() as ErrorMessage
END CATCH

Example 2 :

BEGIN TRY
SELECT GETDATE()
SELECT 1/0
END
BEGIN CATCH
SELECT
Error_Number() as ErrorNumber,
Error_Severity() as ErrorSeverity,
Error_State() as ErrorState,
Error_procedure() as ErrorProcedure,
Error_Line() as ErrorLine,
Error_Message() as ErrorMessage
END CATCH

CREATE PROCEDURE usp_CheckAmstrongNumber
DECLARE @InputNumber INT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @IncNo INT
DECLARE @TotalCnt INT
DECLARE @AmstrongNo INT

SET @AmstrongNo = @InputNumber
SET @IncNo = 0
SET @TotalCnt = 0

WHILE (LEN(@AmstrongNo) >= @IncNo)
BEGIN

SET @TotalCnt = @TotalCnt + POWER(@InputNumber % 10, LEN(@AmstrongNo))
SET @InputNumber = @InputNumber / 10
SET @IncNo = @IncNo + 1

END

IF(@AmstrongNo = @TotalCnt)
SELECT 'Amstrong Number'
ELSE
SELECT 'Not a Amstrong Number'

END
GO

CREATE PROCEDURE [dbo].[usp_UpdateTableRecords]
-- Add the parameters for the stored procedure here
@TableName Varchar(100) = 'tbl_Name', -- Name of the Table
@Location Varchar(MAX) = 'C:\DBUpdate\Table\tbl_Name.csv' -- Excel File Location
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--Variable Declaration
DECLARE @ColName VARCHAR(MAX)
DECLARE @Condition as VARCHAR(MAX)
DECLARE @Var as VARCHAR(100)
DECLARE @Qry as VARCHAR(MAX)
DECLARE @ColumnName VARCHAR(MAX)
DECLARE @Condition1 as VARCHAR(MAX)
DECLARE @ColumnName1 VARCHAR(MAX)
DECLARE @FieldName VARCHAR(MAX)
SET @Condition = ''
SET @Condition1 = ''
SET @ColumnName1 = ''
-- Insert statements for procedure here

IF EXISTS(SELECT * FROM Sysobjects WHERE type = 'u' and name = 'tbl_TEMP1234')
DROP TABLE tbl_TEMP1234

SET @Qry = 'Select * into tbl_TEMP1234 from ' + @TableName + ' where 1 = 2'
EXEC (@Qry)

SET @Qry = 'Bulk Insert tbl_TEMP1234 From ''' + @Location + ''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'
EXEC (@Qry)

SELECT @ColName = Coalesce(@ColName,'') + KU.Column_name + ',' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND KU.Table_name = @TableName

IF(@colName <> '')
BEGIN
WHILE (@ColName != '')
BEGIN
SET @Var = (SELECT LTRIM(SUBSTRING(@Colname, 1,charindex(',', @Colname)-1)))
SET @Condition = @Condition + (@Var + ' Not in (SELECT ' + @Var + ' From ' + @TableName + ') and ')
SET @Condition1 = @Condition1 + (@TableName + '.' + @Var + ' = tbl_TEMP1234.'+ @Var + ' and ')
SET @ColName = (CASE WHEN (LEFT(@ColName, 1) != ',') THEN SUBSTRING(@ColName, charindex(',', @ColName) + 1, LEN(@ColName)) ELSE '' END)
END

SET @Condition = (SELECT Substring(@Condition, 1, Len(@Condition) - 4))
SET @Condition1 = (SELECT Substring(@Condition1, 1, Len(@Condition1) - 4))

SELECT @ColumnName = Coalesce(@ColumnName,'') + b.NAME + ',' FROM syscolumns b
INNER JOIN sysobjects a ON a.id = b.id AND a.TYPE = 'u' AND a.NAME = @TableName

SET @FieldName = @ColumnName
WHILE (@FieldName != '')
BEGIN
SET @Var = (SELECT LTRIM(SUBSTRING(@FieldName, 1,charindex(',', @FieldName)-1)))
SET @ColumnName1 = (@ColumnName1 + @TableName + '.' + @Var + ' = tbl_TEMP1234.'+ @Var + ',')
SET @FieldName = (CASE WHEN (LEFT(@FieldName, 1) != ',') THEN SUBSTRING(@FieldName, charindex(',', @FieldName) + 1, LEN(@FieldName)) ELSE '' END)
END

SET @ColumnName = (SELECT Substring(@ColumnName, 1, Len(@ColumnName) - 1))
SET @ColumnName1 = (SELECT Substring(@ColumnName1, 1, Len(@ColumnName1) - 1))

SET @Qry = ('UPDATE ' + @TableName + ' SET ' + @columnName1 + ' FROM tbl_TEMP1234 WHERE ' + @Condition1)
EXEC (@Qry)

SET @Qry = ('INSERT INTO ' + @TableName + '(' + @ColumnName + ')
SELECT * FROM tbl_TEMP1234 WHERE ' + @Condition)
EXEC (@Qry)
END

DROP TABLE tbl_TEMP1234

END

Similarly we can upload the file with any field and row Terminator

Data integrity and Constraints :

Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.
The Various Constrains are
1. Primary Key
2. Foreign Key
3. Unique
4. Check
5. Not Null

Identity :

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at
A GUID column also generates numbers, the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

De-normalization :

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

Difference between DELETE and TRUNCATE commands :

* Delete command removes the rows from a table based on the condition that we provide with a WHERE clause.

* Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

Difference between HAVING and WHERE CLAUSE :

* Having Clause is basically used only with the GROUP BY function in a query.

* WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Difference between clustered and a non-clustered index :

* Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index.

* In Non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

Different index configurations a table :

A table can have one of the following index configurations:
1. No indexes
2. A clustered index
3. A clustered index and many nonclustered indexes
4. A nonclustered indexMany nonclustered indexes

Difference between primary key and Unique key :

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, but unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

Basic functions for master, msdb, model, tempdb databases :

* The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.

*  The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

*  The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

*  The model is essentially a template database used in the creation of any new user database created in the instance.

Join :

Join actually puts data from two or more tables into a single result set

Types of Joins :

There are three types of joins:
1. Inner Join
2. Outer Join
3. Cross Join

Command to rename a db :

sp_renamedb ‘oldname’ , ‘newname’

Stored procedure to add a linked server : 

sp_addlinkedserver, sp_addlinkedsrvlogin

Database :

A database is similar to a data file in that it is a storage place for data. Like a data file, a database does not present information directly to a user; the user runs an application that accesses data from the database and presents it to the user in an understandable format.

RDBMS :

A relational database management system (RDBMS) is a program that lets you create, update, and administer a relational database. The leading RDBMS products are Oracle, IBM's DB2 and Microsoft's SQL Server.

Normalization :

Well a relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.

Stored Procedure :

Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

Example of Stored Procedure :

sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.

Trigger :

Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.

View :

If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.

Index :

When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

Types of Indexes :

There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.

Cursors :

Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values

Use of DBCC commands :

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

Use of SQL Profiler :

SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..

Linked Server :

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.

ALTER FUNCTION [dbo].[udsf_WorkingHrsinMin]
(
-- Add the parameters for the function here
@StartDate Datetime, @EndDate Datetime, @Format varchar(10)
)
RETURNS VARCHAR(30)
AS
BEGIN

-- Declare the return variable here
DECLARE @Age VARCHAR(30)
DECLARE @FromDate as Datetime
DECLARE @TodayEndTime as Datetime
DECLARE @WorkingMin as INT
SET @FromDate = @StartDate

IF(Convert(Varchar(10),@StartDate,101) = Convert(Varchar(10),@EndDate,101))
BEGIN

IF(DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = 0
ELSE
SET @WorkingMin = Datediff("mi",@StartDate, @EndDate)
END

ELSE
BEGIN

WHILE(@StartDate < @EndDate)
BEGIN

IF (Convert(varchar(10),@StartDate,101) = Convert(Varchar(10),@FromDate,101))
BEGIN

IF (DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = 0
ELSE
SET @WorkingMin = Datediff("mi", @StartDate, Convert(Datetime, Convert(varchar(10),@FromDate,101) + ' 11:59 PM')) + 1
END

ELSE IF (Convert(varchar(10),@StartDate,101) = Convert(Varchar(10),@EndDate,101))
BEGIN
IF (DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = @WorkingMin + 0
ELSE
SET @WorkingMin = @WorkingMin + Datediff("mi", @StartDate, @EndDate)
END

ELSE
BEGIN
SET @TodayEndTime = Convert(Datetime, Convert(varchar(10),@StartDate,101) + ' 11:59 PM')
IF (DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @WorkingMin = @WorkingMin + 0
ELSE
SET @WorkingMin = @WorkingMin + Datediff("mi", @StartDate, @TodayEndTime) + 1
END
SET @StartDate = Dateadd("d",1, convert(varchar(10),@StartDate,101))

END
END

IF ((@WorkingMin/1440) > 0)
BEGIN
IF (@Format = 'DDHHMM')
SELECT @Age = Convert(Varchar,(@WorkingMin/1440)) + ' D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H : ' + CONVERT(Varchar, ((@WorkingMin%1440)%60)) + ' M'
ELSE IF (@Format = 'DDHH')
SELECT @Age = Convert(Varchar,(@WorkingMin/1440)) + ' D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H'
ELSE IF (@Format = 'DD')
SELECT @Age = Convert(Varchar,(@WorkingMin/1440)) + ' D'
ELSE
SELECT @Age = @WorkingMin
END

ELSE
BEGIN
IF (@Format = 'DDHHMM')
SELECT @Age = '0 D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H : ' + CONVERT(Varchar, ((@WorkingMin%1440)%60)) + ' M'
ELSE IF (@Format = 'DDHH')
SELECT @Age = '0 D : ' + CONVERT(Varchar, ((@WorkingMin%1440)/60)) + ' H'
ELSE IF (@Format = 'DD')
SELECT @Age = '0 D'
ELSE
SELECT @Age = @WorkingMin
END

-- Return the result of the function
RETURN @Age

END
GO

CREATE FUNCTION [dbo].[udsf_GetWeekEndCount]
(
-- Add the parameters for the function here
@StartDate Datetime, @EndDate Datetime
)
RETURNS INT
AS
BEGIN

DECLARE @HolidayCnt as Int
SET @HolidayCnt = 0

WHILE(@StartDate <= @EndDate)
BEGIN

IF(DateName(dw, @StartDate) = 'Saturday' OR DateName(dw, @StartDate) = 'Sunday')
SET @HolidayCnt = @HolidayCnt + 1
SET @StartDate = Dateadd("d", 1, @StartDate)

END

RETURN @HolidayCnt END
GO

It is called like "Select dbo.udsf_GetWeekEndCount('7/1/2009', '7/11/2009')
The result is 3

Declare @Value as Varchar(max)
SELECT @Value = COALESCE(@Value + ',', '') + Convert(varchar,ID) FROM Temp1 ORDER BY ID
Select @Value

DECLARE @val AS VARCHAR(MAX)
DECLARE @Delimiter AS VARCHAR(5)

SET @Val = '1,3,4,5,6,134,234,234,213,34'
--SET @Val = 'a, b, c, d, e'
SET @Delimiter = ','
SET @Val = @Val + @Delimiter

CREATE TABLE #Temp (val VARCHAR(50))

WHILE (@Val != '')
BEGIN

INSERT INTO #Temp
SELECT LTRIM(SUBSTRING(@val, 1,charindex(@Delimiter, @val)-1))
SET @Val = (CASE WHEN (LEFT(@val, 1) != @Delimiter) THEN SUBSTRING(@val, charindex(@Delimiter, @val) + 1, LEN(@Val)) ELSE '' END)

END

SELECT * FROM #temp
DROP TABLE #Temp

CREATE FUNCTION [dbo].[udtf_DateParameters]
(
@Condition VARCHAR(50)
)
RETURNS @temp TABLE (Startdate DATETIME, Enddate DATETIME)
AS
BEGIN

DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME

IF (@Condition = 'Today')
BEGIN
SET @FromDate = (SELECT CONVERT(DATETIME, CONVERT(varchar(10), Getdate(), 101) + ' 00:00:00 AM'))
SET @ToDate = (SELECT CONVERT(DATETIME, CONVERT(varchar(10), Getdate(), 101) + ' 11:59:59 PM'))
END

ELSE IF (@Condition = 'Yesterday')
BEGIN
SET @FromDate = (SELECT CONVERT(varchar(10), DATEADD(d, -1, Getdate()), 101) + ' 00:00:00 AM')
SET @ToDate = (SELECT CONVERT(varchar(10), DATEADD(d, -1, Getdate()), 101) + ' 11:59:59 PM')
END

ELSE IF (@Condition = 'Last 7 days')
BEGIN
SET @FromDate = (SELECT CONVERT(varchar(10), DATEADD(d, -7, Getdate()), 101) + ' 00:00:00 AM')
SET @ToDate = (SELECT CONVERT(DATETIME, CONVERT(varchar(10), Getdate(), 101) + ' 11:59:59 PM'))
END

ELSE IF (@Condition = 'Last 30 days')
BEGIN
SET @FromDate = (SELECT CONVERT(varchar(10), DATEADD(d, -30, Getdate()), 101) + ' 00:00:00 AM')
SET @ToDate = (SELECT CONVERT(DATETIME, CONVERT(varchar(10), Getdate(), 101) + ' 11:59:59 PM'))
END

ELSE IF (@Condition = 'This week')
BEGIN
SET @FromDate = (SELECT CONVERT(varchar(10), Getdate()-DATEPART(dw,Getdate())+1 , 101) + ' 00:00:00 AM')
SET @ToDate = (SELECT CONVERT(varchar(10), Getdate(), 101) + ' 11:59:59 PM')
END

ELSE IF (@Condition = 'Last Week')
BEGIN
SET @FromDate = (SELECT CONVERT(varchar(10), Getdate()-DATEPART(dw,Getdate())-6 , 101) + ' 00:00:00 AM')
SET @ToDate = (SELECT CONVERT(varchar(10), Getdate()-DATEPART(dw,Getdate()), 101) + ' 11:59:59 PM')
END

ELSE IF (@Condition = 'Last Month')
BEGIN
SET @FromDate = (SELECT CONVERT(varchar(10), DATEADD(mm, -1, DATEADD(dd, -day(Getdate()) + 1, Getdate())), 101) + ' 00:00:00 AM')
SET @ToDate = (SELECT CONVERT(varchar(10), DATEADD(dd, -day(Getdate()), Getdate()) ,101) + ' 11:59:59 PM')
END

INSERT INTO @temp VALUES (@Fromdate, @todate)

RETURN

END

It can be called like "
select Startdate, EndDate from dbo.udtf_DateParameters('Last Month')"

-- Query to delete all the Stored Procedure, Function (Scalar and Table Value Function) and User Defined Tables within the database

DECLARE @Cnt as INTEGER
DECLARE @SPName as varchar(100)
DECLARE @FuncName as varchar(100)
DECLARE @TableName as varchar(100)
DECLARE @Qry as varchar(200)

-- Stored Procedure

select @Cnt = Count(id) from sysobjects where type = 'p'

WHILE (@Cnt > 0)
BEGIN
SELECT @SPName = name From
(SELECT row_number() over (order by name) as rownumber, name from Sysobjects where type = 'p') t WHERE rownumber = @Cnt
exec ('DROP Procedure ' + @SPName)
SET @Cnt = @Cnt - 1
END

--Table Valued Function

select @Cnt = Count(id) from sysobjects where type = 'TF'
WHILE (@Cnt > 0)
BEGIN
SELECT @FuncName = name From
(SELECT row_number() over (order by name) as rownumber, name from Sysobjects where type = 'TF') t
WHERE rownumber = @Cnt
exec ('DROP Function ' + @FuncName)
SET @Cnt = @Cnt - 1
END

--Scalar valued Function

select @Cnt = Count(id) from sysobjects where type = 'FN'
WHILE (@Cnt > 0)
BEGIN
SELECT @FuncName = name From
(SELECT row_number() over (order by name) as rownumber, name from Sysobjects where type = 'FN') t
WHERE rownumber = @Cnt
exec ('DROP Function ' + @FuncName)
SET @Cnt = @Cnt - 1
END

-- Tables

SELECT @Cnt = Count(id) from sysobjects where type = 'u'
WHILE (@Cnt > 0)
BEGIN
SELECT @tablename = name FROM
(SELECT row_number() over (order by name) as rownumber, name from Sysobjects where type = 'u') t
WHERE rownumber = @Cnt
--To Delete all the Constraints for the Table
IF(SELECT Count(Constraint_Name) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_Name = @tablename) > 0
BEGIN
SELECT @Qry = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name = @tablename
EXEC sp_executesql @Qry
END
--To Delete all the Records in the Table
EXEC ('Truncate Table ' + @tablename)
--To Delete the Table
EXEC ('Drop Table ' + @tablename)
SET @Cnt = @Cnt - 1
END