Differences between Clustered Index and Non Clustered Indexes // Clustered Vs Non Clustered Indexes

  

Character	Clustered Index	Non Clustered Index
1.       Max num to a Table	Only 1 max per table	Max  249 for 2005 Version Max  999 for 2008 Version
2.       Sort operation	table will be physically – sorted (on the actual disk) – by the values inside the Clustered-Indexed column	Non-clustered index has no effect on which the order of the rows will be stored.
3.       Data Storage	it actually stores row level data in the leaf nodes of the index itself	Non clustered indexes store both a value and a pointer to the actual row that holds that value
4.       Usage	Using a clustered index is an advantage when groups of data that can be clustered are frequently accessed by some queries. This speeds up retrieval because the data lives close to each other on disk. Also, if data is accessed in the same order as the clustered index, the retrieval will be much faster because the physical data stored on disk is sorted in the same order as the index.	Not Possible here.
5.       DisAdvantage	For DML operations the data sorting for each record will effects performance. If any change is made to a value of an indexed column, the subsequent possibility of re-sorting rows to maintain order is a definite performance hit.	No You can do DML operations as normal speed, because here the data is stored as last record, no sorting is happening here.
6.       Advantage	Much faster while retrieving the data from a table	Fast compare normal data retrieving process(compare to Clustered , Non Clustered index is slow)
7.       UNIQUE Ness	Clustered Index doesn’t enforce uniqueness on its filed. But if you include UNIQUE key word while creation time, then Clustered Index becomes UNIQUE CLUSTERED INDEX  and must contain Unique values only. Example: CREATE CLUSTERED INDEX bob ON T_Name( ID ) is not the same as CREATE UNIQUE CLUSTERED INDEX bob ON T_Name( ID )	Here we can create but that’s not a good idea to create UNIQUE NonClustered Index on a field of a table(bcz query takes too much time on Unique non clustered index scan)
8.       Object Space	Clustered Index won’t take extra space for creating Clustered Index on a field of a table(table level it self we can find Clustered Index)	For Non Clustered Indexes a separate Object will be created outside of the table with "NonClustered-field, Reference “ named fileds. So that query has to look 2 table objects while working with Non-Clustered Index, that is what it takes some extra time to retrieve data compare to Clustered Index
9.

Note:

SQL Server creates a unique clustered index when you define thePRIMARY KEY constraint on a table.

Pictures of Clustered & Non Clustered Indexes:

the above one is Non Clustered Index Pic

The above one is Clustered Index Pic

Clustered indexes VS non-clustered indexes

	CLUSTERED	NON-CLUSTERED
PROS	·         Fast to return large range of data ·         Fast for presorted results	·         Wide keys do not reflect on other indexes ·         Frequently updated key columns do not reflect on other indexes ·         Can be assigned on different FileGroup ·         Many non-clustered indexes per table ·         Smaller size than clustered indexes due to column subsets
CONS	·         Frequently updated key columns reflect on non-clustered indexes ·         Wide keys increase the size of the non-clustered indexes ·         Only one clustered index per table	·         Generally slower than clustered indexes due to bookmark lookup (except for covering indexes). ·         Not recommended for returning large data sets (except for covering indexes).

Extracting insert, update, delete rowcounts from T-SQL MERGE

source:

http://sqlblog.com/blogs/jamie_thomson/archive/2009/08/30/extracting-insert-update-delete-rowcounts-from-t-sql-merge.aspx

How to get Non matching records from 2 tables in Sql Server

We are having 2 tables like below

select * from table_2

select * from Table_1

Now write the below queries:

1. (select table_2.id from table_2
except
select Table_1.id from Table_1)
union
(select Table_1.id from Table_1
except
select table_2.id from table_2)

OR

2. (select table_2.id from table_2
left join Table_1
on table_2.ID=Table_1.id)
union
(select Table_1.id from Table_1
left join table_2
on table_2.ID=Table_1.id)
except
(select Table_1.id from Table_1
 join table_2
on table_2.ID=Table_1.id)


Result is:

Difference between SQL Server 2008 R2 and SQL Server 2012 // SQL Server 2012 Vs 2008 R2

What are the Difference between SQL server 2008 R2 (10.5) and SQL Server 2012 (11)?

(OR)

Difference between SQL Server 2008 R2 and SQL Server 2012

