Category Archives: SQL Server

I used PowerShell !

Our production environment consists of   SQL Server 2008 R2 with several databases across multiple SQL Sever instances. We follow a somewhat old school approach to deployment, wherein once a project is past QA and in the Stage/UAT environment, we no longer create and deploy builds in a cumulative fashion. When bugs are found in Stage/UAT environment, the Builds to fix those bugs (iterative cycle) in Stage/UAT are preserved and deployed sequentially , as-is in Production as well. If we needed 10 iterations (hence 10 builds) to fix a bug in Stage/UAT, we will deploy the same 10 builds to Production sequentially !

The Problem:

This tediously meticulous approach to deployment guarantees the repetition of  the same successful path to deployment in production (in theory), that was taken in Stage/UAT environment. It leads to same quality of code being deployed to production, as was deployed to Stage/UAT and hence is expected to produced the same results (in theory). However, when the number of iterations needed to fix all bugs in Stage/UAT is large enough that we routinely end up with builds running into double digits. Efficiently and accurately deploying 10 plus builds to production, within a relatively short deployment window was starting to become a challenge for us (Our DBA is not only expected to log deployment results, but proceed with next script ONLY upon success of previous script).  While we were not ready to fully automate the execution of our deployment scripts via a batch run , we needed a command line method for deploying our SQL scripts relatively fast , where the execution messages are not only captured in a log file, but also displayed on the screen. This would  not only let our DBA identify if a script’s execution encountered any errors, without having to open up the log file, but also help execute the deployment faster than using a fully manual, SSMS based deployment approach.

The solution:

Our first attempt was using  SQLCMD to achieve a fair degree of automation and speed up the deployment time, by reducing manual work. I have a simple test script here with a few PRINT statements , one simple SELECT statement that executes successfully and another simple SELECT statement that fails due to non-existent table (to simulate a script failure scenario). Do take note that my script uses SQLCMD variable “:on error exit” ,which causes the batch to stop execution upon encountering an error . I have named the script quite creatively as “test.sql”.

USE Demo;
GO
:on error exit
PRINT N'Deploying Demo Script...';
GO
SELECT COUNT(*) FROM [dbo].[demo_order];
GO
PRINT N'Running query against non-existing table...';
GO
SELECT COUNT(*) FROM [dbo].[does_not_exist];
GO
PRINT N'This PRINT should not run as previous query errors and batch should exit...';
GO

When run in SSMS, this script produces the following output, and exits the batch upon encountering the first error as expected ;

Deploying Demo Script...
(1 row(s) affected)
Running query against non-existing table...
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.does_not_exist'.
** An error was encountered during execution of batch. Exiting.

The quickest way to automate the execution of my test script, is to use SQLCMD via the command line. Note the “-b” option used in my SQLCMD command string, which forces the termination of batch upon encountering errors. This is functionally similar to using “:on error exit” SQLCMD variable within the script itself. Here is the simple command line string ;

sqlcmd -S  WKS18176\SANIL_2012 -d Demo -b -i test.sql  -o test.sql.log.txt

When this SQLCMD command string is executed in the command prompt, it created the log file documenting the error message and the fact that batch was terminated .However, note that the command prompt screen shows no indication of success or failure of the script.

sqlcmd

Unless our DBA opens up the log file “test.sql.log.txt” for review, he cannot see the execution and error messages as seen below. (I could use the “type” command on the next line here but we prefer to have a single line command )

Changed database context to 'Demo'.
Deploying Demo Script...
-----------
 12
(1 rows affected)
Running query against non-existing table...
Msg 208, Level 16, State 1, Server WKS18176\SANIL_2012, Line 2
Invalid object name 'dbo.does_not_exist'.

This is where PowerShell came to our rescue. With minor modification to my SQLCMD command itself, and adding a PowerShell cmd-let, we were  able to not only log the execution messages into a file, but also display them on the PowerShell screen, without losing any functionality related to exiting the batch upon error.

sqlcmd -S  WKS18176\SANIL_2012 -d Demo -b -i test.sql  | Tee-Object -file test.sql.log.txt

