Modify Data by Using INSERT, UPDATE, and DELETE Statements

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement
What I see:
·         OUTPUT statement
OUTPUT Statement
                The OUTPUT clause can be used to pipe affected data from a corresponding DML statement (INSERT, UPDATE, DELETE) or MERGE.  Through the use of this clause, the data can be saved to a table (user table, table var, temp table, etc.) or output to the host.  The below example shows two of the many ways you can utilize the OUTPUT clause:
use MeasureMySkills;
go
if exists
(
       select *
       from sys.tables
       where name = ‘TestDataTable’
)
       drop table TestDataTable;
go
create table TestDataTable
(
       id int identity(1, 1) not null,
       SomeString nvarchar(128) not null,
       AnotherInt int not null
);
go
— show the output of the inserted data to the client
insert into TestDataTable(SomeString, AnotherInt)
output inserted.id, inserted.SomeString, inserted.AnotherInt
values
       (‘hello’, 34),
       (‘goodbye’, 49),
       (‘hola’, 60),
       (‘adios’, 78);
— create temp table to hold deleted data
create table #DeletedData
(
       id int not null,
       SomeString nvarchar(128) not null,
       AnotherInt int not null
);
go
— output the deleted data to the temp table
delete from TestDataTable
output deleted.*
into #DeletedData
where AnotherInt in (34, 49);
select *
from #DeletedData;
References
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Create and Alter Stored Procedures

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of stored procedure results; create a stored procedure for data access layer; program stored procedures, triggers, and functions with T-SQL
What I see:
·         stored procedure results
·         create stored procedures
Stored Procedure Results
                I go into quite a bit detail about the difference types of stored procedure results, and returning data to the calling code in this SQL Salt blog post.  In summary, stored procedures have a few ways of returning data, but often times there are different requirements to use each method, such as returning a result set, or returning execution status (success or failure).  To see examples and a fuller explanation, read the linked blog post.
Create Stored Procedures
                Again, this was covered in the same SQL Salt blog post as above.  Not much to it, but there are a lot of “gotchas” that could arise.  Read the blog post, but also see BOL for corner-cases that aren’t always widely known.
References
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Implement Data Types

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use which data type for columns
What I see:
·         appropriate data types
·         NEWID() vs. NEWSEQUENTIALID()
Appropriate Data Types
                This is one of those can’t-really-explain-it-in-a-blog-post topics, but I think what Microsoft is getting at here is that you should know the limitations (for data storage as well as storage consumption) of the many data types that SQL Server uses.  For instance, know the difference (precision, storage) between datetime and datetime2, and when to use one or the other.  See BOL for a refresher on the data types.
NEWID() vs. NEWSEQUENTIALID()
                I wrote a blog post on this exact topic.  For extensive information, review that.  But in summary, NEWID() does not ensure a sequentially generated GUID, and that is a bad candidate for a clustered index key.  This could lead to high amounts of page splits and index fragmentation.  NEWSEQUENTIALID() solves that problem by ensuring sequential identifiers when called.
References
·         BOL reference on Data Types
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Implement Sub-Queries

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement
What I see:
·         pivot and unpivot
·         APPLY operator
·         CTEs
PIVOT and UNPIVOT
                In my opinion the best resource for learning and reviewing PIVOT and UNPIVOT is BOL.  They have written a great explanation with very helpful examples.  Please see this reference for further reading.
APPLY Operator
                The APPLY operator is used with a table-valued function to pass parameters to the TVF and these are then “applied” to the result set.  Below shows an example of this:
use MeasureMySkills;
go
create table NumbersTable
(
       id int identity(1, 1) not null,
       MyNumber int not null
);
go
insert into NumbersTable(MyNumber)
values
       (3),
       (7),
       (12),
       (5),
       (93),
       (132);
go
create function AddMore
(
       @original_num int,
       @add_by int
)
returns table
as
       return
       (
              select
                     @original_num + @add_by as NewNum
       )
go
select
       nt.id,
       nt.MyNumber,
       ao.NewNum
from NumbersTable nt
cross apply AddMore(nt.MyNumber, 27) ao;
As you can see in the above example, by using the CROSS APPLY clause with the AddMore()TVF, the result set now contains the additional column NewNum with the calculated values from the function.
CTEs
                A Common Table Expression (CTE) is a temporary result set that has the scope of and can be used for a single, subsequent DML statement.  Its definition starts out with the WITH keyword.  It is commonplace to put a semi-colon in front of the WITH, as SQL Server needs to know it’s not part of a previous statement.
