SQL Server 2008 allows objects to be created for high level business like Customers, Parts, Inventory. Instead of returning individual rows and tables, these entities could be used. The E-R model will now match with the SQL model.
2. What is Change Data Capture (CDC) feature in sql server 2008?
Change Data Capture is a feature that is used for tracking the changes on a table. The process involves in steps.
Step 1 - Creation of a database
The database name is MyDataBase
USE [master]
GO
/*** Object: Database [MyDataBase] ***/
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MyDataBase')
DROP DATABASE [MyDataBase]
GO
USE [master]
GO
/*** Object: Database [MyDataBase] ***/
CREATE DATABASE [MyDataBase]
GO
Step 2 - Creation of a table in MyDataBase database
USE [MyDataBase]
GO
/*** Object: Table [dbo].[MyTable] ***/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDataBase]
GO
CREATE TABLE [dbo].[MyTable](
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [MyTable_PK] PRIMARY KEY
GO
Step 3 - Enabling Change Data Capture feature.
The Transact SQL command enables the Change Data Capture feature.
After enabling the Change Data Capture, a schema along with objects is created.
USE [MyDataBase]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO
Using the cdc the columns, tables, history of ddl commands, index columns and time mappings are created as follows:
cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
3. What is MERGE in sql server 2008?
Merge statement allows a single statement for INSERT, DELETE and UPDATE a row that depends on a condition. The target table for certain operations is based on the results of join with a source table. The following example illustrates the use of MERGE.
MERGE InventoryMaster AS invmstr
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON invmstr. InventoryID = src. InventoryID
WHEN MATCHED THEN
UPDATE SET invmstr.Descr = src.Descr
WHEN NOT MATCHED THEN
INSERT (InventoryID, Descr) VALUES (src. InventoryID, src.Descr);.
4. What are spatial data types - geometry and geography in sql server 2008?
Location based data can be seamlessly consumed and extended by the enterprises with the comprehensive support of spatial data types.
Geometry data type: This data type supports a flat 2D surface with points of XY coordinates. The points could be on line string, on lines and also mark the polygon edges. There are certain methods like STintersects, STarea, STDistance and STTouch which uses geometry data types.
Geography data type: The similar methods of geometry data type are used in this data type. But the type reflects on curved 2D surfaces. The ST* functions are utilized but the results in the curvature.
DECLARE @gmtry geometry;
SET @gmtry = geometry::STGeomFromText('POINT (3 4)', 0);
DECLARE @grphy geography;
SET @grphy = geography::STGeomFromText('POINT (3 4)', 4326);
Certain observations need to be considered. They are:
- A polygon is tried which is larger than a hemisphere, an ArgumentException is thrown.
- If the returned result is larger than a hemisphere, then a NULL value is returned.
5. Explain how to store and query Spatial Data?
Spatial data is stored by using Geometry and Geography data types that are introduced in SQL Server 2008.
Geometry data type is created as follows:
CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO
The data into the geometry data column is persisted by using the following INSERT command
INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));
The data in the geometry data columns is queried by using the following DECLARE and SELECT statements:
DECLARE @geom1 geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;
SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;
SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();
6. Explain TIME data type, datetime2, datetimeoffset data type in sql server 2008?
TIME Data type:
TIME data type of SQL Server 2008 allows to exclusively storing the time.
The following is an example of using TIME data type:
DECLARE @dot as TIME
SET @dot = get date()
PRINT @dt
The above script displays the output as HH:MM:SS.SSSSSSS format. The TIME has the data range from 00:00:00.0000000 through 23:59:59.9999999.
DATETIME2 Data Type:
DATETIME2 is a data type which returns date/time. It provides larger factional seconds and the year compared to DATETIME data type. There are options to specify the number fractions as per the need. The maximum fraction is 7 and the minimum fraction is 0.
The following is an example of using DATETIME2 data type:
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
PRINT @dt7
The above script displays the date as YYYY-MM-DD HH:MM:SS.SSSSSSS format.
DATETIMEOFFSET Data type:
To store date, time along with time zone, the DATETIMEOFFSET is used. This is important when dealing with date of several countries with various time zones. The clock is based on 24-hour clock.
The following is an example of using DATETIMEOFFSET data type:
DECLARE @dt DATETIMEOFFSET(0)
SET @dt = '2007-10-29 22:50:55 -1:00'
DECLARE @dt1 DATETIMEOFFSET(0)
SET @dt1 = '2007-10-29 22:50:55 +5:00'
SELECT DATEDIFF(hh,@dt,@Dt1)
7. Explain Sparse Columns of sql server 2008?
A column with an optimized storage for null values is known as sparse column. Sparse columns reduce the storage space needs for null values. In a scenario of saving 20 percent to 40 percent of storage space, sparse columns can be considered. They are created using CREATE TABLE or ALTER TABLE statements. Sparse columns can be used with
- Column sets: The statements INSERT, DELETE, UPDATE could be referred the sparse columns by name. The same an also be combined into a single XML Column. This is a column set.
- Filtered index: As there are several null value rows in sparse columns, they are appropriate for filtered indexes. The filtered index on a sparse column can accommodate only the rows / tuples which populate values. The index created with filtered index is small and more efficient index.
A large number of user defined properties can be accessed by using sparse columns and filtered indexes enabled applications such as Share Point Services of windows are efficiently store and access.
8. What is policy based administration feature of SQL Server 2008?
Policy based database administration allows the DBA for managing the instances and objects of SQL Server 2008 across the enterprise by a defined policies that are set. These policies are the rules and regulations which determine the possible ways of what to do and what not to do and the way the violations of policies are enforced and reported. The policies are well defined by using facets and conditions.
Facet: An object which contains the properties which well describes a component.
Condition: A logical expression that is defined on a facet. It is used for identification of acceptable property values of the facet.
9. What is filestream storage of sql server 2008?
The complexity of application development and reduces the managing unstructured data cost. The manageability is increased by extending various capabilities which are available only to relational data to non-relational data.
SQL Server 2008 came with 2 new capabilities for persisting BLOB data:
1. FILESTREAM: It is an attribute that can be set on a ‘varbinary' column for data persistence on the file streams. This enables the benefits from fast streaming capabilities and storage capabilities.
2. Remote BLOB storage: It is a client-side API which reduces the application building complexity and relies on an external persistence for BLOBs and a database for relational data.
SQL Server 2008 will also continue the support for standard BLOB type through the data type ‘varbinary'.
10. What is sql server 2008 backup compression?
A compression backup is smaller than uncompressed backup. The backup speed significantly increases because it needs less disk space and I/O operations. The CPU usage is increased and the increased and the additional CPU that is consumed by the process of compression would impact the operations that are running concurrently.
The following processes can be isolated for obtaining a good picture
- Physical disk counters
- Device throughput bytes / second counter of SQL Server Backup Device object
- Backup/Restore throughput / second counter of SQL Server Databases object.
