Which are new data types introduced in SQL SERVER 2008?
The
GEOMETRY Type: The GEOMETRY data type
is a system .NET common language runtime (CLR) data type in SQL Server. This
type represents data in a two-dimensional Euclidean coordinate system.
The
GEOGRAPHY Type: The GEOGRAPHY datatype’s
functions are the same as with GEOMETRY. The difference between the two is that
when you specify GEOGRAPHY, you are usually specifying points in terms of
latitude and longitude.
New Date
and Time Datatypes: SQL Server 2008 introduces
four new datatypes related to date and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2.
- DATE: The new DATE type just stores the date itself. It is based on the Gregorian calendar and handles years from 1 to 9999.
- TIME: The new TIME (n) type stores time with a range of 00:00:00.0000000 through 23:59:59.9999999. The precision is allowed with this type. TIME supports seconds down to 100 nanoseconds. The nin TIME (n) defines this level of fractional second precision, from 0 to 7 digits of precision.
- The DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-zone-aware version of a datetime datatype. The name will appear less odd when you consider what it really is: a date + a time + a time-zone offset. The offset is based on how far behind or ahead you are from Coordinated Universal Time (UTC) time.
- The DATETIME2 Type: It is an extension of the datetime type in earlier versions of SQL Server. This new datatype has a date range covering dates from January 1 of year 1 through December 31 of year 9999. This is a definite improvement over the 1753 lower boundary of the datetime datatype. DATETIME2 not only includes the larger date range, but also has a timestamp and the same fractional precision that TIME type provides
What are the Advantages of using CTE?
- Using CTE improves the readability and makes maintenance of complex queries easy.
- The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated.
- CTE can be defined in functions, stored procedures, triggers or even views.
- After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.
How can we rewrite sub-queries into simple select statements or with joins?
Yes we can write using
Common Table Expression (CTE). A Common Table Expression (CTE) is an expression
that can be thought of as a temporary result set which is defined within the
execution of a single SQL statement. A CTE is similar to a derived table in
that it is not stored as an object and lasts only for the duration of the
query.
E.g.
USE AdventureWorksGOWITH EmployeeDepartment_CTE AS (SELECT EmployeeID,DepartmentID,ShiftIDFROM HumanResources.EmployeeDepartmentHistory)SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftIDFROM HumanResources.Department edINNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID =ed.DepartmentIDGO
0 comments
Post a Comment