Here is a screenshot of executing my test script via PowerShell.

powershell

This was my first time using PowerShell and I am impressed how quickly we were able to learn and use it. Over the next few weeks, I am going to take up exploring PowerShell and learn how I can apply it to ease some more of our automation pain points !

References:

I am speaking at the PASS Summit 2013 !

PASS_2013_SpeakingButton_250x250

I am honored and excited to be selected to speak at the PASS Summit 2013 in Charlotte, NC – Oct 15th through 18th ! I will be talking about “Database Unit testing” with Visual Studio.  This session highlights the importance of Unit Testing in the development life cycle of a Database application. Unit testing a Database application is definitely a lot more challenging than unit testing a VB.NET or C# application . Creating a consistent database test environment not only involves database code, but also the data itself. More often than not, due to the time and effort involved in creating a consistent database test environment, Unit Testing database code is rarely given a though upfront during the development. This usually leads to late discovery of bugs, that are expensive to fix as the development life cycle progresses. Visual Studio, with Database projects and more recently with SQL Server Data tools (SSDT), had made unit testing fairly  easy to implement. During the course of this session, we will touch base with the the concepts of Unit Testing and demonstrate the implementation of Unit tests for a Database project and an SSDT project in VSTS 2010 and VSTS 2012 respectively. If you have already implemented Database Unit test projects in VSTS 2010, we will also go through a demo for upgrading them to SSDT.

I have presented this session at several SQL Saturday events, User group meetings and regional conferences, and I am looking forward to bring to this session to the PASS Summit. I look forward to seeing you all at the Summit in October !

SQL Saturday # 236, St.Louis – A Review

STLSQLSATTeamPhoto

We had great SQL Saturday #236, the second annual St.Louis SQL Saturday event, on Aug 3. As always, PASS plays a big role helping make any SQL Saturday event successful by providing the necessary infrastructure to run the event.

We moved the 2013 event to a different facility this year, the Wool Center at SLU. SLU provided the venue for this year’s event, as well as a few of their staff members  to help us out on the day of the event. we could not have asked for any better. We definitely plan to continue host future St.Louis SQL Saturdays at SLU.

I would also like to thank the core team of organizers – Mike Lynn, Jay carter, Danielle Mayers and Kim Tessereau for putting in a lot of hard work to make this event possible. There’s also several volunteers who helped out at the registration desk, lunch line and classrooms, all of who deserve a big thanks. Organizing a successful SQL Saturday is definitely a team effort and I could not have asked for a better team for this event.  No SQL Saturday event is possible with out the the speakers who contribute their time and skills , to present at the event. The generous support of all event sponsors plays an equally important role.

Last but the not the least, all the attendees who took the time to attend this event on a Saturday and are passionate about learning as well the SQL Community, deserve a big round of applause as well.

As organizers of the event, we noted a few improvements that can help us make the 2014 event even better;

1. Event Date – Quite a few of our regular local speakers, as well as several potential attendees could not make it to the event due to vacation plans. Several SQL Saturday organizers from the mid-west region had similar experiences in the months of July and August. We are planning for a event date in the month for September, for the 2014 St.Louis SQL Saturday.

2. Communication of Event start time and SpeedPASS – Though the first lecture for the day started at 9:30 AM and the registration desk opened at 8:30 AM, we had several attendees show up for the event before 8 AM.  some of the sponsor representatives did not get the directions to the free parking lot We will definitely be  much more clearer with our communication in the future. On the bright side, over 60% of the attendees came in with a printed SpeedPASS, which help the registration process move smoothly.

3. Lunch – We seem to have erred on the side of caution again while ordering lunch for the attendees, volunteers and sponsors. While we donated the left over lunch boxes to the building staff, we intend to plan the lunch orders better for the 2014 event.

4. After party – We intend to explore a venue closer to the SLU campus, for the after party for the 2014 event.

5. Recommended Hotel – While we were unable to secure a discount at the nearby hotels for the 2013 event, we intent to start negotiating with these hotel earlier for the 2014 event.

