SQL: Clean buffer and cache of Proc

dbcc freeproccache
GO

dbcc dropcleanbuffers
GO

Difference between Throw and RaiseError

Computed Columns or Virtual column in table

This feature introduced in Sql Server 2005

- Computed columns are virtual columns that are not physically stored in the table.
- Their values are recalculated every time they are referenced in a query.
- The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE              statements to physically store computed columns in the table
- You can create an index on a computed column that is deterministic but not precise.
- Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other                locations in which regular expressions can be used.

Example :1

Limitation 

• A computed column cannot be the target of an INSERT or UPDATE statement. 
• You can not reference columns from other tables for a computed column expression directly.
• If you are combining operators of two different data types in your expression then operator of lower precedence will be converted to that of higher precedence. If implicit conversion is not possible then error will be generated.

SPARSE Columns in table

This feature introduced in Sql server 2008

SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).

In SQL Server 2008 maximum column allowed per table is 1024. All the SPARSE columns does not count to this limit of 1024. The maximum limit of SPARSE column is 100,000. In summary any table can have maximum of 100,000 SPARSE and 1024 regular columns.

Example
--------
CREATE TABLE Test
(
  PK_RowID INT IDENTITY(1,1),
  Col1 INT SPARSE,
  Col2 VARCHAR(500) SPARSE,
)

Advantages of SPARSE column are:

• INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
• SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
• SPARSE column saves lots of database space when there are zero or null values in database.

Disadvantages of SPARSE column are:

• SPARSE column does not have IDENTITY or ROWGUIDCOL property.
• SPARSE column cannot be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
• SPARSE column cannot have default value or rule or computed column.
• Clustered index or a unique primary key index cannot be applied SPARSE column. SPARSE column cannot be part of clustered index key.
• Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.

A table operation which involves SPARSE column takes performance hit over regular column.

All Trigger are Enable/Disable by single query

 -- disable all triggers Sql
EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"

-- Enable all triggers Sql
EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"


-- Disable single table and single trigger
ALTER TABLE DemoTrg DISABLE TRIGGER utrDemo

-- Enable single table and single trigger
ALTER TABLE DemoTrg ENABLE TRIGGER utrDemo