I am going continuing my short articles or tidbits on Transaction SQL logical functions.

These functions were introduced with the SQL Server 2012 release. Both functions have their origins from Visual Basic for Applications (VBA) framework.

I think of these function as decision trees. Especially when they are nested [...]]]>

I am going continuing my short articles or tidbits on Transaction SQL logical functions.

These functions were introduced with the SQL Server 2012 release. Both functions have their origins from Visual Basic for Applications (VBA) framework.

I think of these function as decision trees. Especially when they are nested to several levels.

If you have a kid like I do, you might find the decision tree for dropped food funny. Please see image to left.

I will be exploring the IIF function today.

The example below uses the RAND function, to set two variables with random numbers between 1 and 10. The immediate if function takes an expression to evaluate and two values. It returns the first value if the expression is TRUE; otherwise, it returns the second value.

--
--  Immediate If (IIF) Function
--

DECLARE @a int = CAST((RAND() * 10) AS INT) + 1;
DECLARE @b int = CAST((RAND() * 10) AS INT) + 1;
SELECT @a as var_a, @b as var_b, IIF ( @a > @b, 'TRUE', 'FALSE' ) AS a_gt_b;
GO 5

The output of each of the five batches is listed below.

output: 

Beginning execution loop

var_a       var_b       a_gt_b
----------- ----------- ------
9           2           TRUE

var_a       var_b       a_gt_b
----------- ----------- ------
7           2           TRUE

var_a       var_b       a_gt_b
----------- ----------- ------
6           1           TRUE

var_a       var_b       a_gt_b
----------- ----------- ------
3           1           TRUE

var_a       var_b       a_gt_b
----------- ----------- ------
5           6           FALSE

Batch execution completed 5 times.

Please note the number five after the key word GO. This tells the transaction processor to run the commands (batch) five times.

In a nutshell, this function can be replaced by the IF … ELSE control flow language. This syntax has been part of the T-SQL language for a long time.

I am going write a couple of tidbits on Transaction SQL logical functions.

These functions were introduced with the SQL Server 2012 release. Both functions have their origins from Visual Basic for Applications (VBA) framework.

I think of these function as decision trees. Especially when they are nested to [...]]]>

I am going write a couple of tidbits on Transaction SQL logical functions.

These functions were introduced with the SQL Server 2012 release. Both functions have their origins from Visual Basic for Applications (VBA) framework.

I think of these function as decision trees. Especially when they are nested to several levels.

If you have a kid like I do, you might find the decision tree for dropped food funny. Please see image to left.

I will be exploring the CHOOSE function today.

If you grew up like I did, you probably saw black and white re-runs of the Three Stooges on television.

The example below uses the RAND function, to get a random index value. The index value is used with the CHOOSE() function to return the culprit who broke our chair.

Many chairs were broken in the Three Stooges slapstick skits.

--
--  Choose Function
--

-- Who broke the chair?
declare @idx int = CAST((RAND() * 3) AS INT) + 1;
select @idx as idx, CHOOSE(@idx, 'Moe', 'Larry', 'Curley') as Culprit
go 5

The output of each of the five batches is listed below.

output: 

Beginning execution loop

idx         Culprit
----------- -------
1           Moe

idx         Culprit
----------- -------
2           Larry

idx         Culprit
----------- -------
3           Curley

idx         Culprit
----------- -------
2           Larry

idx         Culprit
----------- -------
3           Curley


Batch execution completed 5 times.

The function takes an index value N and a list of items. It returns the Nth item in the list.

Please note the number five after the key word GO. This tells the transaction processor to run the commands (batch) five times.

Next time, I will be talking about the immediate if, IIF function.

I had the opportunity last week to attend the SQL Server Performance Tuning Workshop by Pragmatic Works Learning Center.

I definitely recommend this workshop.

This is the second class I have taken at the Microsoft New England Research and Development Center located in Cambridge, MA.

It is conveniently located right [...]]]>

I had the opportunity last week to attend the SQL Server Performance Tuning Workshop by Pragmatic Works Learning Center.

