Unix Date Format to SQL DateTime

Epoch to SQL Date Time

Read more "Unix Date Format to SQL DateTime"

Extract Images from A database

You may come across a scenario where you need to extract images from your database to a drive, below script can be used: DECLARE CURSOR_ProductIds CURSOR FOR ( select top 10 iDocID from ScannedDocs) DECLARE @ProductId INT; OPEN CURSOR_ProductIds FETCH NEXT FROM CURSOR_ProductIds INTO @ProductId WHILE (@@FETCH_STATUS <> -1) BEGIN DECLARE @ImageData varbinary(max); SELECT @ImageData […]

Read more "Extract Images from A database"

Sparse Column

A Column which would generally have NULL values can made a sparse column which effectively uses no space for NULLs, Hence effective storage. CREATE TABLE Person ( ID INT PRIMARY KEY, Email VARCHAR(100) SPARSE NULL ) GO Thing to note is that the sparse column has to be NULLABLE A filtered index is an index […]

Read more "Sparse Column"

Filtered Index

Nonclustered indexes that have the addition of a WHERE clause. Although the WHERE clause in a filtered index allows only simple predicates, it provides notable improvements over a traditional nonclustered index. This allows the index to target specific data values – only records with certain values will be added to the index – resulting in […]

Read more "Filtered Index"

Last Update on table

Below Script will help you check who updated the table : SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,* FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( ‘DATABASE’) AND OBJECT_ID=OBJECT_ID(‘TABLE’)

Read more "Last Update on table"

Table Size in all databases

Supose you need to check the table size along with the rows it has , Following Query will help you do it : SELECT NAME, ‘ SELECT @@SERVERNAME AS SERVER, ”’+NAME+”’ [Database Name], t.Name AS TableName, p.rows AS RowCounts, CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB, CAST(ROUND((SUM(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) AS […]

Read more "Table Size in all databases"