Character	Sql Server 2012	Sql Server 2008 R2
1.       Code Name	SQL Server 2008 R2 is codenamed as Kilimanjaro	SQL Server 2012 is codenamed as Denali
2.       New Functions	CONCAT(), FORMAT() and TRY_CONVERT() ,functions are not available in SQL Server 2012	CONCAT(), FORMAT() and TRY_CONVERT() functions are not available in SQL Server 2008 R2
3.       Concurrent Connection	SQL server 2012 has unlimited concurrent connections.	SQL server 2008 R2 has 32767 concurrent connections.
4.       Working with CONVERT(  )       and FORMAT(  )  functions	SQL Server 2012 come up with new function FORMAT() same as .format() C# function for change the date/currency format etc. This example displays the date in Taiwan format. Taiwan uses traditional Chinese characters. DECLARE @date DATETIME = '12/21/2011'; SELECT FORMAT ( @date, 'MMMM dddd d', 'zh-TW' ) AS FormattedDate; In US format DECLARE @date DATETIME = convert(datetime,'2011/01/01 2:00:00'); SELECT FORMAT ( @date, 'yyyy/MM/dd hh:mm ss tt','en-US' ) AS FormattedDate; Result FormattedDate 2011/01/01 02:00:00 AM	CONVERT() function for convert date format. for that we need to memorizing cryptic style codes like 101 and 103 for converting datetime values to localized presentation formats. CONVERT(DATETIME, '7/24/2010 12:00:00 AM', 101)
5.       Currency / Numbers	SQL Server 2012 have the solution for cast a money field, this is done by FORMAT() function Example Let's see some of the numeric formats that we can display using the FORMAT() function. Personally, I format numbers for currencies and number of decimal characters or for percentage. Here are some examples. To display the number with currency using locale, use the following example. Example 1: DECLARE @money money = '125000'; SELECT FORMAT ( @money, 'C') AS MyMoney; Result: $125,000.00	There is no direct way to cast a money/currency field in SQL Server 2008
6.       Server Down	In SQL Server 2012, server down time is reduced by 50% , hence OS patching is not rebooting n times.	In SQL Server 2008 R2 , rebooting is requisite for OS patching , hence server down time is high.
7.       New  Objects	SQL Server 2012 has “SEQUENCE” as new Object To create an integer sequence number that increments by 1 from -2,147,483,648 to 2,147,483,647, use the following statement.	SQL Server 2008 R2 has  no “SEQUENCE” Object
8.       IIF( ) Function	SQL Server 2012 has “IIF” as new Function	SQL Server 2008 R2 has no “IIF” Function

Typical Use

---

To create an integer sequence number that increments by 1 from -2,147,483,648 to 2,147,483,647, use the following statement.

CREATE SEQUENCE Schema.SequenceName

    AS int

    INCREMENT BY 1 ;

To create an integer sequence number similar to an identity column that increments by 1 from 1 to 2,147,483,647, use the following statement.

CREATE SEQUENCE Schema.SequenceName

    AS int

    START WITH 1

    INCREMENT BY 1 ;

Syntax:

CREATE SEQUENCE [schema_name . ] sequence_name

 [ AS [built_in_integer_type | user-defined_integer_type ] ]

 [ START WITH  ]

 [ INCREMENT BY  ]

 [ { MINVALUE [  ] } | { NO MINVALUE } ]

 [ { MAXVALUE [  ] } | { NO MAXVALUE } ]

 [ CYCLE | { NO CYCLE } ]

 [ { CACHE [  ] } | { NO CACHE } ]

 [ ; ]

Where:
Start with:             the initial value to start with sequence.
Increment by:       the step by which the values will get incremented or decremented.
Minvalue:              the minimum value of the sequence.
Maxvalue:             the maximum value of the sequence.
Cycle / No Cycle:  to recycle the sequence once it reaches to the maximum or minimum (if increment by is a negative number).
Cache / No Cache:  to pre-allocate the number of sequences specified by the given value.

Example: 

USE TempDB
GO
-- Create sequence
CREATE SEQUENCE dbo.SequenceID AS BIGINT
START WITH 3
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE
NO CACHE;
GO
-- Following will return 3
SELECT next value FOR dbo.SequenceID;
-- Following will return 4
SELECT next value FOR dbo.SequenceID;
-- Following will return 5
SELECT next value FOR dbo.SequenceID;
-- Following will return which number
SELECT next value FOR dbo.SequenceID;
-- Clean up
DROP SEQUENCE dbo.SequenceID;
GO

SQL Server 2008 R2 (SQL Server 10.5) :

1.SQL Server 2008 R2 is codenamed as Kilimanjaro
***2.In SQL Server 2008 R2 , rebooting is requisite for OS patching , hence server down time is high
3.SQL Server 2008 R2 does not have this feature of availability groups, hence fast recovery is not possible.
4.The SQL Server 2012 uses 48 bit precision for spatial calculations
5.CONCAT(), FORMAT() and TRY_CONVERT() functions are not available in SQL Server 2008
6.SQL Server 2008 R2 is slow compared to SQL Server 2012.
7.However buffer rate is less because there is no data redundancy in SQL Server 2008 R2
8.Data visualization is not supported in SQL Server 2008 R2
9.Spatial features are not supported more in SQL Server 2008 R2. Instead a traditional way for geographical elements have been set in SQL Server 2008 R2.
10.The Maximum number concurrent connections to SQL Server 2008 is 32767.
11. SQL Server have old CONVERT() function for convert date format. for that we need to memorizing cryptic style codes like 101 and 103 for converting datetime values to localized presentation formats
CONVERT(DATETIME, '7/24/2010 12:00:00 AM', 101)
12. There is no direct way to cast a money/currency field in SQL Server 2008

SQL Server 2012 (SQL Server 11) :

