Playing with NULL

A NULL values is an unknown value, different from a zero or an empty value.

There are few points that must be remembered when you are dealing with NULL values:

1.       No two NULL values are equal.

2.       Comparison between two NULL values or between one NULL and any other values results in an UNKNOWN value.

3.       Logical (e.g. AND, OR etc.) and Comparison (e.g. =, >, < etc.) Operators can return a third result UNKNOWN during comparison between two expressions.

4.       Concatenating two string values, if one of strings is Null, then result will be a null value. (SET CONCAT_NULL_YIELDS_NULL is ON)

How to manipulate NULL values?

There are three cases where you can face NULL values occurrences,

First case – Conditional expressions – used in WHERE clause, with IF statements, or with CASE statements, all above are for comparison between columns or between a column and a value.

Second case – Data sets returned through a selection or SELECT statement. Here you apply comparisons and conversion by evaluating or equating the values.

Third case – DML Statements, mostly considered statement here is INSERT statement, for NULL allowable columns, where you are not providing values for them, NULL is inserted.

You can use mix of below given functions and clauses for both first and second cases, while for third case if the columns are null allowable you can specify DEFAULT constraint with some appropriate default value.

There are three functions and two clauses that you can use to deal with NULL values.

Functions:

a.       ISNULL (Check_Expression, Replacement_value) – Replaces NULL with the specified replacement value.

b.      NULLIF (Expression, Expression) – Returns a NULL value if two provided expressions are equal or first expression if they are not equal.

c.       COALESCE (Expression,…..n) – Returns first non-NULL expression among n expressions or NULL if all expressions contain null.

Clauses:

a.       Expression IS NULL – Determines whether the specified expression is null. Returns TRUE if expression contains a null value otherwise FALSE.

b.      Expression IS NOT NULL – same as above, but it negates the result of above clause. FALSE for the expression containing null values otherwise TRUE.

Example:

/*

@String1 has default value.

@String2 has null as default.

@String3 has null as default.

*/

DECLARE @String1 varchar(10) = 'String1'

            ,@String2 varchar(10)

            ,@String3 varchar(10)

           

SELECT ISNULL(@String2, @String1) AS "ISNULL"

SELECT NULLIF(@String2, @String3) AS "NULLIFF"

SELECT COALESCE(@String2, @String3, @String1) AS "COALESCE"

SELECT 1 AS "IS NULL" WHERE @String2 IS NULL

SELECT 1 AS "IS NOT NULL" WHERE @String1 IS NOT NULL

Monitoring Transactional Replication Status - SQL Server 2005, 2008, 2008 R2

USE DISTRIBUTION

GO

SELECT

      s.agent_id

      ,a.id

      ,s.article_id

      ,a.subscriber_id

      ,ar.Source_owner

      ,ar.Source_object

      ,ar.destination_owner

      ,ar.destination_object

      ,s.undelivcmdsindistdb

      ,a.publisher_db

      ,a.subscriber_db

      ,a.publication

FROM distribution.dbo.msdistribution_status s with (nolock)

INNER JOIN (SELECT * FROM msdistribution_agents with (nolock)) AS a ON a.id = s.agent_id

INNER JOIN (SELECT * FROM msarticles with (nolock)) AS ar

            ON ar.article_id = s.article_id

                  AND a.publisher_Db = ar.publisher_db

WHERE a.subscriber_db<>'virtual'

AND s.undelivcmdsindistdb>0

ORDER BY  s.undelivcmdsindistdb DESC

Script: view information of currently running SQL Server Jobs

Below script returns five (5) columns result set as output. You can further enhance this query to accommodate your requirement.

1. Job_Name: This is job’s registered name on the server’s instance.
2. Last_Executed_Step_ID: This is the ID of the last executed step in the job. E.g. if a job has 4 steps, and currently it is running 3^rd step, so last executed step will be 2 and it will be populated with 2 against Job’s 2^nd step row, while all other steps rows will have 0.
3. Step_ID: This query returns all steps included in the job along with their id, while 2^nd column, returns id same as in this column.
4. Step_Name: This reflects job step name that was given during job creation.
5. Start_Execution_Date: This column returns execution date and time of this job. So you can get an idea when this started execution.

SELECT 
LTRIM(RTRIM(CONVERT(VARCHAR(100),j.name))) job_name

,ISNULL(a.last_executed_step_id,'') last_executed_step_id

,s.step_id

,RTRIM(CONVERT(VARCHAR(100),s.step_name))step_name

,a.start_execution_Date

FROM msdb.dbo.sysjobs j WITH (NOLOCK)

