1. Intellisence Enhancements (IDE, Lookup will come automatically)
2. Declaration and Initialization in Same Line (Instead of DECLARE and SET in seperate Statement).
3. Compound Operators : (+=, -+, *=, /=....)
4. Multiple Value Insert in single Insert Statement
5. Grouping Sets (Grouping in Different Select stament is merged with one).
6. User-Defined Table Type and Table-Value Parameter (TVP)
7. New Date and Time Data Types
8. New Datatype DateTime2, DateTimeOffSet
9. HirerachyID

Ref : http://www.sqlservercentral.com/articles/SQL+Server+2008/65539/

Compound Operators



Row Constructor (or Table-Valued Constructor) as Derived Table

SELECT *
FROM
(VALUES ('USD', 'U.S. Dollar'),
('EUR', 'Euro'),
('CAD', 'Canadian Dollar'),
('JPY', 'Japanese Yen')) AS [Currency] ( [CurrencyCode], [CurrencyName] )

Multiple Value Inserts Within a Single INSERT Statement

INSERT INTO [dbo].[USState]
VALUES ('AK', 'Alaska'),
('AL', 'Alabama'),
('AR', 'Arkansas'),
('AZ', 'Arizona'),
('CA', 'California')

Table-Valued Parameters

CREATE TYPE [ContactTemplate] AS TABLE (
[Email] VARCHAR(100),
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
GO

CREATE PROCEDURE [dbo].[usp_ProcessContact]
@Contact ContactTemplate READONLY
AS

-- Update First Name and Last Name for Existing Emails
UPDATE A
SET [FirstName] = B.[FirstName], [LastName] = B.[LastName]
FROM [dbo].[Contact] A INNER JOIN @Contact B ON A.[Email] = B.[Email]

-- Add New Email Addresses
INSERT INTO [dbo].[Contact] ( [Email], [FirstName], [LastName] )
SELECT [Email], [FirstName], [LastName]
FROM @Contact A
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Contact] B
WHERE A.[Email] = B.[Email])
GO

MERGE Statement

CREATE PROCEDURE [dbo].[usp_MergeEmployee]
@EmployeeNumber VARCHAR(10),
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Position VARCHAR(50)
AS

MERGE [dbo].[Employee] AS [Target]

USING (SELECT @EmployeeNumber, @FirstName, @LastName, @Position)
AS [Source] ( [EmployeeNumber], [FirstName], [LastName], [Position] )

ON [Target].[EmployeeNumber] = [Source].[EmployeeNumber]

WHEN MATCHED THEN

UPDATE SET [FirstName] = [Source][FirstName],
[LastName] = [Source].[LastName],
[Position] = [Source].[Position]

WHEN NOT MATCHED THEN

INSERT ( [EmployeeNumber], [FirstName], [LastName], [Position] )
VALUES ( [Source].[EmployeeNumber], [Source].[FirstName],
[Source].[LastName], [Source].[Position] );

GO

Ref : http://www.sql-server-helper.com/sql-server-2008/index.aspx

TRUNCATE TABLE statement was used to remove all data, the IDENTITY column resets the numbering to start with the original value. DELETE statement was used to remove all data, the IDENTITY values are still incremented according to the last value used.

A TRUNCATE TABLE statement tends to use fewer locks than a DELETE statement. When a TRUNCATE TABLE statement is used, SQL Server applies table and page locks but not row locks, as a DELETE statement does.

A TRUNCATE TABLE statement uses less transaction log space than a DELETE statement. When a TRUNCATE TABLE statement is used, SQL Server de-allocates the data pages and records only the de-allocations in the transaction log. When a DELETE statement is used, SQL Server makes an entry into the transaction log for each deleted row.

A TRUNCATE TABLE statement leaves no pages in a table, whereas a DELETE statement can leave empty pages.

While a TRUNCATE TABLE statement is more efficient than a DELETE statement, there are restrictions that govern the use of TRUNCATE TABLE. For example, you should not use a TRUNCATE TABLE statement against a table under the following conditions:

An indexed view specifies the table in its definition.

Transaction or merge replication is used to publish the table.

The table is referenced by a foreign key constraint (unless it is a self-referencing foreign key).

The table's IDENTITY values must be preserved and consistently incremented.

Only specific rows are to be deleted from the table, and not the entire dataset.

Regardless of these differences, both the TRUNCATE TABLE and DELETE statements remove only data and do not impact the table structure. Indexes, constraints, and columns are left untouched.

Relational Databases :

A relational database at its simplest is a set of tables used for storing data. Each table has a unique name and may relate to one or more other tables in the database through common values.

Tables :

A table in a database is a collection of rows and columns. Tables are also known as entities or relations.

Rows :

A row contains data pertaining to a single item or record in a table. Rows are also known as records or tuples.

Columns :

A column contains data representing a specific characteristic of the records in the table. Columns are also known as fields or attributes.

Relationships :

A relationship is a link between two tables (i.e, relations). Relationships make it possible to find data in one table that pertains to a specific record in another table.

Datatypes :

Each of a table's columns has a defined datatype that specifies the type of data that can exist in that column. For example, the FirstName column might be defined as varchar(20), indicating that it can contain a string of up to 20 characters. Unfortunately, datatypes vary widely between databases.

Primary Keys :

Most tables have a column or group of columns that can be used to identify records. For example, an Employees table might have a column called EmployeeID that is unique for every row. This makes it easy to keep track of a record over time and to associate a record with records in other tables.

Foreign Keys :

Foreign key columns are columns that link to primary key columns in other tables, thereby creating a relationship. For example, the Customers table might have a foreign key column called SalesRep that links to EmployeeID, the primary key in the Employees table.

Relational Database Management System :

A Relational Database Management System (RDBMS), commonly (but incorrectly) called a database, is software for creating, manipulating, and administering a database. For simplicity, we will often refer to RDBMSs as databases.

Popular Databases :

ORACLE
SQL SERVER
DB2

Popular Open Source Databases :

MySQL
PostgreSQL

SQL Statements :

Database Manipulation Language (DML)

DML statements are used to work with data in an existing database. The most common DML statements are:

SELECT
INSERT
UPDATE
DELETE

Database Definition Language (DDL)

DDL statements are used to structure objects in a database. The most common DDL statements are:
CREATE
ALTER
DROP

Database Control Language (DCL)

DCL statements are used for database administration. The most common DCL statements are:

GRANT
DENY (SQL Server Only)
REVOKE

SQL Server uses the following precedence order for data types:

1.user-defined data types (highest)
2.sql_varian t
3.xml
4.datetimeoffset
5.datetime2
6.datetime
7.smalldatetime
8.date
9.time
10.float
11.real
12.decimal
13.money
14.smallmoney
15.bigint
16.int
17.smallint
18.tinyint
19.bit
20.ntext
21.text
22.image
23.timestamp
24.uniqueidentifier
25.nvarchar (including nvarchar(max))
26.nchar
27.varchar (including varchar(max))
28.char
29.varbinary (including varbinary(max))
30.binary (lowest)

• The maximum number of columns per base table is 1024.


• The maximum number of columns per index is 16.


• The maximum number of columns per SELECT statement is 4096.


• The maximum number of foreign key table references per table is 253.


• The maximum number of parameters per stored procedure is 1024.


• The maximum number of characters allowed for a table name is 128.


• The maximum number of characters allowed for a column name 128.


• The maximum number of columns in the view is 250.

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.