I definitely recommend this workshop.

This is the second class I have taken at the Microsoft New England Research and Development Center located in Cambridge, MA.

It is conveniently located right off the Kendall station stop on the Massachusetts Transit Bay Authority (MBTA) Red Line.

The main instructor for the class was Bradley Ball aka SQLBalls. He was a very likable speaker possessing good presentation skills as well as a wealth of knowledge to draw upon.

The following topics were covered during the two day workshop.

1. Introduction: ‘Houston, do you have a problem?’
2. Hardware Environment and Configuration
3. Software Environment and Configuration
4. Understanding Queries in SQL Server
5. Index Tuning
6. Locking / Blocking / Deadlocks / Latches
7. Waits and Queues Methodology
8. Baselines
9. Setting Up Alerts
10. Extended Events and other Monitoring Tools
11. Making it real

Other Pragmatic employees at the event were Kim Hathaway (training) and Nick Slevoski (sales).

Kim presented several topics when Bradley needed a break and Nick made sure the event went on without a hitch.

The food and refreshments were plenty and good. Mike Hillwig, a Microsoft SQL MVP, also attended the class.

In short, if you have the time and money, please take the workshop. You might even learn something like this old IT guy did.

I am thrilled about presenting to the Southern New England SQL Server User Group (SNESSUG) on June 12, 2013.

I always love the home team advantage by knowing the audience very well. It makes for a relaxing environment to teach and attendees to learn.

Here is the gritty details of the presentation [...]]]>

I am thrilled about presenting to the Southern New England SQL Server User Group (SNESSUG) on June 12, 2013.

I always love the home team advantage by knowing the audience very well. It makes for a relaxing environment to teach and attendees to learn.

Here is the gritty details of the presentation that I will be exploring during the 90 minute talk.

Topic:

Effective Data Warehouse Storage Patterns

Abstract:

Many companies start off with a simple data mart for reporting. As the company grows, users become dependent on the data mart for monitoring and making decisions on Key Performance Indicators (KPI).

Unexpected information growth in your data mart may lead to a performance impacted reporting system. In short, your users will be lining up at your cube for their daily reports.

How do you reduce the size of your data mart and speed up data retrieval?

This presentation will review the following techniques to fix your woes.

Coverage:

1 – What is horizontal partitioning?
2 – Database sharding for daily information.
3 – Working with files and file groups.
3 – Partitioned views for performance.
4 – Table and Index partitions.
5 – Row Data Compression.
6 – Page Data Compression.
7 – Programming a sliding window.
8 – What are Federations in Azure SQL?

presentation bundle

I am going to bring closure to my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.

I will exploring the operator precedence today. In mathematics and computer programming, the order of [...]]]>

I am going to bring closure to my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.

I will exploring the operator precedence today. In mathematics and computer programming, the order of operations is a set of rules used to clarify which procedures should be performed first in a given mathematical expression.

I do not have enough time in this very short article to go over the eight levels that group and order expression evaluation. However, I want to go over one example in which parentheses can be used to change the order of evaluation.

The first expression evaluates multiplication first since it is considered a higher level than subtraction. The second example changes the order by using parentheses.

--
-- Operator Precedence
--

-- Results in 2
select x = 2*3-2*2;

-- Results in 4
select 2*(3-2)*2 as y;

-- ~ OUTPUT ~
x
-----------
2

y
-----------
4

If your Transaction SQL code is not evaluating the programming expression as you would like, consider using parentheses. Next time I will be talking about logic functions.

I will exploring the Unary Operators today. In mathematics, a unary operation is an [...]]]>
I am going to stick to writing my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.

I will exploring the Unary Operators today. In mathematics, a unary operation is an operation with only one operand.

Microsoft Transaction SQL has three unary operators: positive, negative, and bitwise not. The example script below explores all three operators.

--
--  Unary operators 
--

-- negative
declare @x int = 5;
select -@x as negative;

-- positive
declare @y int = 5;
select +@y as positive;

-- ones complement
declare @z int = 0x7F;
select ~ @z;

