Querying XML in Sql Server

declare @xml xml = ' Viral Bhatt Petlad Anand Jinal Shah Ahmedabad Ahmedabad Rajesh Davda Wadhwan City Surendrabagar ' SELECT ISNULL(b.value('Name[1]','NVARCHAR(50)'),NULL) AS Name, ISNULL(b.value('City[1]','NVARCHAR(50)'),NULL) AS City, ISNULL(b.value('District[1]','NVARCHAR(50)'),NULL) AS District FROM @xml.nodes('/xmlroot/xmlattribute') a(b)

Sending E-Mail through SQL Server stored procedures

This summary is not available. Please click here to view the post.

Inline variable assignment in sql server 2008

Instead of:

DECLARE @myVar int
SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Reset Identity Column in SQL Server

Run Following SQL Query in your Query Window and Execute it. This will reset the identity column’s value once gain to 0, so that the new record will start from 1.

DBCC CHECKIDENT(‘Users’, RESEED, 0)

Here ‘Users’ is table name.This will reset the Identity column’s value to 0. So, next record will start from 1.

Enjoy!!!

The transaction log for database 'mydatabase' is full

Issue : The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Resolution :

BACKUP LOG WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (, 1)
GO

Basic Understanding for Sql server

* Difference between Clustered Index and Non Clustered Index

- A Clustered Index consists of index as well as data pages. Clustered Index is not just an index but also contains the table data. A clustered index is organized as a B-tree where the non-leaf nodes are index pages and the leaf nodes are data pages.

- A Non-clustered index is organized as a B-tree but it consists of only index pages. The leaf nodes in a non-clustered index are not data pages, but contains pointer for individual rows in a data pages.

* Master Database contains login Information.
* MSDB Database contains Job Information.
* Model system database is default to FULL recovery model.


* Difference between Primary Key and Unique Key is as follows

- Primary key prevents the duplication of key values and does not allow NULL values. It allows each row in a table to be identified uniquely.
- Unique Key does not same what Primary Key does except it allows NULL record.

* Only One NULL values can be inserted for column that has Unique Key defined.

* Difference between Clustered Index and Non Clustered Index

- A Clustered Index consists of index as well as data pages. Clustered Index is not just an index but also contains the table data. A clustered index is organized as a B-tree where the non-leaf nodes are index pages and the leaf nodes are data pages.

- A Non-clustered index is organized as a B-tree but it consists of only index pages. The leaf nodes in a non-clustered index are not data pages, but contains pointer for individual rows in a data pages.

Stuff Keyword in Sqlserver

STUFF - The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Syntex - STUFF (character_expression , start , length ,character_expression )

Example -

SELECT STUFF('VABCDERBHATT', 2, 6, 'iral');
GO