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

Get Row number from query

use Row_Number() function to get row numbers in rows

ex.

select Row_Number() over (order by fieldname) as rowid, * from [Tablename]

Export EXCEL File To SQL Server 2000 /2005 /2008

CREATE procedure [dbo].[InsertEXCELToSQLSERVER]
as

declare @Field1 nvarchar(50)
declare @Field2 nvarchar(50)

declare @CursorName cursor

set @CursorName = cursor
for
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\\path\datafile.xls','select @Field1,@Field2 from [Sheet1$]')
open @CursorName
fetch next from @CursorName into @Field1, @Field2

WHILE @@FETCH_STATUS = 0
BEGIN
select @Field1, @Field2 ;

INSERT INTO Tablename (Field1,Field2) VALUES (@Field1, @Field2 )

fetch next from @UserCursor into @Field1,@Field2

END

CLOSE @CursorName
DEALLOCATE @CursorName

OutPut : following will execute and insert data in to table
exec InsertEXCELToSQLSERVER

Functions In Sqlserver

Table valued Functions

create function [dbo].[GetInformation](@ParameterId int)
returns table as
return (
select count(*) as total from tablename where condition=@ParameterId)

to view results
select * from GetInformation(10) where 10 is parent table's primary key Id

Scalar valued Functions

ALTER FUNCTION [dbo].[GenerateReceiptNumber](@FirstName varchar(50),@Year varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @Result varchar(50)
Declare @FN varchar(50)
Declare @YR varchar(50)

set @FN = @FirstName
set @YR = @Year


SELECT @Result = upper(@FN + @YR)

RETURN @Result

END

to view results write

select dbo.GenerateReferenceNumber('MyName','2009')
OutOut -- > MyName2009

Use Of Pivot Table in Sqlserver 2005

Display Columns into Rows Using Pivot Table

here is the example that from Attendence table Want to get Yearly Attendence report By Student Wise

select StudentID,
StudentName,
isnull(January,0) January,
isnull(February,0) February,
isnull(March,0) March,
isnull(April,0) April,
isnull(May,0) May,
isnull(June,0) June,
isnull(July,0) July,
isnull(August,0) August,
isnull(September,0) September,
isnull(October,0) October,
isnull(November,0) November,
isnull(December,0) December
from
(
select StudentID, StudentName, DateName(m,AttendenceDate) [Month], Count(*) Total from AttendenceTable
Group by StudentID, DateName(m,AttendenceDate),StudentName
) as source
Pivot
(
sum(Total)
for [month] in (January,February,March,April,May,June,July,August,September, October, November, December)

) as result

Useful Sql Querys

Print Row number in Sqlserver 2005

SELECT (ROW_NUMBER() OVER (ORDER BY field_name) )as RowNumber,
field1, field2,fieldn FROM tablename

Get random record from database

SELECT TOP 1 field1, field2,fieldn FROM tablename ORDER BY NEWID()

Find Recenty executed Query in SQLSERVER 2005

SELECT

DMExQryStats.last_execution_time AS [Executed At], DMExSQLTxt.text AS [Query]
FROM
sys.dm_exec_query_stats AS DMExQryStats
CROSS APPLY
sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt
ORDER BY
DMExQryStats.last_execution_time DESC


Taking Multiple backups in SQL SERVER 2005

BACKUP DATABASE Northwind
TO DISK = 'D:\DataabseBkps1\Northwind.bak'
MIRROR
TO DISK = 'E:\DataabseBkps2\Northwind.bak'
WITH FORMAT;