Friday, August 28, 2015

SQL – Wide Tables

As we know that tables are used to store all the data and stated as the database objects. They stored the data in a row-and-column format which is very similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record.

A standard user-defined table can have up to 1,024 columns. The number of rows in the table is limited only by the storage capacity of the server.
Wide Tables
This feature was introduced with SQL Server 2008 release on-wards. A wide table is just a table that uses column sets and sparse columns. It still follows the same width restrictions per row (8019 bytes). To make a table wide you just add a column set to its definition. They come into the picture whenever business needs to increase the total of columns more than 1024 and wide table can have to 30,000. 
The number of non-sparse columns is still 1024 and the max size of the row is still 8000 bytes.

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.
True fact about Sparse columns
  • Sparse columns are ordinary columns, with the addition of the SPARSE property. 
  • Sparse columns do not support primary keys, data compression, NOT NULL constraints, or default values.

A sparse column set gathers all sparse columns into a new column that is similar to a derived or computed column, but with additional functionality – its data can be updated and selected from directly.
True fact about column set
  • A column set may be included in a table definition at the time of creation, or added later, if no sparse columns exist yet. 
  • Only one column set may be used per table, and a column set cannot be added to a table that already contains sparse columns. 
  • Replication, distributed queries, and change data capture (CDC) do not support using column sets. 
  • Security permissions can be granted and revoked from the column set column, similar to any other column in the table.


Whenever designing the wide tables with sparse columns, keep in mind that an additional 2 bytes of overhead are required for each non-null sparse column in the table when a row is being updated. As a result of this additional memory requirement, updates can fail unexpectedly with error 576 when the total row size, including this memory overhead, exceeds 8019, and no columns can be pushed off the row.

A wide table has defined a column set, which is an untyped XML representation that combines all the sparse columns of a table into a structured output. The number of indexes and statistics is also increased to 1,000 and 30,000, respectively.

How to create Wide Table
However, by leveraging the column set functionality, we can create a table with greater than 1,024 columns.  The AdventureWorks2012 example from Books Online shows a nice example, where the column SpecialPurposeColumns is defined as the column set that returns the 4 sparse columns defined in the table definition:

USE AdventureWorks2012;
GO

-- create wide table with sparce and column set
CREATE TABLE DocumentStore
    (
DocID int PRIMARY KEY,
     Title varchar(200) NOT NULL,
     ProductionSpecification varchar(20) SPARSE NULL,
     ProductionLocation smallint SPARSE NULL,
     MarketingSurveyGroup varchar(20) SPARSE NULL,
     MarketingProgramID int SPARSE NULL,
    SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);
GO
-- Insert values
INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);
GO

-- Insert Values
INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35');
GO

-- Fetch data from wide table
SELECT * FROM DocumentStore;
GO

DocID
Title
SpecialPurposeColumns
1
Tire Spec 1
<ProductionSpecification>AXZZ217</ProductionSpecification>
<ProductionLocation>27</ProductionLocation>
2
Survey 2142
<MarketingSurveyGroup>Men 25 - 35</MarketingSurveyGroup>

Limitation of Wide Tables
Wide tables have the following performance implications.
  • Wide tables can increase the cost to maintain indexes on the table. We recommend that the number of indexes on a wide table be limited to the indexes that are required by the business logic. 
  • As the number of indexes increases, so does the DML compile-time and memory requirement. Nonclustered indexes should be filtered indexes that are applied to data subsets. For more information, see Create Filtered Indexes.
  • Applications can dynamically add and remove columns from wide tables. When columns are added or removed, compiled query plans are also invalidated. 
  • When data is added and removed from a wide table, performance can be affected. Applications must be designed for the projected workload so these changes to the table data are minimized.
  • Limit the execution of DML statements on a wide table that update multiple rows of a clustering key. These statements can require significant memory resources to compile and execute.
  • Switch partition operations on wide tables can be slow and might require large amounts of memory to process. 
  • The performance and memory requirements are proportional to the total number of columns in both the source and target partitions.
  • Update cursors that update specific columns in a wide table should list the columns explicitly in the FOR UPDATE clause. This will help optimize performance when you use cursors.
  • There are size restrictions on the rows and it can be slower to retrieve data than if you use related tables (even those with one-to-one relationships).
  • Transnational replication supports sparse columns, but it does not support column sets, which can be used with sparse columns.
  • Merge replication does not support sparse columns or column sets. Change data capture supports sparse columns, but it does not support column sets.
Summary
To make a table into a wide table, you must create sparse columns and also add a column set. If you don't create a column set your table will still be limited to 1024 columns. Sparse columns and column sets in SQL Server provide a way of accommodating the less-structured data that has always proved to be tricky for the relational model. Wide tables can increase the cost to maintain indexes on the table and switching partition operations work very slow and require more memory to process. 