Please follow these links to the view the pictures taken during this event :

Please feel free to send us your feedback and suggestions to make the St.Louis SQL Saturday event better !

Please feel free to send us your feedback for the event.

SQL Saturday #214, Louisville KY

SQL Saturday #214 , Louisville KY , is coming up this weekend (July 13,2013) and I am looking forward to a trip to my favorite city in the mid-west. I was selected to present at the 2012 Louisville SQL Saturday and it was one of best SQL Saturdays I have attended so far (they get extra points for keeping ice-cream in the speaker’s lounge !). I am excited to be chosen to present at the 2013 Louisville SQL Saturday . I will be talking about Service Broker , and my session is scheduled for 1 PM in Room #3. This is definitely my favorite topic and I started my life as speaker by presenting this topic at the 2011 Kansas City SQL Saturday event. Over time, I have kept this session updated with advancements in SQL Server 2012 and the feedback I have received over the numerous events in the last 2 years.

So if you like to learn about service broker, understand its applications in real life situations and learn how to implement and troubleshoot service broker applications (with plenty of demos!), I look forward to seeing you at my session this Saturday.

 

 

Spring 2013 – Upcoming Speaking Engagements

Spring 2013 is shaping up to be a busy season for talking about SQL Server. So far I have an exciting line up of five sessions, scheduled for March and April of 2013

  • On 3/8/2013 at noon MST, I will be talking about Service Broker, with the PASS Application Development Virtual Chapter. I am really looking forward to this one, only only because it will be first talk with a PASS Virtual Chapter, but also because the topic “Service Broker” is one of my favorites. This was the topic of my very first presentation at the SQL Saturday in Kansas City, back in 2010. 
  • On 3/9/2013 , I will be talking about Database Unit Testing with Visual Studio, at the Greater Midwest SQL Relay. Oakwood Systems organizes this annual conference in Spring and its a huge hit in St.Louis. If you are in the area, please do make time to attend this free full day event of top notch SQL Server Training.
  • On 3/11/2013, I will be talking about Parameter Sniffing at the Capital Area SQL Server User group (PASS Local Chapter) in Albany, NY . I especially like this talk because I had learned about parameter sniffing while trying to tune a Query that was suffering from intermittent performance issues. Right around that time, I happened to come across Grant Fritchey’s  chapter on the same topic in the MVP Deep Dives Vol.2 book . The content there in was so awesome, I decided to make a presentation out of it, and it has been a popular topic at several SQL Saturdays in the Mid-West region.
  • On 3/26/2013, I will be talking about Database Unit Testing with VSTS, at the St.Louis Metro East .NET User group in O Fallon, IL .  The first time I talked about this topic was at the St.Louis Days of .NET in Aug 2012 and I have noticed a renewed interest in this topic since the release of VSTS 2012.
  • On 4/13/2013, I will be talking about Database Unit Testing with VSTS, at SQL Saturday #211 in Chicago, IL . SQL Saturday #31 (Chicago) in April of 2010 was the very first SQL Saturday I attended and it got me involved with the SQL Server Community. Its a huge honor for me to be selected to speak at the 2013 Chicago SQL Saturday and I am looking forward to this trip !

Programming with SQL Server: Variables

Understanding the numerous types of variable SQL Server has to offer, and their appropriate usage is one of the cornerstones of developing effective database code. I recently helped a co-worker fix an error message in his code.

-- Error
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@l_INT".

The code snippet looked something like this, which led to a discussion about scope of T-SQL variables and an interesting way to fix this problem.

CREATE TABLE #scope_test1
 (col1 INT);

CREATE TABLE #scope_test2
 (col1 INT);
 GO

DECLARE @l_INT INT = 42;

INSERT INTO #scope_test1 (col1)
 SELECT @l_INT;
 GO

INSERT INTO #scope_test2 (col1)
 SELECT @l_INT;
 GO