-- ~ OUTPUT ~
negative
-----------
-5

positive
-----------
5

ones_complement
---------------
-128

I find the positive unary operator kinda-of useless. It will not take a negative number and make it positive. By default, a non-negative number is positive. I think is part of the language for mathematical completeness.

To complete my exploration of operators, I will be talking about operator precedence next time.

I will exploring the String Operators today. These operators can be broken down into two [...]]]>
I am going to press on with my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.

I will exploring the String Operators today. These operators can be broken down into two categories: concatenation and pattern matching.

Transaction SQL has both a simple and compound version of the concatenation operator. This operator takes two strings and combines them into one. The two examples below return the same output.

--
--  String operators - Concatenation
--

-- Declare variables
declare @front varchar(16) = 'Red ';
declare @back varchar(16) = 'Riding Hood ';
declare @phrase varchar(32) = '';

-- ~ simple concatenation ~
select @phrase = @front + @back;
select @phrase as grimm_tale

-- ~ compound concatenation ~
set @phrase = '';
set @phrase += @front;
set @phrase += @back;
select @phrase as grimm_tale

-- ~ OUTPUT ~
grimm_tale
--------------------------------
Red Riding Hood 

The [] operator matches any single character within the specified range or set that is specified between the brackets. The example below returns all employees with id’s like 22#.

-- String Match - Character patterns
USE AdventureWorks2012;
GO 

-- Employees w/id of 22X - character match [] - 10 rows
SELECT 
    P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate
FROM 
    HumanResources.Employee AS E join Person.Person P
ON 
    E.BusinessEntityID = P.BusinessEntityID
where 
    E.BusinessEntityID like '[2][2][0-9]'
GO

-- ~ OUTPUT ~
BusinessEntityID FirstName                                          LastName                                           Gender BirthDate
---------------- -------------------------------------------------- -------------------------------------------------- ------ ----------
220              Karen                                              Berge                                              F      1970-01-25
221              Chris                                              Norred                                             M      1981-06-26
222              A. Scott                                           Wright                                             M      1962-10-19
223              Sairaj                                             Uddin                                              M      1982-01-22
224              William                                            Vong                                               M      1975-12-08
225              Alan                                               Brewer                                             M      1978-04-30
226              Brian                                              LaMee                                              M      1978-09-12
227              Gary                                               Altman                                             M      1965-03-21
228              Christian                                          Kleinerman                                         M      1970-02-18
229              Lori                                               Penor                                              F      1964-08-31

The _ operator matches any single character in a string comparison operation that involves pattern matching such as LIKE and PATINDEX. We have the same 10 records as output.

-- String Match - Character patterns
USE AdventureWorks2012;
GO 

-- Employees w/id of 22X - character match _ - 10 rows
SELECT 
    P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate
FROM 
    HumanResources.Employee AS E join Person.Person P
ON 
    E.BusinessEntityID = P.BusinessEntityID
where 
    E.BusinessEntityID like '22_'
GO

-- ~ OUTPUT ~
BusinessEntityID FirstName                                          LastName                                           Gender BirthDate
---------------- -------------------------------------------------- -------------------------------------------------- ------ ----------
220              Karen                                              Berge                                              F      1970-01-25
221              Chris                                              Norred                                             M      1981-06-26
222              A. Scott                                           Wright                                             M      1962-10-19
223              Sairaj                                             Uddin                                              M      1982-01-22
224              William                                            Vong                                               M      1975-12-08
225              Alan                                               Brewer                                             M      1978-04-30
226              Brian                                              LaMee                                              M      1978-09-12
227              Gary                                               Altman                                             M      1965-03-21
228              Christian                                          Kleinerman                                         M      1970-02-18
229              Lori                                               Penor                                              F      1964-08-31

The % operator matches any string of zero or more characters. The output of this example adds one more record to the result set.

-- String Match - Character patterns
USE AdventureWorks2012;
GO 

-- Employees w/id of 22X - character match % - 11 rows
SELECT 
    P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate
FROM 
    HumanResources.Employee AS E join Person.Person P
ON 
    E.BusinessEntityID = P.BusinessEntityID
where 
    E.BusinessEntityID like '22%'
GO

-- ~ OUTPUT ~
BusinessEntityID FirstName                                          LastName                                           Gender BirthDate
---------------- -------------------------------------------------- -------------------------------------------------- ------ ----------
22               Sariya                                             Harnpadoungsataya                                  M      1981-06-21
220              Karen                                              Berge                                              F      1970-01-25
221              Chris                                              Norred                                             M      1981-06-26
222              A. Scott                                           Wright                                             M      1962-10-19
223              Sairaj                                             Uddin                                              M      1982-01-22
224              William                                            Vong                                               M      1975-12-08
225              Alan                                               Brewer                                             M      1978-04-30
226              Brian                                              LaMee                                              M      1978-09-12
227              Gary                                               Altman                                             M      1965-03-21
228              Christian                                          Kleinerman                                         M      1970-02-18
229              Lori                                               Penor                                              F      1964-08-31

Last but not least, the ^ operator returns any strings that do not match this character. In the example below, we want to return all employees that have a first name that does not start with J. The result set has 256 matches. Shown below are the first five records.

-- String Match - Character patterns
USE AdventureWorks2012;
GO 

-- Employees with first name does not start with J - 256 row
SELECT 
    P.BusinessEntityID, P.FirstName, P.LastName, E.Gender, E.BirthDate
FROM 
    HumanResources.Employee AS E join Person.Person P
ON 
    E.BusinessEntityID = P.BusinessEntityID
where 
    p.FirstName like '[^J]%'
GO

-- ~ OUTPUT ~
BusinessEntityID FirstName                                          LastName                                           Gender BirthDate
---------------- -------------------------------------------------- -------------------------------------------------- ------ ----------
1                Ken                                                Sánchez                                            M      1963-03-02
2                Terri                                              Duffy                                              F      1965-09-01
3                Roberto                                            Tamburello                                         M      1968-12-13
4                Rob                                                Walters                                            M      1969-01-23
5                Gail                                               Erickson                                           F      1946-10-29

In summary, concatenation operator + was defined as part of the TSQL syntax long before the CONCAT function was ever introduced. You are supplied with 4 pattern matching functions to slice and dice your data. I am sure you will come across string operators in your scripting endeavors.

Later today, I will be researching Unary Operators.

I am going to promote my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.

I will exploring the Set Operators today. A Venn diagram is used to express different operations between [...]]]>

I am going to promote my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.

I will exploring the Set Operators today. A Venn diagram is used to express different operations between sets. The above image represents the four possible operations given two sets, A and B, and three operators. We will get into the operators supported by Microsoft Transaction SQL shortly.

To demonstrate these operators, we need a two temporary tables to query against. The TSQL code below will create temporary tables A and B.

--
-- ~ Setup Play Table ~
--

-- Drop existing table [A]
IF EXISTS 
(
    SELECT * 
    FROM tempdb.sys.objects T 
    WHERE T.TYPE = 'U' AND T.name LIKE '#A%'
) 
DROP TABLE #A;
GO

-- Make temp table [A]
CREATE TABLE #A (NUM INT PRIMARY KEY);
GO

-- Add data to table [A]
INSERT INTO #A VALUES (1), (2), (3), (4), (5), (6); 
GO

-- Drop existing table [B]
IF EXISTS 
(
    SELECT * 
    FROM tempdb.sys.objects T 
    WHERE T.TYPE = 'U' AND T.name LIKE '#B%'
) 
DROP TABLE #B;
GO

-- Make temp table [B]
CREATE TABLE #B (NUM INT PRIMARY KEY);
GO

-- Add data to table [B]
INSERT INTO #B VALUES (5), (6), (7), (8), (9), (10); 
GO

The UNION operator combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. By default, the operator removes any duplicates. Using set notation, the A ∪ B notation is the union of both data sets in the above diagram.

To use this operator, the number and order of the columns have to be the same as well as the data types of the columns must be compatible.