To know more on the different kinds of the tables in SQL Servers at
  1. SQL – Wide Tables
  2. SQL - Table Variables
  3. SQL - Temp Table (Local & Global Temp Tables)
  4. SQL - Common Table Expression
  5. SQL - Difference between Table Variable and Common Type Expression
  6. SQL - Difference between Temp Table and CTE
  7. SQL - Difference between Temp Table and Table Variable

References:
https://msdn.microsoft.com/en-us/library/ms189084(v=sql.110).aspx

Monday, August 24, 2015

SQL - Event Notifications

This is also a very beautiful feature of SQL Server which was introduced with the release of SQL Server 2005. Event Notifications mechanism is used for tracking all events or changes happening at the database or instance level.

Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled as SQL Trace.
Event Notifications work asynchronously like as SQL Trace does outside the scope of the transaction that caused the event and never used the transaction resources. The Most important this is that it won’t impact those transactions if something goes horribly awry (think errors, blocking, etc.). The event notification can’t be rolled back if the firing transaction rolls back due to working outside the scope of the transaction.

Event Notifications can do more than just record an event, they can respond to it like as DDL Triggers do and they can insert event information into a table. They can be used inside a database application to respond to events without using any resources defined by the immediate transaction.
Event data can be used by applications that are running together with SQL Server to track progress and make decisions. Event notifications can be used to do the following:
  • Log and review changes or activity occurring on the database.
  • Perform an action in response to an event in an asynchronous instead of synchronous manner.
In simple words, we can easily refer Event notifications as they can offer a programming alternative to DDL triggers and SQL Trace.

When an event notification is created, one or more Service Broker conversations between an instance of SQL Server and the target service you specify are started and these conversations typically remain open as long as the event notification exists as an object on the server instance. But in some error cases the conversations can close before the event notification is dropped. These conversations are never shared between event notifications that mean every event notification will have its own exclusive conversations. Ending a conversation explicitly prevents the target service from receiving more messages, and the conversation will not reopen the next time the event notification fires.

Event information is delivered to the Service Broker as a variable of type xml that provides information about when an event occurs, about the database object affected, the Transact-SQL batch statement involved, and other information. Event data can be used by applications that are running together with SQL Server to track progress and make decisions.


Permissions for Event Notifications
Based on the requirements for the various targets/objects (Databases, servers etc.), they need to specific permissions on these targets/objects-
  1. To create an event notification which is scoped to the database (ON DATABASE), requires CREATE DATABASE DDL EVENT NOTIFICATION permission in the current database.
  2. To create an event notification on a DDL statement that is scoped to the server (ON SERVER), requires CREATE DDL EVENT NOTIFICATION permission in the server.
  3. To create an event notification on a trace event, requires CREATE TRACE EVENT NOTIFICATION permission in the server.
  4. To create an event notification that is scoped to a queue, requires ALTER permission on the queue.
How to create an Event Notification in a database
This will be notified you whenever a new table is created or altered in your database.
Use AdventureWorks2012
Go

-- Create Event Notification which will be fired
-- Whenever a new table is created
CREATE EVENT NOTIFICATION CreateTableNotification
ON DataBase
FOR CREATE_TABLE
TO SERVICE 'EventNotificationService', 'Current Database';

-- Create Event Notification which will be fired
-- Whenever an existing table is altered
CREATE EVENT NOTIFICATION AlteredTableNotification
ON DataBase
FOR ALTER_TABLE
TO SERVICE 'EventNotificationService', 'Current Database';

-- check event nofification
SELECT * FROM sys.event_notifications
WHERE name IN ('CreateTableNotification', 'AlteredTableNotification')


Event notifications can be created directly in the SQL Server Database Engine or by using the WMI Provider for Server Events. You can also create a notification event group; for example you can create a single notification with DDL_TABLE_EVENTS (CREATE_TABLE, ALTER_TABLE and DROP_TABLE) to track all events of a table like creation, alteration and drop.   
Use AdventureWorks2012
Go

-- Create Event Notification which will be fired
-- Whenever a new table is created, altered table or dropped table
CREATE EVENT NOTIFICATION DDLTableEventNotification
ON DataBase
FOR DDL_TABLE_EVENTS

TO SERVICE 'EventNotificationService', 'Current Database';

-- Create table to store table monitoring
CREATE TABLE TableMonitoring
(
PostTime DATETIME,
LoginName NVARCHAR(100),
EventType NVARCHAR(100),
TSQLCommand NVARCHAR(2000) NULL ,
ObjectName NVARCHAR(100) NULL,
ObjectType NVARCHAR(100) NULL,
SchemaName NVARCHAR(100) NULL,
)
GO

--Create the DDL trigger
CREATE TRIGGER TrTableMonitoring
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
DECLARE @Data XML
SET @Data = EventData()

-- Insert Log Values
INSERT TableMonitoring
(
PostTime, LoginName, EventType, TSQLCommand,
ObjectType,ObjectName,SchemaName
)
VALUES
(GETDATE(),
CONVERT(NVARCHAR(100), CURRENT_USER),  
@Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
@Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
@Data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)'), 
@Data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)'),
@Data.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)') ) ;
GO

