1) Geometry Data Types
a. Point -  POINT ( 10 15 ) -  A point
b. Multipoint  – MULTIPOINT (10 10, 50 50) – A multipoint with two points
c. LineString  – LINESTRING (10 10,20 20,31 35) -  A line string with three points
d. Polygon – POLYGON (( 10 10, 10 20, 20 20, 20 15, 10 10))  – A polygon
e. GeomCollection  – GEOMETRYCOLLECTION ((POINT (10 15), LINESTRING (10 10,20 20)) A 
collection of a point and line

2) Geography  Data Types

3) Date Alone column - Range – 01-01-0001 to 12-31-9999

4) Time Alone Column - 00:00:00.0000000 through 23:59:59.9999999 (hours, minutes, seconds, and fractional seconds)

5) DATETIME2—A datetime type with larger fractional seconds and year range than the existing DATETIME type. It is .net Compatible – Range is 00:00:00 through 23:59:59.9999999 , 0001-01-01 through 9999-12-31

6) .Write Extension  - for update command (Can update small part of the column)
DECLARE @offset int
SET @offset = 0
WHILE @offset < 26
BEGIN
UPDATE testBIGtext
–the text I am writing is just starting at the letter A –> char(97)
–and increasing by adding the value of offset to 97 char(97) = a
–char (98) = b. It is also used as the offset in the varchar(max) column
–It is multiplied by the length of the data being written to fill a
–pattern of aaabbbccc…zzz only with a 1000 of each
SET value.write(replicate(char(97 + @offset),1000),@offset*1000, 1000)
WHERE testBIGTextId = 1
SET @offset = @offset + 1
END

7) Execute – Previously only execute the statement of same server but now can execute the statement of another server

8) SQL Server 2008 allows us to declare and initialize variables in a single statement, like:
DECLARE @a int = 1, @b int = 2

9) In SQL Server 2008, developers can now pass a table as a parameter to stored procedures and user-defined functions

10) SELECT TOP(@rowsToReturn)

11) The OUTPUT clause gives you the ability to access the changes from a DML statement without building triggers or any other method.
DECLARE @changes table (change varchar(2000))
UPDATE TOP (10) Person.Contact
SET firstname = Reverse(firstname)
OUTPUT ‘Was: ”’ + DELETED.firstname +
”’ Is: ”’ + INSERTED.firstname + ””
INTO @changes
SELECT *FROM @changes

12) MERGE -
MERGE <target_table>
USING <table_source>
ON <search_criteria>
<merge_clause>
The merge_clause consists of keyword combinations such as WHEN MATCHED, WHEN NOT MATCHED,and WHEN SOURCE NOT MATCHED. This merge_clause, in addition to the granularity available by being able to define search criteria, allows the MERGE functionality to be a valuable part of your database applications.

13) EXCEPT: Takes the first set of data and compares it with the second set. Only values that exist in the first set, but not in the second, are returned.
Example :- What if we want to see who worked only on projLittle but not projBig?
SELECT personId FROM projectPerson WHERE projectId = ‘projLittle’
EXCEPT
SELECT personId FROM projectPerson WHERE projectId = ‘projBig’

14) INTERSECT : say we want to see who worked only on both projects.
SELECT personId FROM projectPerson WHERE projectId = ‘projBig’
INTERSECT
SELECT personId

15) Synonyms give you the ability to assign different names to objects. You can alias object names; for example, using the Employee table as Emp. You can also shorten names. This is especially useful when dealing with three and four part names; for example, shortening server.database.owner.object to object.

16) User defined data type -

1. Real World Application Scenario
   It has always been a pain area for a developer to pass multiple rows in batch and get maximum performance benefit. For example, when a developer needed to code for page which accepts order with multiple order items. He had to write his own logic to bundle all the insert statements into either delimited strings or XML type (OPENXML in SQL Server 2000) and then pass those text values to a procedure or statement. This requires the procedure or statement to include the logic necessary for unbundling the values and validating the data structures and then finally insert the records. Lots of work and not so optimized, here table-value parameter can be used to pass a data table filled with rows from .Net application to SQL Server and do the direct insert into the table without doing any kind of additional processing at the server.
2. Limitations
   1. i.     it cannot be used as a column in a table
   2. ii.    its definition cannot be modified after it is created
   3. iii. A DEFAULT value cannot be specified in the definition of a user-defined table type etc
   4. iv. SQL Server does not maintain statistics on columns of table-valued parameter
   5. v. Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine

17) Grouping Sets – It provide facility for combining various select statements of RollUP , Cube and Union All.

For example -SELECT EmpId, Month, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, Yr, Month), (EmpId, Yr), (EmpId))

18) Multiple Inserts - Example - Insert into tblCust values (1,’sachin’),(2,’Mayukh’)

19) Compound operators –

a. += Add and assign

b. -= Subtract and assign

c. *= Multiply and assign

d. /= Divide and assign

e. %= Modulo and assign

f. &= Bitwise AND and assign

g. ^= Bitwise XOR and assign

h.   |= Bitwise OR and assign

20) Syntax Enhancements – Example

a. Declare @TotalHours int = 10

b. Declare  @Wages int =20

c. Declare @total int = TotalHours * Wages