-- ~ UNION ~ no duplicates
SELECT * FROM #A
UNION
SELECT * FROM #B

-- ~ OUTPUT ~
NUM
-----------
1
2
3
4
5
6
7
8
9
10

Using the ALL clause of the UNION operator shows the all rows including the duplicates. Please note that the order of the rows in the result set is not guaranteed. In our example, both values 5 and 6 are duplicated in the output list.

-- ~ UNION ALL ~ show duplicates
SELECT * FROM #A
UNION ALL
SELECT * FROM #B

-- ~ OUTPUT ~
NUM
-----------
1
2
3
4
5
6
5
6
7
8
9
10

The EXCEPT operator returns any distinct values from the left query that are not also found on the right query. Using set notation, A – B is the EXCEPT operation of set A in regards to B. Again, the number and order of the columns have to be the same as well as the data types of the columns must be compatible.

-- ~ EXCEPT ~ ones that show just in A
SELECT * FROM #A
EXCEPT 
SELECT * FROM #B

-- ~ OUTPUT ~
NUM
-----------
1
2
3
4

Since this operator is order specific, the set notion B – A is the EXCEPT operation of set B in regards to A. Please see the image above for a visual representation using set theory.

-- ~ EXCEPT ~ ones that show just in B
SELECT * FROM #B
EXCEPT 
SELECT * FROM #A

-- ~ OUTPUT ~
NUM
-----------
7
8
9
10

Last but not least, the INTERSECT operator returns any distinct values in common from both the left and right queries.

Using set notation, A ∩ B is the INTERSECT operation between sets A and B. As usual, the number and order of the columns have to be the same as well as the data types of the columns must be compatible.

-- ~ INTERSECT ~ ones that show in both A & B
SELECT * FROM #A
INTERSECT
SELECT * FROM #B

-- ~ OUTPUT ~
NUM
-----------
5
6

To wrap-up this article, set operators are a great way to combine two similar query outputs using the UNION, EXCEPTION or INTERSECTION as combinatorial rules. Next time, I will be researching the String Operators.

I am going to make headway on the series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.

I will exploring the Logical Operators today. In short, these operators return either a boolean [...]]]>

I am going to make headway on the series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.

I will exploring the Logical Operators today. In short, these operators return either a boolean TRUE or FALSE.

To demonstrate many of these operators, we need a temporary table to query against. The Transaction SQL code below creates a table that contains the first eight prime numbers.

--
-- ~ Setup Play Table ~
--

-- Drop existing table
IF EXISTS 
(
    SELECT * 
    FROM tempdb.sys.objects T 
    WHERE T.TYPE = 'U' AND T.name LIKE '#PRIMES%'
) 
DROP TABLE #PRIMES;
GO

-- Make temp table
CREATE TABLE #PRIMES (NUM INT PRIMARY KEY);
GO

-- Add data to table
INSERT INTO #PRIMES VALUES 
    (2), (3), (5), (7),
    (11), (13), (17), (19);

-- Show the data
SELECT * FROM #PRIMES;

The output of the SELECT statement showing the prime numbers.

-- ~ OUTPUT ~
NUM
-----------
2
3
5
7
11
13
17
19

The SOME operator compares a scalar value with a single column set of values. It returns TRUE when one or more conditions are satisfied.