T-SQL language supports local variables (their names begin with a single @). The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared. A T-SQL batch is a group of statements that SQL Server parses as a single unit. A batch can be delimited by the BEGIN..END statements, or a client tool like SQL Server Management Studio (SSMS) can specify the end of a batch with a GO command (you can set any word to be a batch separator in SSMS, but we will leave that discussion for another time). The names of some Transact-SQL system functions begin with two at signs (@@). They are commonly referred to as Global variables. Global variable names begin with a @@ prefix. You do not need to declare them, since the server constantly maintains them. They are system-defined functions not variables and do not have the same behaviors as variables. All the global variables represent information specific to the server or a current user sessions. Some of the commonly used ones are @@ERROR, @@IDENTITY, @@VERSION. Traditionally, DECLARE command is used to declare a local variable and SET or SELECT used to initialize its value. With SQL Server 2012, both of these tasks can be accomplished in a single statement ;

DECLARE @MyCounter INT = 12 ;

The variables we discussed so far are scalar variables, which can hold a single data value of a specific type. A table variable is a special data type that can be used to store a result set for processing at a later time. table is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function. Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches. A DECLARE statement (similar to local scalar variables) is used to DECLARE a table variable. While they behave exactly like local variables, with a well defined scope,they can be thought of as being similar to temporary table, but with several limitations.

The SQLCMD utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses ODBC to execute Transact-SQL batches. Scripting variables can be used in SQLCMD scripts. Scripting variables enable one script to be used in multiple scenarios. The setvar command is used to define scripting variables. Variables that are defined by using the setvar command are stored internally. Scripting variables should not be confused with environment variables that are defined at the command prompt by using SET.

The scope of a SQLCMD scripting variable can span several batches, which can be used to implement variables that don’t go out of scope even when a batch ends. The example below is a simplified demo of the same ; 

:setvar l_int "42"
CREATE TABLE #scope_test1
 (col1 INT);
CREATE TABLE #scope_test2
 (col1 INT);
 GO
INSERT INTO #scope_test1 (col1)
 SELECT $(l_int);
 GO
INSERT INTO #scope_test2 (col1)
 SELECT $(l_int);
 GO

-- Results
(1 row(s) affected)
(1 row(s) affected)

A review of the types of variables the T-SQL language has to offer, has helped refresh my understanding of their appropriate usage.

References:

SQL Saturday #154, St.Louis – Event Review

SQL Saturday #154 | St.Louis on Sept 15th 2012, was the first SQL Saturday event in St.Louis, I am happy to say that we started off the SQL Saturday series in St.Louis ( yes, there will be one next year as well !) on a great note. We had over 130 attendees, 11 onsite sponsors and 25 technical sessions spread across 5 classrooms.

Center for Application of Information Technology (CAIT) at WashU, generously offered the venue (6 awesome classrooms plus the common areas and facilities) to host this event. The support of sponsors played a huge role in making this event successful. We got local as well as national sponsors, plus plenty of SWAG sponsors as well. Within a month of opening our call for speakers, we had over 55 sessions submitted for this event. They were a great mix of new and experienced speakers, both local and out of town, on topics ranging across Database Administration, Application Development, Business Intelligence and Professional Development. The real challenge was selecting only 25 sessions and the selection committee put a lot of thought into coming up with the session schedule for the event.

Our team took some time to review the event, which will help us make the next St.Louis SQL Saturday even better event. Feedback from attendees, sponsors, speakers and volunteers was crucial for the team to pin-point areas of improvement. 

  •  The duration of the overall event should be constrained from 9 AM to 4 PM, by either cutting down the session duration from 75 to 60 minutes or possibly having 4 sessions per classroom. We are trying to secure more classrooms at the  venue to make this feasible, so that the total number of session offerings during the event remains the same. 
  • We heard great feeback about the location and quality of the recommended event hotel, and we plan to continue the same for next year
  • We will definitely invest in bigger and more number of signs on the sidewalks around the venue, to make it easier for attendees to find their way to the parking area, registration desk and locate the facilities in the venue.
  • We are also looking at offering a better packaged box lunch  and making some healthy meal options available for next year, not only for lunch, but also for breakfast, snacks and beverages.
  • The SPEEDPASS worked out very well and we were able to move attendees efficiently through the registration process. We will probably do an online ONLY version of the event schedule for next year, to mitigate any confusion arising out of last minute schedule changes.
  • We plan  to leverage Twitter and Linkedin for communicating a majority of updates to event attendees , and use emails ONLY for crucial milestones during the days leading up to the event