Server Scoped Event Notification
The following example creates the required objects to set up a target service using Service Broker. The target service references the message type and contract of the initiating service specifically for event notifications. Then an event notification is created on that target service that sends a notification whenever an Object_Created trace event happens on the instance of SQL Server.
Use AdventureWorks2012
Go

--Create a queue to receive messages.
CREATE QUEUE NotifyQueue ;
GO

--Create a service on the queue that references
--the event notifications contract.
CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

--Create a route on the service to define the address
--to which Service Broker sends messages for the service.
CREATE ROUTE NotifyRoute
WITH SERVICE_NAME = 'NotifyService',
ADDRESS = 'LOCAL';
GO

--Create the event notification.
CREATE EVENT NOTIFICATION Logs_DDL
ON SERVER
FOR Object_Created
TO SERVICE 'NotifyService', '8140a771-3c4b-4479-8ac0-81008ab17984';



Event Notifications vs. Triggers :
The following table compares and contrasts triggers and event notifications.
Triggers
Event Notifications
DML triggers respond to data manipulation language (DML) events. DDL triggers respond to data definition language (DDL) events.
Event notifications respond to DDL events and a subset of SQL trace events.
Triggers can run T-SQL or common language runtime (CLR) managed code.
Event notifications do not run code. Instead, they send xml messages to a Service Broker service.
Triggers are processed synchronously, within the scope of the transactions that cause them to fire.
Event notifications may be processed asynchronously and do not run in the scope of the transactions that cause them to fire.
The consumer of a trigger is tightly coupled with the event that causes it to fire.
The consumer of an event notification is decoupled from the event that causes it to fire.
Triggers must be processed on the local server.
Event notifications can be processed on a remote server.
Triggers can be rolled back.
Event notifications cannot be rolled back.
DML trigger names are schema-scoped. DDL trigger names are database-scoped or server-scoped.
Event notification names are scoped by the server or database. Event notifications on a QUEUE_ACTIVATION event are scoped to a specific queue.
DML triggers are owned by the same owner as the tables on which they are applied.
The owner of an event notification on a queue may have a different owner than the object on which it is applied.
Triggers support the EXECUTE AS clause.
Event notifications do not support the EXECUTE AS clause.
DDL trigger event information can be captured using the EVENTDATA function, which returns an xml data type.
Event notifications send xml event information to a Service Broker service. The information is formatted to the same schema as that of the EVENTDATA function.
Metadata about triggers is found in the sys.triggers and sys.server_triggers catalog views.
Metadata about event notifications is found in the sys.event_notifications and sys.server_event_notifications catalog views.

Event Notifications vs. SQL Trace
The following table compares and contrasts using event notifications and SQL Trace for monitoring server events.
SQL Trace
Event Notifications
SQL Trace generates no performance overhead associated with transactions. Packaging of data is efficient.
There is performance overhead associated with creating the XML-formatted event data and sending the event notification.
SQL Trace can monitor any trace event class.
Event notifications can monitor a subset of trace event classes and also all data definition language (DDL) events.
You can customize which data columns to generate in a trace event.
The schema of the XML-formatted event data returned by event notifications is fixed.
Trace events generated by DDL are always generated, regardless of whether the DDL statement is rolled back.
Event notifications do not fire if the event in the corresponding DDL statement is rolled back.
Managing the intermediate flow of trace event data involves populating and managing trace files or trace tables.
Intermediate management of event notification data is accomplished automatically through Service Broker queues.
Traces must be restarted every time the server restarts.
After being registered, event notifications persist across server cycles and are transacted.
After being initiated, the firing of traces cannot be controlled. Stop times and filter times can be used to specify when they initiate. Traces are accessed by polling the corresponding trace file.
Event notifications can be controlled by using the WAITFOR statement against the queue that receives the message generated by the event notification. They can be accessed by polling the queue.
ALTER TRACE is the least permission that is required to create a trace. Permission is also required to create a trace file on the corresponding computer.
Least permission depends on the type of event notification being created. RECEIVE permission is also needed on the corresponding queue.
Traces can be received remotely.
Event notifications can be received remotely.
Trace events are implemented by using system stored procedures.
Event notifications are implemented by using a combination of Database Engine and Service Broker Transact-SQL statements.
Trace event data can be accessed programmatically by querying the corresponding trace table, parsing the trace file, or using the SQL Server Management Objects (SMO) TraceReader Class.
Event data is accessed programmatically by issuing XQuery against the XML-formatted event data, or by using the SMO Event classes.

Summary
Event Notifications are asynchronous and they can target local or remote services. They allow scale out of auditing and can easily respond to DDL and Trace events. They are easily integrated WMI and SQL Agent. They allow custom, scalable, performance enterprise auditing out of the box.

References:
https://msdn.microsoft.com/en-us/library/ms186376.aspx
https://msdn.microsoft.com/en-us/library/ms189453(v=sql.110).aspx