-- ~ SOME OPERATOR ~
PRINT '13 LESS THAN [SOME] OF THE FIRST 8 PRIMES?' 
IF 13 < SOME (SELECT NUM FROM #PRIMES)
    PRINT 'TRUE' 
ELSE
    PRINT 'FALSE' ;
PRINT '';

-- ~ OUTPUT ~
13 LESS THAN [SOME] OF THE FIRST 8 PRIMES?
TRUE

The ANY operator is equivalent to the SOME operator. It returns FALSE when all of the conditions are not satisfied.

-- ~ ANY OPERATOR ~
PRINT '23 LESS THAN [ANY] OF THE FIRST 8 PRIMES?' 
IF 23 < ANY (SELECT NUM FROM #PRIMES)
    PRINT 'TRUE' 
ELSE
    PRINT 'FALSE' ;
PRINT '';

-- ~ OUTPUT ~
23 LESS THAN [ANY] OF THE FIRST 8 PRIMES?
FALSE

The ALL operator compares a scalar value with a single column set of values. It returns TRUE when all of the conditions are satisfied.

-- ~ ALL OPERATOR ~
PRINT '3 LESS THAN [ALL] OF THE FIRST 8 PRIMES?' 
IF 3 < ALL (SELECT NUM FROM #PRIMES)
    PRINT 'TRUE' 
ELSE
    PRINT 'FALSE' ;
PRINT '';

-- ~ OUTPUT ~
3 LESS THAN [ALL] OF THE FIRST 8 PRIMES?
FALSE

The BETWEEN operator compares a scalar against a range of values. It returns TRUE when the scalar is greater than or equal to the lower limit and less than or equal to the upper limit.

-- ~ BETWEEN OPERATOR ~
PRINT 'AVERAGE OF FIRST 8 PRIMES IS [BETWEEN] 8 AND 10' 
IF (SELECT AVG(NUM) FROM #PRIMES) BETWEEN 8 AND 10 
    PRINT 'TRUE' 
ELSE
    PRINT 'FALSE' ;
PRINT '';

-- ~ OUTPUT ~
AVERAGE OF FIRST 8 PRIMES IS [BETWEEN] 8 AND 10
TRUE

The logical AND operator combines two BOOLEAN expressions. It returns TRUE if both conditions are true; otherwise, it returns false. The example below uses simple integer expressions to demonstrate this operator.

-- ~ AND OPERATOR ~
PRINT 'TWO GREATER THAN ONE [AND] FOUR GREATER THAN THREE' 
IF (2 > 1) AND (4 > 3)
    PRINT 'TRUE' 
ELSE
    PRINT 'FALSE' ;
PRINT '';

-- ~ OUTPUT ~
TWO GREATER THAN ONE [AND] FOUR GREATER THAN THREE
TRUE

The logical OR operator combines two BOOLEAN expressions. It returns TRUE if any conditions are true; otherwise, it returns false. The example below uses simple integer expressions to demonstrate this operator.

-- ~ OR OPERATOR ~
PRINT 'TWO GREATER THAN ONE [OR] FOUR LESS THAN THREE' 
IF (2 > 1) OR (4 < 3)
    PRINT 'TRUE' 
ELSE
    PRINT 'FALSE' ;
PRINT '';

-- ~ OUTPUT ~
TWO GREATER THAN ONE [OR] FOUR LESS THAN THREE
TRUE

The LIKE operator determines if a character string matches a specific pattern. The example below queries the sys.databases table and returns the row for the master database.

-- ~ LIKE OPERATOR ~
SELECT name as dbms_name, database_id as dbms_id 
FROM master.sys.databases
WHERE name LIKE 'master%';

-- ~ OUTPUT ~
dbms_name  dbms_id
---------- -----------
master     1

The IN operator ascertains if a value matches any elements in the sub-query or list of elements. The example below queries the sys.databases table and returns a row for each system database.

-- ~ IN OPERATOR ~
SELECT name as dbms_name, database_id as dbms_id 
FROM master.sys.databases
WHERE name IN ('tempdb', 'model', 'master', 'msdb')
ORDER BY database_id;

-- ~ OUTPUT ~
dbms_name  dbms_id
---------- -----------
master     1
tempdb     2
model      3
msdb       4

The NOT operator negates the BOOLEAN expression. The example below queries the sys.databases table and returns the first 10 databases by id. It removes the master database by using the NOT LIKE clause.

-- ~ NOT OPERATOR ~
SELECT TOP 10 name as dbms_name, database_id as dbms_id 
FROM master.sys.databases
WHERE name NOT LIKE 'master%' 
ORDER BY database_id;

-- ~ OUTPUT ~
dbms_name            dbms_id
-------------------- -----------
tempdb               2
model                3
msdb                 4
ReportServer         5
ReportServerTempDB   6
MATH                 7
AdventureWorksDW2012 8
AUTOS                9
AdventureWorks2012   10
BASIC                11

Last but not least, the EXISTS operator finds out if the sub-query contains at least one row. The following example checks to see if the Adventure Works database for 2012 is installed.

-- ~ EXISTS OPERATOR ~
IF EXISTS (SELECT 1 FROM master.sys.databases WHERE name = 'AdventureWorks2012')
    PRINT 'TRUE - ADVENTURE WORKS IS INSTALLED' 
ELSE
    PRINT 'FALSE - ADVENTURE WORKS IS INSTALLED' ;
PRINT '';

-- ~ OUTPUT ~
TRUE - ADVENTURE WORKS IS INSTALLED

Next time, I will be researching the Set Operators.

I am going to forge ahead with my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.

I will exploring the Compound Operators today. These operators are a short hand for taking a [...]]]>

I am going to forge ahead with my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.

I will exploring the Compound Operators today. These operators are a short hand for taking a variable @V, applying some operator O and storing the result as variable @V. A long way to write out adding 2 to variable @V is the expression @V = @V + 2 while the short way is the expression @V += 2.

T-SQL supports the following five compound math operations: ADDITION, SUBTRACTION, MULTIPLICATION, DIVISION, and MODULUS.

The examples below performs a simple calculation using each operator.

--
--  Compound operators - Math (+-*/%)
--

declare @x tinyint = 0;

-- Addition
select @x += 4;
select @x as comp_addition

-- Subtraction
select @x -= 1;
select @x as comp_subtraction

-- Multiplication
select @x *= 4;
select @x as comp_multiplication

-- Division
select @x /= 2;
select @x as comp_division

-- Modulo
select @x %= 5;
select @x as comp_modulo

The output of each calculation is listed below.

output: 

comp_addition
-------------
4

comp_subtraction
----------------
3

comp_multiplication
-------------------
12

comp_division
-------------
6

comp_modulo
-----------
1

In addition to math, T-SQL supports the following three compound bitwise operations: AND, OR and XOR.

The examples below performs a simple calculation using each operator.

--
--  Compound operators - Bitwise (+-*/%)
--

-- Operator & (AND)
declare @a tinyint = 0xFF;
set @a &= 0x02;
print 'low_nibble_bit_two = 0x' + format(@a, 'X2');
print ' '; 

-- Operator | (OR)
declare @o tinyint = 0x00;
set @o |= 0x40;
print 'high_nibble_bit_three = 0x' + format(@o, 'X2');
print ' '; 

-- Operator | (XOR)
declare @x tinyint = 0xAA;
set @x ^= 0x55;
print 'all bits are on = 0x' + format(@x, 'X2');
print ' '; 

The output of each calculation is listed below.

output: 

low_nibble_bit_two = 0x02
 
high_nibble_bit_three = 0x40
 
all bits are on = 0xFF

Please note that I have used both the SELECT and SET statements to perform assignments using compound operators. This means that a variable can be used to get a summation from a query.

For instance, let’s believe the chief financial officer of Adventure Works wanted us to get the hourly run rate for the company. We want to only consider active employees, people who do not have a termination date. The solution below uses a common table expression to find active employees.

The @total variable is used with the addition compound operator to calculate a total hourly cost.

--
--  Calculate hourly run rate
--

use [AdventureWorks2012];
declare @total as money = 0;
;
with cte_Active_Employees
as
(
select distinct
 BusinessEntityID
from 
  [HumanResources].[EmployeeDepartmentHistory]
where 
  EndDate is null
)
select 
  @total += Rate 
from 
  [HumanResources].[EmployeePayHistory] as h 
inner join 
  cte_Active_Employees as a
on 
  h.BusinessEntityID = a.BusinessEntityID
print 'Daily run rate is ' + format(@total, 'C') + ' per hour.';

-- 
-- output: 
--

Daily run rate is $5,611.78 per hour.

Next time, I will be exploring the Logical Operators.