Differences between CAST & CONVERT // CAST Vs CONVERT

Character	CAST	CONVERT
1.       Type	CAST is ANSI.	CONVERT is SQL Server specific
2.       Date values	Cast don’t has different types date formats.	You can apply Different styles for date values. Like 108, 98, 101… etc styles.
3.       Syntax	Syntax for CAST: CAST ( expression AS data_type [ ( length ) ] )	Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
4.       For Decimal Results	Use CAST
5.       For Truncate purpose	CAST to truncate
6.       For Quick Search	CAST for quicker search

Similarities:

Variants	CAST and CONVERT variants -- always
Concatenate	CAST and CONVERT to concatenate

• Cast

1.      Cast is  ANSII Standard

2.      Cast cannot be used for Formatting Purposes.

3.      Cast cannot convert a datetime to specific format

• Convert

1.      Convert is Specific to SQL SERVER

2.      Convert can be used for Formatting Purposes.For example Select convert (varchar, datetime, 101)

3.      Convert can be used to convert a datetime to specific format

Useful Link:

http://www.techrepublic.com/blog/10-things/10-reasons-to-explicitly-convert-sql-server-data-types/324/

Differences between Star Schema and Snowflake Schema // Star Schema Vs Snowflake Schema

How to delete Non Date values from a field of a table // query to get non date values from a field in sql server

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).

Differences between TempTable & CTE // TempTable Vs CTE

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

Differences between TempTable & TableVariable // TempTable Vs TableVariable

Character	TempTable	TempVariable
1.       Definition	#TempTable	@TempVariable
2.       Where we Use	For small to medium volumes of data and simple usage scenarios you should use table variables Stored Procedures, Triggers, Batches.	UDFs, Stored Procedures, Triggers, Batches
3.       Creation	CREATE TABLE statement. SELECT INTO statement.	DECLARE statement only.
4.       Table Name	Maximum 116 characters.	Maximum 128 characters
5.       Index Creation	Indexes can be added after the table has been created. For large amounts of data for which accessing by index will be faster then temporary tables are a good option	Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.
6.       Constraints	PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.
7.       Data insertion	INSERT statement, including INSERT/EXEC. SELECT INTO statement.	INSERT statement (SQL 2000: cannot use INSERT/EXEC).
8.       Post-creation DDL (indexes, columns)	Statements are allowed.	Statements are not allowed.
9.       Truncate table	Allowed.	Not Allowed.
10.   Destruction	Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)	Automatically at the end of the batch.
11.   Rollbacks	Affected (Data is rolled back).	Not affected (Data not rolled back).
12.   Statistics	Optimizer can create statistics on columns. Uses actual row count for generation execution plan.	Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.
13.   Transactions	Last for the length of the transaction. Uses more than table variables.	Last only for length of update against the table variable. Uses less than temporary tables. Table variables don't participate in transactions, logging or locking.
14.   SELECT INTO	You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing data types over time, since you don't need to define your temp table structure upfront
15.   Storage	TempDB	TempDB
16.   Bulk Insert	Can’t possible. You cannot use table variable when using BULK INSERT	Possible
17.   Scope	1.       #TempTable: is Local temp tables Scope: current session of current user means to the current query window. And in Nested SPs These are only available to the SQL Server session or connection (means single user) that created the tables These are automatically deleted when the session that created the tables has been closed. 2.       ##TempTable : is Global TempTable Scope: Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.	This acts like a variable and exists for a particular batch of query execution. TableVariable gets dropped once it comes out of batch
18.   Recompilation in SP	procedure with a temporary table cannot be pre-compiled	execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.
19.   Usage in INSERT/EXEC statements		table variables exist only in the same scope as variables. Opposite to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

Similarities with TempTable & TempVariable:

• Instantiated in tempdb
• Clustered indexes can be created on table variables and temporary tables
• Both are logged in the transaction log
• Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable:  SELECT, INSERT, UPDATE, and DELETE.

Source URL:

http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table