Monday, June 18, 2012

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.

Friday, June 15, 2012

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.

Thursday, June 14, 2012

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.