INNER JOIN msdb.dbo.sysjobsteps s WITH (NOLOCK) ON j.job_id = s.job_id

LEFT OUTER JOIN msdb.dbo.sysjobactivity a WITH (NOLOCK) ON j.job_id=a.job_id

AND (s.step_id=a.last_executed_step_id OR a.last_executed_Step_id IS NULL)

AND run_requested_date>=CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME)

AND stop_execution_Date IS NULL

WHERE j.enabled=1

AND j.category_id IN (0,1,2,3,4,5,98,99)

AND j.job_id in 

(SELECT job_id FROM msdb.dbo.sysjobactivity WITH (NOLOCK)

WHERE start_execution_date>= CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME) 

AND stop_execution_date IS NULL )

ORDER BY 1,3

Compound Operators - SQL SERVER 2008/R2

You are aware about arithmetic, bitwise and assignment operators, don’t worry if you are not, see below you will get an idea about them.

Arithmetic Operators: There are 5 arithmetic operators that are Plus (+), Minus (-), Divide (/), Multiply (*) and Modulo (%)

Bitwise Operators: There are three bitwise operators that are Bitwise AND (&), Bitwise OR (|) and Bitwise Exclusive OR (^).

Assignment Operators: There is only one assignment operator Equal to (=)

Example to use them (in Older version):

-- Arithmetic

DECLARE @var INT

SET @var = 1 -- Assignment

SET @var = @var + 2 -- Increment and them assignment

SELECT @var

SET @var = @var - 1 -- decrement and them assignment

SELECT @var

SET @var = @var & 3 -- Bitwise AND and then assignment

SELECT @var

SET @var = @var | 3 -- Bitwise AND and then assignment

SELECT @var

You can see above old method for increment or decrement and then assignment by using Plus (+) and Minus (-) operators. You can use other operators as well in the same way. But this was an old way for of manipulation and then result assignment. We will see enhancement in operator utilization from SQL Server 2008 that is Compound Operators.

Compound Operators:

These operators defined by combining one operator from Arithmetic/Bitwise and 2^nd operator from Assignment. It first performs associated operation on left operand with right operand and then saves result to left operand.

These are 8 in numbers.

1. += (Add EQUALS), you can use it for string concatenation as well.
2. -= (Minus EQUALS)
3. *= (Multiply EQUALS)
4. /= (Divide EQUALS)
5. %= (Modulo EQUALS)
6. &= (Bitwise AND EQUALS)
7. |= (Bitwise OR EQUALS)
8. ^= (Bitwise Exclusive OR EQUALS)

Example:

Compare this example with above one:

DECLARE @var INT

SET @var = 1 -- Assignment

SET @var += 2 -- Increment and them assignment

SELECT @var

SET @var -= 1 -- Increment and them assignment

SELECT @var

SET @var &= 3 -- Bitwise AND and then assignment

SELECT @var

SET @var |= 3 -- Bitwise AND and then assignment

SELECT @var

Enhanced VALUES Clause (ROW Constructor) – SQL SERVER 2008 / R2

 Old Style:

If you see at previous versions of SQL Server (Older than 2008), you were able to insert only a single row by using values clause.

INSERT INTO dbo.mytable(id, val) VALUES(1, 'First Value');

Or

If you were inserting through SELECT statement, you were able to specify a single row values or by combining multiple rows by using UNION/UION ALL operator.

INSERT INTO dbo.mytable(id, val)

SELECT 1 AS ID, 'First Value' AS VAL

UNION ALL SELECT 2, 'Second Value'

UNION ALL SELECT 3, 'Thrid Value'

New Style:

Now, using SQL Server 2008 or Later, you can find enhanced version of VALUES clause that works as a collection of row(s). You can specify up to 1000 rows by using this clause. Further, it treats it a single transaction, in case of failure, all rows will be rolled back.

Try this example:

CREATE TABLE dbo.mytable

(ID INT,

 VAL VARCHAR(100)

 );

 -- Inertion by using Values clause

 INSERT INTO dbo.mytable (ID, VAL )

 VALUES (1, 'First Value')

 ,(2, 'Second Value')

 ,(3, 'Third Value')

-- Insertion by using Values clause with Select Statement

 INSERT INTO dbo.mytable (ID, VAL )

 SELECT *

 FROM (VALUES (1, 'First Value')

 ,(2, 'Second Value')

 ,(3, 'Third Value') ) as temp(ID, VAL)

Further, you can use VALUES clause to parse a string of multiple parameter values to get them in rows.

