T-SQL

bigint (8bytes)

int (4bytes)

smallint(2 bytes) -32768 ~ 32768

tinyint(1byte) unsigned integer 0 – 255

bit 0 or 1

 

Exact Numerics

decimal (precision, scale) (5-17 bytes)

numeric (5-17 bytes)– is identical to decimal, prefer using decimal

money(8 bytes) – up to four decimal parts

smallmoney(4 bytes)

 

Approximate Numerics

float (4 or 8 bytes)

real – is a synonym for a 4-byte float

 

datetime (8 bytes) – accuracy is 3.33 milliseconds

datetime2 (8 bytes) – accuracy is variable, by default is 100 nanoseconds

smalldatetime (4 bytes) – accuracy is 1 minute

date (3 bytes)

time (5 bytes)

datetimeoffset (10 bytes)

 

char – one byte per character, maximum is 8000 characters or bytes

varchar – up to 2GB,  varchar(MAX)

text – up to 2GB, the data is stored separately from the actual row and a pointer is stored in the row.

nchar – two bytes per character, maximum is 4000 characters or 8000 bytes.

nvarchar – up to 2GB

ntext

 

binary

varbinary

image

 

timestamp

uniqueidentifier

hierarchyid

sql_variant

xml

cursor

table

 

 

One - to - zero or more

One - to - one or more

One - to - exactly - one

Many - to - many  (needs three tables)

 

Connections

Transactions

Locks

SQL Server manages locks on a connection basis, which simply means that locks cannot be

held mutually by multiple connections. SQL Server also manages locks on a transaction basis. In the

same way that multiple connections cannot share the same lock, neither can transactions

 

Six Lock modes :

Shared, Update, Exclusive, Intent can be applied to rows of tables or indexes, pages, extents, tables or databases.

Schema, Bulk Update an only apply to tables.

Key Range lock

 

Identity – in the distributed systems, it can’t assure the generated number is unique, in this case, use unique identifier or GUID instead of identity

 

Data  Normalization Rules :

·         Present data to the relational engine that is set accessible

·         Label and identify unique records and columns within a table

·         Promote the smallest necessary result set for data retrieval

·         Minimize storage space requirements by reducing redundant values in the same table and in multiple tables

·         Describe standards for relating records in one table to those in another table

·         Create stability and efficiency in the use of the data system while creating flexibility in its structure.

 

SQL Query à rewritten to optimized query by query optimizerà complied into low-level computer instructions, or object codeà placed into an in-memory cache

Execution plan is saved with that objects in the procedure cache.

Data read from the query is placed in the buffer cache.

 

 

 

Analyzing and Optimizing Query Performance :

From à Onà where à selectà group by à havingà order by

 

1)      Parsing

2)      Resolution

3)      Optimization

4)      Compilation

5)      Caching

6)      Execution

 

Buffer Cache/Procedure Cache

The compiled plan is cached in the procedure cache.

Data from tables and indexes are placed in the buffer cache.

 

Before analyzing queries, it’s better to clear the cache, but it’ll will degrade performance until t he cache is refilled.

 

DBCC DROPCLEANBUFFERS (empty data cache)

DBCC FREEPROCCACHE (empty procedure cache)

 

STATISTICS IO	Returns the number of read and scan operations required to run the
STATISTICS TIME	query.
STATISTICS PROFILE	Returns the number of milliseconds required to parse, compile, and
STATISTICS XML	execute a query.
SHOWPLAN_TEXT	Returns a textual showplan and query statistics.
SHOWPLAN_ALL	Returns an XML document that contains a query plan and query statistics.
SHOWPLAN_XML	Returns an estimated textual showplan. Does not actually run the query.

 

STATISTICS IO

 

Scan count	The number of table or index scans performed.
Logical reads	The number of pages read from the buffer cache.
Physical reads	The number of pages read from disk.
Read - ahead reads	The number of pages read into the buffer cache from disk to be available for logical reads.
LOB logical reads	The number of large - object data pages read from the cache.
LOB physical reads	The number of large - object pages read from disk.
LOB read - ahead reads	The number of large - object pages read into the buffer cache from disk to be made available for logical reads.

 

Remember that a SQL Server data page is an 8KB storage object. So, a

physical read retrieves data 8KB at a time. A read - ahead read is an extent read. SQL Server extents

comprise eight 8KB data pages for a total of 64KB.

 

STATISTICS TIME

 

SQL Server parse and compile time:

CPU time = 47 ms, elapsed time = 141 ms.

SQL Server Execution Times:

CPU time = 94 ms, elapsed time = 317 ms.

 

STATISTICS PROFILE

 

Rows	The number of rows returned by the query processor.
Executes	How many times the query is executed in the batch.
Stmt Text	The first row is generally the text of the T - SQL statement. The remaining rows contain a description of an operation performed to process the query. The rows in the Stmt Text column are divided into two different types. They are either of the type of the query operation, such as SELECT, UPDATE, INSERT, or DELETE, or they are of the PLAN_ROW type, which described the operation being performed, such as an index seek.
StmtId
NodeId
Parent
PhysicalOp	The physical operation associated with the step — for instance, an index scan or index seek.
LogicalOp	The logical operation associated with the step — for instance, an index scan or index seek.
Argument
DefinedValues
EstimateRows
EstimateIO
EstimateCPU
AvgRowSize
TotalSubTreeCost
OutputList

 

a physical hash - match join is conceptually described as an inner join.

 

Positive Searches          not in

Wildcards                      ‘%abc’, ‘abc%’

Logic Operators             or

Join Operators               prefer using inner join instead of using where clause

 

Temporary Table :

Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when they are no longer used.

 

Local Temporary Table #tbl_name

Global Temporary Table ##tbl_name

 

DECLARE @TransData TABLE(ActivityID numeric,
             Data nvarchar(255),
                            [Description] nvarchar(50),
                            DataSequenceDesc nvarchar(50),
                            EventDataSequence nvarchar(10)
)

CREATE TABLE #TEMP( 
Day0 numeric(18, 0),

)