1.SQL Server 2012 is codenamed as Denali
2.In SQL Server 2012, server down time is reduced by 50% , hence OS patching is not rebooting n times.
3.In SQL Server 2012, high availability and disaster recovery factor has been introduced which duplicates the data and rapidly recovers the loss.
4.The SQL Server 2012 uses 48 bit precision for spatial calculations
5.CONCAT(), FORMAT() and TRY_CONVERT() functions are newly included in SQL Server 2012
6.In SQL Server 2012, the performance is 10 times faster than the predecessor.
7.Buffer rate is high in SQL Server 2012 because of data compression.
8.Data visualization tool is available in SQL Server 2012.This allows snapshots of data.
9.Support for persistent computed columns and extra geographical approach is possible with spatial features in SQL Server 2012.
10.SQL server 2012 has unlimited concurrent connections.

11. SQL Server 2012 come up with new function FORMAT() same as .format() C# function for change the date/currency format etc.
This example displays the date in Taiwan format. Taiwan uses traditional Chinese characters.
DECLARE @date DATETIME = '12/21/2011';
SELECT FORMAT ( @date, 'MMMM dddd d', 'zh-TW' ) AS FormattedDate;

In US format
DECLARE @date DATETIME = convert(datetime,'2011/01/01 2:00:00');
SELECT FORMAT ( @date, 'yyyy/MM/dd hh:mms tt','en-US' ) AS FormattedDate;
Result
FormattedDate
2011/01/01 02:00:00 AM

12. SQL Server 2012 have the solution for cast a money field, this is done by FORMAT() function
Example
Let's see some of the numeric formats that we can display using the FORMAT() function. Personally, I format numbers for currencies and number of decimal characters or for percentage. Here are some examples.

To display the number with currency using locale, use the following example.
Example 1:

DECLARE @money money = '125000';
SELECT FORMAT ( @money, 'C') AS MyMoney;
Result:
$125,000.00

Here, I am getting the currency symbol ‘$’ because my current locale language setting is en-us. I could also display the currency ‘$’ explicitly by using the culture parameter as shown below.
DECLARE @money money = '125000';
SELECT FORMAT ( @money, 'C', 'en-US' ) AS MyMoney;
for more example http://www.databasejournal.com/features/...-2012.html

References http://onlydifferencefaqs.blogspot.in/20...8-and.html

SQL Server COMPLEX QUERIES ( Not Yet Written)

1. How can you change a column values from "male" to "female" with Sql Query?

T-SQL Important Syntaxes (Not Yet Written)

SQL SERVER –Explanation of Transaction Lock, Lock Type, Avoid Locks

In SQL Server 2005 (SSMS, object Explorer)
Expand-server-management-double click Activity Monitor.
on left side you have three options to choose from, select those options and you can see all the locks related information.

run this stored procedure in the database.

1. sp_lock

to know the running process in the sql server, run this query,

2. select * from sysprocesses ( in sql server 2000)
3. select * from sys.sysprocesses ( in sql server 2005)

4. sp_who
5. sp_who2 will also give you some good information.

To work around the locks, you can run profiler to check which query is is creating a lock and if that is necessary.

Types of locks on object level, ( general idea)

Database : Database.
Extent : Contiguous group of eight data pages or index pages.
Key: Row lock within an index.
Page: 8-kilobyte (KB) data page or index page.
RID :Row ID. Used to lock a single row within a table.
Table: Entire table, including all data and indexes.

Types of locks;
Shared (S) – more than one Query can access the object.
Exclusive lock (X) – only one Query can access the object.
Update lock (U)
Intent share (IS)
Intent Exclusive (IX)

Just to give you a brief idea about locks, We have something called as transaction levels in sql server databases.

TRANSACTION ISOLATION LEVEL
level 0. READ COMMITTED
level 1. READ UNCOMMITTED
level 2. REPEATABLE READ
level 3. SERIALIZABLE

level 0 is the lowest level isloation level, if your database is set in this isolation level, no query will lock any resources,Under this level, there will be no locks on the database, not even shared locks.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
This data will also read uncommitted data. Data which you have not comitted, you can still read that data.

level1 is the default isolation level of the database.
Under this category you will not be able to read uncomitted data, this is also called as dirty data. Under this we will have shared locks.

As the level increases the locks also increases. The highest is the serializable.

To make you understand in detail, lets see an example of what is committed data and what is uncomitted data.

use pubs
create table example1 ( eid int, ename varchar(10))

begin tran T1
insert into example1 values ( 1, ‘example’)
go

select * from example1 — this is uncomitted data.

The above is uncomitted transaction, because you started the transaction with a begin, you have to commit the transaction, untill then the transaction will not be uncommitted.

to commit the same transaction

commit tran T1

select * from example1 — this is committed data.

To check what is the current isolation level of your database, run this command,

Dbcc useroptions — check for isolation level.

If you dont want your query to put locks on objects you might want to use something like this,

select * from example1_1 with (nolock)

This will not keep any lock, not even a shared lock on the table.

This is indepth concept try looking BOL.

Hope this helps,