use AdventureWorks2012;
go
;with PersonCTE as
(
       select
              BusinessEntityID,
              FirstName,
              LastName,
              EmailPromotion
       from Person.Person
)
select
       EmailPromotion,
       count(*) as EmailPromoCount
from PersonCTE
group by EmailPromotion;
References
·         BOL reference on Using APPLY
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Query Data by Using SELECT Statements

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new code items such as synonyms and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; CASE versus ISNULL versus COALESCE
What I see:
·         ranking functions
·         except and intersect
·         data access technologies
·         CASE vs. ISNULL vs. COALESCE
Ranking Functions
                There are four ranking functions available in SQL Server:  RANK, DENSE_RANK, ROW_NUMBER, and NTILE.
As per BOL, the RANK function “returns the rank of each row within the partition of a result”:
use MeasureMySkills;
go
create table RankingTable
(
       id int identity(1, 1) not null,
       IntValue int not null
);
go
insert into RankingTable(IntValue)
values
       (16),
       (17),
       (17),
       (19),
       (26),
       (25),
       (26),
       (26),
       (30);
select
       id,
       IntValue,
       rank() over
       (
              order by IntValue
       ) as Rank
from RankingTable;
As you can see from the above result, ties are the same rank but then subsequent rank values reflect the gap caused by the ties.  If you want to fill those gaps (i.e. have a “dense” ranking), use the DENSE_RANK function:
select
       id,
       IntValue,
       dense_rank() over
       (
              order by IntValue
       ) as DenseRank
from RankingTable;
Another common ranking function is ROW_NUMBER.  This ranking function will assign a sequential, non-duplicated “row number” to the result set (very handy for deleting duplicate data through the use of the windowing PARTITION BY clause):
select
       id,
       IntValue,
       row_number() over
       (
              order by IntValue
       ) as RowNumber
from RankingTable;
And the last ranking function is NTILE.  This ranking function breaks the windowed data into the specified number of groups:
select
       id,
       IntValue,
       ntile(3) over
       (
              order by IntValue
       ) as NTile
from RankingTable;
EXCEPT and INTERSECT
                These two operators return the same distinct rows from two queries (INTERSECT) or missing distinct rows from first query that aren’t in the second query (EXCEPT).  Below shows examples of this output:
create table IntersectExceptTbl1
(
       MyInt int not null,
       MyString nvarchar(128) not null
);
go
create table IntersectExceptTbl2
(
       MyInt int not null,
       MyString nvarchar(128) not null
);
go
insert into IntersectExceptTbl1(MyInt, MyString)
values
       (1,‘a’),
       (2,‘b’),
       (3,‘c’),
       (4,‘d’),
       (5,‘e’);
insert into IntersectExceptTbl2(MyInt, MyString)
values
       (1,‘a’),
       (6,‘b’),
       (3,‘c’),
       (4,‘f’),
       (5,‘e’);
select *
from IntersectExceptTbl1
intersect
select *
from IntersectExceptTbl2;
/* RESULT SET (INTERSECT)
MyInt  MyString
1             a
3             c
5             e             */
select *
from IntersectExceptTbl1
except
select *
from IntersectExceptTbl2;
/* RESULT SET (EXCEPT)
MyInt  MyString
2             b
4             d             */
Data Access Technologies
                For more information, please see this MSDN reference on the Data Access Technologies Road Map.  It is worth familiarizing yourself with the most commonly-used methods.
CASE vs. ISNULL vs. COALESCE
                Choosing between CASE, ISNULL, and COALESCE just comes down to what’s the best tool for the given task.  CASE is typically used for relatively complex conditional logic that COALESCE can’t encompass (for instance, not driven by NULL).  COALESCE is really just a restricted and abbreviated CASE expression.  ISNULL is simply used to return either the first parameter’s value, or if that’s NULL then it’ll return the second.  This is great for ensuring that NULL isn’t passed to aggregating functions, or any other time that NULL is less than desired.