Try below script:

DECLARE @StrValues NVARCHAR(100)

DECLARE @Str NVARCHAR(500)

SET @StrValues = '(''A''),(''B''),(''C'')'

SET @Str = 'SELECT * FROM (VALUES ' + @StrValues + ' ) as Temp(Col1)'

EXEC SP_EXECUTESQL @Str

Database & Disk space analysis

BEGIN

CREATE TABLE #LOGSPACE
(DBNAME VARCHAR(100)
,LOGSIZE DECIMAL(30,10)
,LOGSPACE DECIMAL(30,10)
,STATUS INT
)

CREATE TABLE #DRIVESPACE
(DRIVE VARCHAR(2)
,DRIVESPACE BIGINT
)

INSERT INTO #LOGSPACE
EXEC ('DBCC SQLPERF(LOGSPACE)')

INSERT INTO #DRIVESPACE
EXEC ('MASTER.SYS.XP_FIXEDDRIVES')

SELECT F.TYPE_DESC DBFILES
,LEFT(F.PHYSICAL_NAME,1) DRIVE
,F.STATE_DESC DBSTATE
,((F.[SIZE]*8)/1024.00)/1024.00 DBCURRENTSIZE
,ISNULL(((A.RESERVED*8)/1024.00)/1024.00 - ((D.LOGSIZE/1024.00) * (D.LOGSPACE/100.00)),(L.LOGSIZE/1024.00) * (L.LOGSPACE/100.00)) DBRESERVEDSIZE
,CASE WHEN F.IS_PERCENT_GROWTH=0 THEN 'PAGES' WHEN F.IS_PERCENT_GROWTH=1 THEN 'PERCENTAGE' END DBGROWTHTYPE
,F.GROWTH DBGROWTHVALUE
,CASE WHEN F.IS_PERCENT_GROWTH=0 THEN ((F.GROWTH*8)/1024.00)/1024.00 WHEN F.IS_PERCENT_GROWTH=1 THEN (((F.[SIZE]*8)/1024.00)*(F.GROWTH/100.00))/1024.00 END DBGROWTHSIZE
,CASE WHEN F.MAX_SIZE=0 THEN 'FIXED SIZE' WHEN F.MAX_SIZE=-1 THEN 'UNLIMITED' ELSE CONVERT(VARCHAR,((CONVERT(BIGINT,F.MAX_SIZE)*8)/1024.00)/1024.00) END MAX_SIZE
,DS.DRIVESPACE/1024.00 FREESPACEONDRIVE
INTO #SPACEANALYSISREPORT
FROM SYS.DATABASE_FILES F
INNER JOIN #DRIVESPACE DS ON DS.DRIVE = LEFT(F.PHYSICAL_NAME,1)
LEFT OUTER JOIN #LOGSPACE L ON L.DBNAME = DB_NAME() AND F.TYPE_DESC='LOG'
LEFT OUTER JOIN #LOGSPACE D ON D.DBNAME = DB_NAME() AND F.TYPE_DESC='ROWS'
LEFT OUTER JOIN
(SELECT DB_ID() DATABASE_ID, 'ROWS' TYPE_DESC,SUM(TOTAL_PAGES) RESERVED, SUM(USED_PAGES) USED FROM SYS.ALLOCATION_UNITS) A ON A.TYPE_DESC=F.TYPE_DESC

SELECT * FROM #SPACEANALYSISREPORT

DROP TABLE #LOGSPACE
DROP TABLE #DRIVESPACE
DROP TABLE #SPACEANALYSISREPORT

END

Space used in tempdb by each Session

SELECT DB_NAME(DATABASE_ID) DBNAME
,SP.SESSION_ID
,S.LOGIN_NAME
,S.HOST_NAME
,(SUM(USER_OBJECTS_ALLOC_PAGE_COUNT)*8)/1024.00 ALLOCATED_SPACE_MB
,(SUM(USER_OBJECTS_DEALLOC_PAGE_COUNT)*8)/1024.00 DEALLOCATED_SPACE_MB
FROM TEMPDB.SYS.DM_DB_SESSION_SPACE_USAGE SP
INNER JOIN TEMPDB.SYS.DM_EXEC_SESSIONS S ON S.SESSION_ID = SP.SESSION_ID AND S.HOST_NAME IS NOT NULL
GROUP BY DB_NAME(DATABASE_ID),SP.SESSION_ID,S.LOGIN_NAME, S.HOST_NAME
ORDER BY ALLOCATED_SPACE_MB DESC

Reading Sql Server and Sql Agent log files using T-SQL

xp_readerrrorlog

It is an undocumented extended stored procedure with at least 7 parameters (default null), that calls a dll to show results.

Parameters details as:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
5. ? - Unknown
6. ? - Unknown
7. Sort order for results: N'asc' = ascending, N'desc' = descending

Example:
xp_readerrorlog 0, 2, 'ODBC', 'SQL Server',Null, Null , N'ASC'

LogDate                                   ErrorLevel     Text
2008-11-23 07:17:55.000   3                      [102] SQL Server ODBC driver version 9.00.3042

Current Identity value, Identity usage and other identity information

SELECT
QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) + '.' + QUOTENAME(T.NAME) AS TABLENAME,
C.NAME AS COLUMNNAME,
CASE C.SYSTEM_TYPE_ID WHEN 127 THEN 'BIGINT' WHEN 56 THEN 'INT'
WHEN 52 THEN 'SMALLINT' WHEN 48 THEN 'TINYINT' END AS 'DATATYPE', IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS CURRENTIDENTITYVALUE,
IDENT_INCR(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYINCREMENT,
IDENT_SEED(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYSEED,
CASE C.SYSTEM_TYPE_ID
WHEN 127 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 9223372036854775807
WHEN 56 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 2147483647
WHEN 52 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 32767
WHEN 48 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 255 END AS 'PERCENTAGEUSED'
FROM SYS.COLUMNS AS C
INNER JOIN SYS.TABLES AS T ON T.[OBJECT_ID] = C.[OBJECT_ID] AND C.IS_IDENTITY = 1
ORDER BY PERCENTAGEUSED DESC

How to use Output Clause?

It returns information from each row affected by any DML operation [INSERT/UPDATE/DELETE], it means you can use it with only DML statements. These results can be retuned to the processing application as confirmation message or archiving purpose, and they can be inserted into a physical or temporary or memory table.

You can get all columns [*] or you can specify the columns to be returned by this clause.

We have two column prefixes DELETED and INSERTED. DELETED returns deleted rows by UPDATE and DELETE operations, while INSERTED returns newly inserted values/rows by INSERT or UPDATE statement.

Syntax:
OUTPUT [column list to be returned] INTO [table/table variable/temporary table] [(Column list)]

Note: You can use OUTPUT clause without INTO clause, just to return data to the processing application, if you want to save/insert affected data into any historical or archival table then you have to use INTO clause.

Example:
-- Create Temporary Table
Create Table #Table1
(
ID int
,val1 int
,val2 int
)

-- Create history table
Create Table #Actions_Performed_ON_Table1
(
ID int
,val1 int
,val2 int
,Actions varchar(20)
,Date DateTime
)

-- INSERT Dummy Data
BEGIN
Declare @ID int, @val1 int, @val2 int
SELECT @ID=isnull(@ID,0)+1, @Val1=isnull(@Val1,0)+1, @Val2=isnull(@val2,0)+1
INSERT into #Table1
Select @ID+ID, @val1+val1, @val2+val2 From #Table1
Go 5
END

-- Using output clause with INSERT Statement to capture new inserted values
INSERT into #table1(ID,val1,val2)
Output INSERTED.ID, INSERTED.val1, INSERTED.val2, 'INSERT', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
values(10,10,10)

Select * From #table1 Where Id = 10
Select * From #Actions_Performed_ON_Table1

-- Using output clause with UPDATE Statement to capture deleted data
Update t Set Id = ID + 1, val1 = Val1 + 1, val2 = val2 + 1
Output DELETED.ID, DELETED.val1, DELETED.val2, 'UPDATE', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
From #table1 t
Where Id=10

Select * From #table1 Where Id = 11
Select * From #Actions_Performed_ON_Table1

-- Using output clause with UPDATE Statement to capture new inserted data
Update t Set Id = ID - 1, val1 = Val1 - 1, val2 = val2 - 1
Output INSERTED.ID, INSERTED.val1, INSERTED.val2, 'UPDATE', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
From #table1 t
Where Id=11

Select * From #table1 Where Id = 10
Select * From #Actions_Performed_ON_Table1

-- Using output clause with Delete Statement to capture deleted data
Delete From #table1
Output DELETED.ID, DELETED.val1, DELETED.val2, 'DELETE', GETDATE()
into #Actions_Performed_ON_Table1(ID,val1,val2,actions,date)
Where Id=10

Select * From #table1 Where Id = 10
Select * From #Actions_Performed_ON_Table1

-- Drop temporary tables
Drop Table #table1
Drop Table #Actions_Performed_ON_Table1