Our team is always happy to hear feedback from the attendees and we have plenty of new ideas for the next year’s event. We do have some resource constraints, considering the fact that the event is free to attend, and all of the organizers are volunteers. The event sponsorship, availability of speakers, venue, and costs can change from one year to another and our team will strive to continue providing a valuable learning experience at the event. Organizing a SQL Saturday event, represents team work at its best. On that note, I would like to thank everyone involved; the core team, all the volunteers, PASS & community mentors, sponsors, speakers and friends & family for a successful first SQL Saturday in St. Louis.

Related material: Blog post on PASS Professional Development Chapter Website by Kathi Kellenberger

SQL Saturday #154 is almost here !

SQL Saturday #154, the first SQL Saturday in St.Louis is almost here (Sept 15) and I wanted to share some details of this exciting upcoming event.

This event will be held Sep 15th, 2012 at the CAIT Campus of Washington University in St.Louis. The Street address of the venue is – 5 Jackson Avenue, Center for Application of Information technology (CAIT) at Washington University in St.Louis, Clayton, MO 63105. Please follow this link for directions to the venue. The venue is only a short walk from the recommended hotel (Crowne Plaza Clayton). There is free parking for the event attendees in the attached parking Garage (Lot #60) on saturday, you do not need any parking pass or permit. The Garage can be accessed via Lee avenue. From this Garage, take the elevators to Level 1 and follow the signs for SQL Saturday #154 Registration desk. The registration desk will be open for attendees from 7.30 AM and the first 100 attendees to come in with a SPEEDPASS will get a free event t-shirt ! There will be coffee and donuts for breakfast (first come first served!). The first session for the day starts at 8.30 AM.

CAIT  has generously offered the use of their facilities to host the event and we would like to remind all of our attendees be respectful of their premises. We do want to make sure they will continue supporting us next year. We have 200 registered attendees (unfortunately, there are still 60+ on the waitlist!), 25 awesome sessions , and 16 sponsors (11 onsite) ! This is going to be a big event in a tight space and it would help for all the attendees to familiarize themselves with the classroom locations in the building.

We will be bringing in lunch boxes from Amighetti’s , a St.Louis favorite on the Hill (The Hill has all the awesome Italian restaurants in town). Your lunch tickets (for those who paid for lunch before midnight of Tuesday 9/11/2012) will be available on your SPEEDPASS. You can take your food and beverages (cans and plastic bottles ONLY) into the classrooms to eat. The Gold sponsors will be presenting 30 minute sessions during lunch in each classroom. Please do stop by their booths and to say hi (such events are not possible without their generous support) and drop your raffle tickets (also on your SPEEDPASS) to enter yourself in drawings for awesome prizes.

There will be plenty of volunteers to help out at the event (look for people wearing blue shirts and wearing lanyards that say “STAFF”).  After the last session of the day, we will all assemble in and around room #024 for the closing ceremonies – namely a thank you note and raffle prize drawings ! While we are trying our best to make sure we have taken care of everything, this is our first time organizing an event of this size and we would really appreciate all the help and support we can get to ensure a great event.

The after party will be a small informal gathering of the event attendees at Schlafly Bottleworks, in Maplewood, MO 63143. The SQL Saturday #154 team is only suggesting this venue. Attendees are responsible for their own food and beverages at the after party. SQL Saturday #154 organizers will not be responsible for anything at the after party.

On Friday night (sept 14) around 9.30 PM, we are planning to having a pre-event party in the form of  SQL Karaoke. Mike Talayna’s Juke Box Restaurant (1115 Hampton Avenue St. Louis, MO 63139 (314) 647-7600) starts their karaoke at 9.30 PM. All speakers and attendees are welcome to attend (look for kathi Kellenbeger at the event to find the SQL people). The SQL Saturday #154 team is only suggesting this venue. Attendees are responsible for their own food and beverages at this party. SQL Saturday #154 organizers will not be responsible for anything at the party.

We strive to ensure all of you will have a great time at SQL Saturday #154. Please do make the best of the awesome free opportunity to learn and network.

Database Unit Testing Made Easy with Visual Studio Team Systems

While brushing up on my knowledge of software testing concepts, I came across quite an amusing definition of testing; To tell somebody that he is wrong is called criticism. To do so officially is called testing” . A programmer usually resents it when a tester finds a defect in his code. We programmers thoroughly unit test our code before handing it off to a tester, because we take pride in developing a bug free application. Some programming languages (C# , VB, ASP.NET) afford themselves to be unit testing easily, because the application is developed within Visual Studio and can readily leverage its unit testing framework.

Visual Studio allows you to create Database projects, and database developers have started embracing it since Visual Studio Team Systems 2008 Database Edition GDR. This offers a robust framework for database developers to identify bugs with their database objects (schemas, stored procedures, functions, etc) by unit testing their database (T-SQL) code, before handing it over to the tester.  Before we jump into the specifics of database unit testing with Visual Studio, the next couple of paragraphs warm us up to the topic by covering a few basic concepts of software testing.

Software testing, undoubtedly plays a important role in the life cycle of most IT Projects. The Goal of any type of software testing is to identify defects to be fixed, so that the product meets requirements and has a deterministic and predictable output. Depending on the testing method employed, testing can be implemented at any time in the development process. Different software development models will focus the test effort at different phases in the development process. Newer development models, such as Agile, often employ test driven development and place an increased portion of the testing in the hands of the developer, before it reaches a formal team of testers.

Software testing methods are traditionally divided into white and black-box testing. These two approaches are used to describe the point of view that a test engineer takes when designing test cases. Unit Testing falls under the category of  white box testing, where the  the tester has access to the internal data structures and algorithms, including the code that implements these. This is in contrast with the black-box testing method, which  treats the software as a “black box”—without any knowledge of internal implementation. A black box tester is usually not a programmer, and  aims to only test the functionality of software according to the applicable requirements. Since the black-box tester has no knowledge on the underlying code, he may find bugs that a programmer misses. However, the same principle can sometimes lead to writing inefficient or incomplete test cases.

Unit Testing is a key component of Test driven development (TDD). Unit Tests are usually written by Developers while they work on the code, to ensure that a specific  of piece of code (Function, Class, Stored procedure, etc) is working as expected. Unit Testing helps to identify defects in the earlier stages of the software development life cycle, where they are cheaper to fix. Unit Testing can prove especially challenging in the world of database development, because of the need for a consistent test environment.

Database Unit Tests are used to establish a baseline state for a database and then to verify any subsequent changes that you make to database objects.  The Unit Testing Framework in Visual Studio (starting with VSTS 2005) helps database developers create, manage and execute Unit Tests for a Database. The Microsoft.VisualStudio.TestTools.UnitTesting namespace supplies classes that provide unit testing support. This namespace contains many attributes that identify test information to the test engine regarding data sources, order of method execution, program management, agent/host information, and deployment data. It also contains custom unit testing exceptions.

You will need the Database Edition GDR of VSTS 2008 or the Ultimate (or Premium) Editions of VSTS 2010 to create, modify and run database unit tests. You can run database unit tests with Professional Edition on VSTS 2010, but cannot create or modify them. Before you can start running database unit tests in VSTS, you must first create a Database Project and then create a test project. The next step is to write sets of Transact-SQL tests that exercise your database objects. Executing these tests in your isolated development environment helps you to verify whether those objects are behaving correctly before you check them in to version control. As changes are made to the database schema, you can use these tests to verify whether the changes have broken existing functionality. A detailed step by step walk through for creating and running database unit tests can be found  here on MSDN . Once Created, a Unit Test Project and of the tests will show up in the Solution Explorer View;

In a typical database unit test, a Transact-SQL test script runs and returns an instance of the ExecutionResult class. The instance of this class contains a DataSet, the execution time, and the rows affected by the script. All of this information is collected during execution of the script. These results can be evaluated within the Transact-SQL script by using the RAISERROR function, or they can be evaluated by using test conditions. Visual Studio Premium provides a set of the following predefined test conditions for you to use;

  • Data Checksum
  • Empty Resultset
  • Execution Time
  • Expected Schema
  • Inconclusive
  • Not Empty Resultset
  • Row Count
  • Scalar Value
You can also create negative unit tests to verify expected failures in a stored procedure. A detailed description of the database unit test conditions can he found  here on MSDN . Your T-SQL test scripts and the number of test conditions you can use, is only limited by your time and test scope constraints !
Visual Studio lets you create test lists to organize unit tests into groups. Test lists are also used to;
  • Run multiple unit tests as a group
  • Run tests as a part of a Build
  • Enforce check-in policy
Two of the most popular ways of organizing a test list is by the level of testing and by functionality;
Visual Studio 11 (currently Beta) comes with a host of unit testing enhancements. Some of the new features include the Unit Test Explorer and Support to Third Party Test Frameworks. More details can be found here on  Peter Provost’s MSDN Blog post .
Visual Studio not only continues to offer a robust framework for implementing database projects, but also makes a compelling case to use it for unit testing database code. It helps us programmers get one step closer to developing a bug free application. Usually, the end result of this undertaking is a rewarding “Test Results” screen with all indicators in green. Visual Studio also allows you to export and save the test results for future reference;
I am presenting a 60 minute session on this topic at the St.Louis Days of Dot Net Conference, Aug 2nd – 4th 2012, where I    will not only cover the basis of unit testing concepts and terminology, but also discuss how unit testing helps ensure and document the quality and accuracy of database deliverables. I will also will run through a demo of creating and running database unit tests using VSTS 2010 . I have spoken on the same topic in the past at the St.Louis SQL Server User Group meeting on June 12th 2012.
References:

Advanced T-SQL with Itzik Ben-Gan

I recently had the privilege of learning Advanced T-SQL  with Itzik Ben-Gan (Blog|Twitter), a mentor and one of the founders of SolidQ. This 2 day Advanced T-SQL course spanned across a range of interesting topics that a Database Developer would find extremely valuable on a day to day basis ! Not only does Itzik deliver a remarkable lecture, but also encourages questions and discussions, which adds a great deal of value to the learning experience. Some of the topics we covered in detail are;

  • SQL Server Internals & Index Tuning – This was my very first deep dive into SQL Server Internals pertinent to Indexes. The multitude of Index access methods that the Optimizer can choose from, and their impact on Query performance has opened a whole new world of indexing strategies for me. Filtered Indexes and Columnstore Indexes some of the newer features I am looking forward to try out.
  • Temporary Tables – Never before has the distinction between Temporary tables, Table variables and Common Table Expressions (CTEs) been presented with so much clarity  and lucidity. I now have a solid understanding of which to use in any given scenario.
  • Sets vs Cursors – Again, a classic topic. Never hurts to reinforce the advantages of a Set based solution versus cursors.
  • Query Tuning has never been so interesting with a real life example of dramatic performance gains between various revisions of the same query.
  • Apply – I cannot believe I had used the apply operator only for XML Queries so for. I have now learned its uses for unpivoting, aggregating and reusing column aliases.
  • Grouping Sets – I had learned this one while studying for my MCTS certification, but had never got around to making much use of it. This gave me the chance to understand its full potential and applicability.
  • Sequences – I had done some research on this topic before to my blog post about the same, but I found out soon that  there is still so much more to it, especially when comparing it with Identity.
  • The enhancements to Window Functions and the world of Partitioning, Ordering and Framing offers a whole new paradigm for OLAP Queries used in Reporting.

Over the next few weeks, I intend to take the time to absorb all the new concepts and ideas I learned in this 2 day course, and find areas to implement these ideas.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: