My speaking engagement schedule for the second half of 2013 is starting to shape up. If you are looking for speakers for your user group, I would love to help you out, especially if I can do so remotely. Send me a private message through Twitter @AuntKathi or through this site. I hope you can join me at some of these events! Date Venue Topic June 11 (Remote) St. Louis SQL Server User Group AlwaysOn Availability Groups and HA/DR Options June 11 I-380 Corridor (East Iowa) PASS TBD June 12 Quad Cit...
Read More
I have been writing a lot of T-SQL scripts lately for regression testing. I identify test cases and write scripts to modify the data. Then the data can be verified as the rows go through ETL. As I tweak the scripts, I need to continually restore the source databases to get the data back to the starting point. Instead of doing restores, I am using the Database Snapshot feature. Database Snapshots were introduced in 2005 in Enterprise and Developer editions. They allow you to quickly revert a data...
I am late for posting this in time for T-SQL Tuesday, but I thought I would contribute anyway. This month the topic is about your career path. My career has focused on SQL Server since 2002, but it was a long, winding road to get here. I didn’t start out in technology at all; I began my adult working life in pharmacy. After getting to play with a TRS 80 computer in a class during my last year of pharmacy school, I became obsessed with computers. Programming became my hobby, and I learned s...
IT is an interesting field; you are never done learning. In fact, things move pretty fast and it is hard to keep up. Even if you think you your job is stable, there are no guarantees and it pays to keep your skill sharp. I recently compiled a list of books that have helped me or are on my list to read (well, a couple of them I wrote) and I thought that list would be helpful to those who want to learn more about SQL Server. Learn T-SQL Beginning T-SQL 2008 Beginning T-SQL 2012 Microsoft SQL Serve...
Earlier this month I had the pleasure of attending SQL Saturday in Chicago . I have attended several SQL Saturdays over the past three years, only one of which I did not have to travel a long way to get to. Why would anyone give up a Saturday to attend one of these events? Here are the top 10 reasons that SQL Saturday is a WIN for everyone and why you need to attend: 10. SWAG bags. Sponsors have stuff to give away. Use your SWAG bag to collect this stuff. 9. T-Shirts. If you are new to tech even...
A very interesting question came up during my recent Training on the T’s presentation on indexes concerning the leaf level of nonclustered indexes. If the cluster key is made up of the primary key does the leaf level contain the primary key? The answer is no, it contains the cluster key. To test this, do the following: Connect to a copy of AdventureWorks2008R2 or AdventureWorks2012 and run the following code to create a table with a primary key of SalesOrderID, but a cluster key of OrderDa...
I presented on Indexes today for Pragmatic Works Training on the Ts . This is Back to Basics month, so my presentation was geared towards beginners. The video should be posted by the end of the week. Here are the slides and code . NOTE: You will need to also create a numbers table. My session went about 59 minutes, so there was not enough time to answer questions, and there were a ton of questions! Here are the questions and answers: Q: When or why should I use clustered vs. non-clustered? A: A ...
Typically, the data for an enterprise data warehouse does not arrive perfect. In fact, the data will come from many source and in many formats. One issue you will always have to contend with is missing values in the data. Task Factory from Pragmatic Works has a transform to help you deal with those missing values, the SSIS NULL Handler Transform . Recently, I decided to try out this transform to see just what it could do. I created a new 2012 SSIS package and added a connection manager to the pr...
When you set up AlwaysOn Availability Groups (AG) you have the choice for each node, when in secondary mode, to be readable. In fact, you can specify that connections will be accepted by the secondary node only with a special connection string specifying that it intends to be read-only. This is a great feature for offloading any read-only workload onto the secondary. The figure shows the AG group configuration with the Readable Secondary options. “No” means that connections will be a...
I gave a Training on the Ts Webinar today on AlwaysOn Availability Groups (AG) today. The recording should show up in a couple of days. My session was focused on showing how AG compares to SQL Server Failover Clustering and Database Mirroring as well as a quick tour of the features. One of the questions at the end of the presentation asked about licensing all the nodes. At the time, I could not recall if this is something that I had looked at since leaving Microsoft last year, a few months befor...
SQL Server Reporting Services (SSRS) has several features that you can use to create visualizations of your data like charts, gauges, KPIs, and maps. These features are great for creating dashboards. The chart feature can be used to show multiple data points or series on the same chart. For example, you might want to show this year’s sales compared to last year’s sales. This is how the report might look: Recently, I decided to create such a report and wondered how to write a query th...
March is Women’s History month in the US, and Pragmatic Works is celebrating by featuring female speakers all month in our Training on the Ts webinars . We are coordinating with the Women in Technology (WIT) PASS virtual chapter to bring you some of the best speakers in the SQL Server Community. So, be sure to check out our Training on the Ts lineup for March! At least here in the US, men far outnumber women in technology jobs, and SQL Server careers are no exception. There are many possib...
With AlwaysOn Availability Groups (AG), you can fail a group of databases over to another node. You may be wondering how to configure your application so that it always connects to the primary replica of the database – the replica that allows writes as well as reads. The good news is that there is no special connection string or application configuration that you must implement for redirection with AG. When setting up AG, you configure a listener which is a virtual name that always points ...
If you attended my session February 19, 2013, or watched the recording, here are the files from that presentation. http://www.bidn.com/Assets/Uploaded-CMS-Files/EE-c5260012-7658-4fce-b85d-cc38a883e4bd.zip
Last year SQL Server 2012 launched with a new choice for high availability and disaster recovery (HADR) called AlwaysOn Availability Groups. The AlwaysOn Availability Groups feature is similar to Database Mirroring which will be removed from SQL Server in a future release, but there are quite a few differences. In both cases, a replica of the data is maintained on another server for HA or DR purposes. Transactions are automatically sent to and replayed on the replica, or secondary copy of the da...
UPDATED 2013-02-14 I have a couple of speaking engagements lined up and have submitted sessions to several upcoming SQL Saturdays. I hope to meet you at some of the events. Here is the lineup: Date Event Session 2013-02-19 Pragmatic Works Training on the Ts Is SQL Server Enterprise Edition the Best Choice for Your Data? 2013-02-25 Wichita SQL Server Users Group Wichita, KS (I'll be online) T-SQL Analytic Functions 2013-03-09 The Greater Midwest SQL Relay -- St. Louis TBA 2013-03-12 Midlands PASS...
The OUTPUT clause, which has been around since SQL Server 2005, is a nifty little feature that lets you save the values of the rows you have just modified. You can just return this information or save it in an auditing table, for example. I’m doing some unit testing on several SSIS packages and have found the OUTPUT feature is invaluable. In my case, I am tweaking a few rows in the existing data to make sure I have rows that go down each possible path in the data flow. In order to keep a r...
It’s not you; it’s me. Sometimes dealing with data, especially data you are not familiar with, feels like detective work. I ran into two situations lately that took a bit of digging to figure out what was going on. I often see people blaming SQL Server for something that isn’t going right. Probably 99.999% of the time (let’s face it, no software is perfect) there is something that the DBA or developer is doing wrong or looking in the wrong place. Often they think a view h...
I wanted to point out some behavior to watch out for when you are working the ROW_NUMBER function along with TOP. Here is a query on the SalesOrderHeader table in AdventureWorks along with the ROW_NUMBER function: SELECT ROW_NUMBER() OVER(Order By CustomerID) RowNum, CustomerID, SalesOrderID FROM Sales.SalesOrderHeader; I am ordering the row numbers by the CustomerID, and the data just happens to be returned by the CustomerID as well. Since I am not using an ORDER BY on the query itself, SQL Ser...
One of the most challenging aspects of data warehouse ETL is handling slowly changing dimensions (SCD). First, you must determine what type of changes are required for each dimension attribute. Will no changes be allowed? Will updates be made to some attributes but you don't care about history? Or, do you need to keep track of historical changes? For example, a birthdate should never change, but marital status can change. Is it important to know the marital status for any point in time? If so, t...
It is common at the end of the year to look back over the previous 12 months and think about what one has accomplished in professional and personal areas of life. We humans like to divide time up into measurable chunks and, when a new cycle starts, such as a new year, we often set goals for ourselves to accomplish before the cycle is completed. Often, we fail miserably, but sometimes we are successful. 2012 was another big year of change for me. Personally, I became the grandmother of two new gr...
I've written a number of posts recently about the new T-SQL features. I thought it would be helpful for you to have a list with links to each one. You may also be interested in a Training on the T's webinar I did a couple of months ago where I demonstrated several of the features. Using First_Value and Last_value Percentile_Cont and Percentile_Disc Paging Percent_Rank and Cume_Dist Lag and Last Are the new T-SQL features ANSI compliant? Using the Sequence Object Enjoy!
Today I’d like to talk to you a bit about LEFT OUTER JOINs and what to watch out for. LEFT OUTER JOIN, or LEFT JOIN as I usually type it, returns all of the qualifying rows from the table on the LEFT side of the join and any rows on the RIGHT side that match. If you continue to join to more tables on the RIGHT side, you will need to continue using LEFT JOIN. For example, if I want a list of all the customers in AdventureWorks along with their SalesOrderIDs and OrderDates for any orders the...
I’ve been blogging a lot lately about the new analytic functions introduced with SQL Server 2012. I have two more to talk about: PERCENTILE_DISC and PERCENTILE_CONT. Previously, I covered CUME_DIST and PERCENT_RANK . Those two functions give you a percent ranking compared to a series of values. The PERCENTILE_DISC and PERCENTILE_CONT functions do the opposite. Given a series of values, which value is located at a certain percent rank? The most common example would be to find the median or ...
One of the great things about SSIS (SQL Server Integration Services) is the ability to make just about any property dynamic. You can set up variables which can be controlled through configurations or, if you are using SSIS 2012, through Parameters. The variables can then be assigned to object properties so that the property values are determined at run time. The most common use for this is for controlling connection properties. What do you do if the value of the variable must change during the e...