References
·         BOL reference on RANK
·         BOL reference on DENSE_RANK
·         BOL reference on ROW_NUMBER
·         BOL reference on NTILE
·         BOL reference on ISNULL
·         BOL reference on CASE
·         BOL reference on COALESCE
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Create and Alter DML Triggers

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers
What I see:
·         inserted and deleted tables
·         types of triggers
·         performance implications of triggers
inserted and deleted Dynamic Tables
                The inserted and deleted dynamic tables contain the respective data during the duration of the DML trigger.  This data can be used to log data changes, check referential integrity, or find differences in modified data.  Below shows an example using these tables:
use MeasureMySkills;
go
create table TestTriggerDataTable
(
       id int identity(1, 1) not null,
       SomeInt int not null,
       SomeString nvarchar(128) not null
);
go
create table OldData
(
       id int not null,
       SomeInt int not null,
       SomeString nvarchar(128) not null
);
go
create table NewData
(
       id int not null,
       SomeInt int not null,
       SomeString nvarchar(128) not null
);
go
create trigger UpdateTestTriggerDataTable
on TestTriggerDataTable
after update
as
       insert into OldData
       select *
       from deleted;
       insert into NewData
       select *
       from inserted;
go
insert into TestTriggerDataTable
values
       (5,‘hello’),
       (10,‘good bye’);
update TestTriggerDataTable
set
       SomeInt = SomeInt + 10,
       SomeString = ‘My Text: ‘ + SomeString;
select *
from OldData;
select *
from NewData;
Types of Triggers
                There are two types of DML triggers:  AFTER and INSTEAD OF.  The difference is that an AFTER trigger (equivalent with the FOR keyword) gets fired after the triggering command has been executed.  An INSTEAD OF trigger overrides the action of the command.  The above example shows the use of an AFTER trigger, and below shows how an INSTEAD OF trigger works:
create table InsteadOfTestData
(
       AnotherInt int not null,
       AnotherString nvarchar(128) not null
);
go
create trigger InsteadOfTestDataTrigger
on InsteadOfTestData
instead of insert
as
       insert into InsteadOfTestData
       (
              AnotherInt,
              AnotherString
       )
       select
              AnotherInt + 100,
              ‘Instead of: ‘ + AnotherString
       from inserted;
go
insert into InsteadOfTestData
values(15, ‘hi’);
select *
from InsteadOfTestData;
Performance Implications of Triggers
                There are many considerations to take into account prior to just creating a large amount of triggers on your tables.  First and foremost is the performance impact:  Triggers are notorious for being the “silent killers” of performance, as they execute for all statements they are defined for.  Be wary on what tables you create them on, and if they are truly necessary.
References
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.

Design Views

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: ensure code non regression by keeping consistent signature for procedure, views, and function (interfaces); security implications
What I see:
·         code non-regression
·         security through views
Code Non-Regression
                The basic gist of non-regression is simple:  don’t make your data calls dependent on the underlying table schemas.  In other words, the layer of views/procedures/functions should break the dependence on the tables.  Underlying tables can change without breaking the code that calls them, and this is through the use of the middle data layer (the “API”) that can be altered to work with the underlying data, yet the call will remain the same.
Security Through Views
                Views are a way to implement both column and row security.  A user can be granted permissions on the view, but denied on the base table.  The below example shows how we can restrict a user (or group of users) from accessing specific table rows that we don’t want them to.  This is the heart of view security:
use MeasureMySkills;
go
create table VerySecureTable
(
       id int identity(1, 1) not null,
       SecurityLevel int not null
              default 1, — default to not very secure
       SecurityString nvarchar(24) not null
              default ‘Some Secure String’
);
go
insert into VerySecureTable
(
       SecurityLevel,
       SecurityString
)
values
(
       1,
       default
);
go 10
insert into VerySecureTable
(
       SecurityLevel,
       SecurityString
)
values
(
       2,
       default
);
go 10
insert into VerySecureTable
(
       SecurityLevel,
       SecurityString
)
values
(
       3,
       default
);
go 10
— implement row-based security through a view
create view NotSecureData
as
       select *
       from VerySecureTable
       where SecurityLevel <= 2;
go
— test out our role security
create login NotSecureLogin
with
       password = ‘password’,
       check_policy = off;
go
use MeasureMySkills;
go
create user NotSecureUser
for login NotSecureLogin;
go
grant select
on object::NotSecureData
to NotSecureUser;
go
— this won’t work (no permissions)
execute as user = ‘NotSecureUser’;
go
select *
from VerySecureTable;
revert;
go
— this will work (row-based security)
execute as user = ‘NotSecureUser’;
go
select *
from NotSecureData;
revert;
go
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.