SQL 301
Time to take your database development to the next level! As I add articles to this series, they will appear below.
- Loading Hierarchical Data Using a WHILE EXISTS Posted on: 20120206
Last time, we discussed three methods for inserting data into a destination table that has a recursive relationship. In this article we’re going to cover how to insert data into that table without vi0lating the foreign key constraint by using a WHILE EXISTS loop. If you want to follow along with the examples, you’re [...] - Loading Hierarchical Data Posted on: 20120202
Earlier this week the question came up on how can we insert data into a table that has a hierarchical data structure. If you’re not familiar with a hierarchical data structures, here’s a quick explanation. Consider the staging table to the right. This table is used to load new accounts into our system. Each [...] - Improve Performance by Replacing Cursors Posted on: 20120125
This is the last article for my upcoming design patterns session. Today we’re going to look into speeding up your queries by replacing cursors, a row-by-row operation with a table value function. Before you start composing that hate-mail on how I’m wrong for not understanding cursors, let me say this: there is a time [...] - Upserts Posted on: 20120123
Up to this point in our design patterns sessions we’ve concentrated on removing duplicates from our troubled table. Now, let’s look into preventing those duplicates from being inserted in the first place. If you haven’t heard the phrase upsert before, it’s the idea of doing an INSERT and UPDATE in the same statement, that [...] - De-duping by CTE Posted on: 20120118
Ok, today we wrap up our design patterns for removing duplicates from tables by turning to common table expressions (CTE’s). When I start talking CTE’s some people begin to cringe in fear. I’ll admit, I still have to refer to a reference when doing something I haven’t done before with a CTE, which is [...] - De-duping by Self join Posted on: 20120111
Let’s continue the series on getting rid of duplicate data. Today we’re going to use self joins to get rid of them. A self join is a join from a table back to itself. It might seem weird to do that, but by the end of this article you’ll see how it’s useful. I [...] - De-duping by temp table Posted on: 20120109
Last time, we learned that manually removing duplicate rows from a table could be a long and manual process. Especially when you have as many duplicates as I created in our troubled table. Today we’re going to look at using temp tables to remove the extra rows. I gave you the overview to this [...] - Manually Deleting Stinks! Posted on: 20120104
Go ahead and set up your TroubledTable by running the Create Troubled Table T-SQl Script. Now take a look at your duplicate rows. SELECT email, COUNT(*) FROM troubledTable GROUP BY email HAVING COUNT(*) > 1 You’ve got 200 rows that have been duplicated over and over. Not a pretty sight. Imagine how long it [...] - Design Patterns Session Posted on: 20120102
On February 6, I’m going to tele-present a session on T-SQL design patterns to the Brazil SQL Server Users Group. During this session I’ll cover three common scenarios you face, that can be solved by using T-SQL: Removing duplicate rows from tables. Create Troubled Table T-SQl Script — This contains the table we’ll use [...] - SQL301–XML VALUE Method Posted on: 20110307
Last time, I covered the EXIST Method. This time around I wanted to show you how to pull out values from XML stored in SQL. Let’s start with what should have been a simple example. I wanted to pull the first and last name for a job applicant along with their resume in XML [...] - SQL 301-XML EXIST Method Posted on: 20110302
And now it’s time to wrap up the XML in SQL posts. At least, this is the last of the information you’re required to know for the 70-433. Anything above and beyond this is great…just not required. Let’s dive right in! Exist This first method I want to cover is EXIST. With this method [...] - SQL 301-The XML Data Type Posted on: 20110228
We’ve spent the last few posts working on getting relational data out of our tables and into XML format. Most of the time, that’s what I’ve used XML in SQL to do. But you’ve got to learn to build on what you know to learn. Today, we’re going to begin storing XML data in [...] - SQL301-Nesting For XML Queries Posted on: 20110223
Given the 4 options I’ve shown you for creating XML output from SQL, you should be able to create nearly any structure you’d want (especially considering FOR XML EXPLICIT). But you may actually c0me across a scenario where you can’t pull off what you want in a single query. When you can’t make a [...] - SQL 301–FOR XML PATH Posted on: 20110221
OK, I’ve covered FOR XML RAW for simple quick XML outputs. Then I covered FOR XML AUTO, while it automatically handles simple hierarchical data, it’s still pretty limited. And last time I covered FOR XML EXPLICIT, which is the end all – be all when it comes to building XML exactly the way you [...] - SQL 301-FOR XML EXPLICIT Posted on: 20110216
OK, so far I’ve covered FOR XML RAW. It’s a pretty simple way to crank out some XML when you need to. FOR XML AUTO has better support for hierarchies, but it too has it’s limits. The end all be all for extensibility is FOR XML EXPLICIT. It actually supports some options that you [...] - SQL 301-FOR XML AUTO Posted on: 20110214
In my previous XML article, I covered FOR XML RAW. It’s useful, if limited. This time I’d like to cover XML AUTO. It’s still limited, but if you’re looking to model simply hierarchical data, AUTO may be the option for you. If you’re looking for more details, I’d suggest checking out the books online [...] - SQL 301-FOR XML RAW Posted on: 20110209
OK, I need to study my XML queries, so I’m going to write them up as I go. That way you can learn along with me. In a normal query we return the results in either datagrid or text view, right. Well, if we want to transmit the results to another user or a [...] - SQL 301-BIDS, The First time around Posted on: 20110202
In my last post, we used the Import and Export wizard to create a simple Load process. We loaded names.csv into a simple table. Let’s open Visual Studio 2008 (Also known as Business Intelligence Development Studio when you’re talking about the version that comes with SQL Server 2008 R2. Once you’ve started it, open [...] - SQL 301 -- SSIS Import And Export Wizard Posted on: 20110131
OK, let’s dive right into the Import and Export Wizard. If you’re on Vista or Windows 7, hit start, then begin typing “Import and Export Data” It should find it for you pretty quickly. If you’re not on one of those two, look for your SQL 2005 or 2008 folder, and search in those [...] - SQL 301 - Introduction to SSIS Posted on: 20110126
I’ve been getting requests to cover SSIS, so here we go. Over the next few months I’m going to give you a course that will help you go from knowing what SSIS stands for (SQL Server Integration Services) to being proficient. You’ll learn how to work in SSIS. I’ll teach you what many of [...] - Sub-query Modifiers Posted on: 20100712
I’ll be honest, I thought I knew all there was to know about sub-queries. Turns out, there is something new (at least to me). You can modify your sub-query to allow you to pass multiple results. The way you do this is you add (ANY | SOME) or ALL to the query. Let me [...] - Equals Sub Queries Versus Exists Sub Queries Posted on: 20100426
I’d like to introduce you to the idea that you can replace an equal sub query with an exists sub query. Doing this can change a seek operation to a scan operation. This usually results in a faster query, with fewer resources used. Check out this example using the adventureworks database. USE AdventureWorks GO [...] - Sorting Numbers in VarChar field Posted on: 20100408
Over at Experts-Exchange, I saw a question I get every once in a while. It all has to do with how SQL Server orders data in a VARCHAR column. SQL Server tries to sort a VARCHAR column in a dictionary order, 0 comes before 1. The problem is you may have mixed numbers in [...] - SQL 301 - GROUPING Posted on: 20100125
In our previous lesson we covered WITH ROLLUP and WITH CUBE. During that lesson you may have noticed that when we saw the summary rows, the columns had NULL as the value. Hopefully you wondered to yourself, how do I know programatically which of the rows are summary lines and which are rows that [...] - SQL 301 -- WITH CUBE and ROLLUP Posted on: 20100111
Have you been asked to summarize some data into a nice report for management? They want to see subtotals and grand totals you say? You don’t have to drop the results of your GROUP BY statement into a temp table, and summarize the subtotals yourself. The SQL ROLLUP and CUBE commands offer a valuable [...] - ASC, DESC, and Filtered Views Posted on: 20090511
I know I’ve covered quite a bit of the CREATE INDEX statement, but there is even more to learn. Today I want to cover ASC, DESC, and the WHERE clause for indexes. ASC | DESC By default, indexes are created in ascending (ASC) order. This is fine most of the time. You’re going to [...] - SET TRANSACTION ISOLATION LEVEL Posted on: 20090323
Like I’ve mentioned before, unless you use SET TRANSACTION ISOLATION LEVEL, the server will default to READ COMMITTED. What this means is by default, your queries will only be able to interact with records that have been committed. If another query is affecting those records, you’re not able to see them in any way. [...] - SQL 301 - Views Posted on: 20081229
At this point I’m sure you know how to CREATE and ALTER a VIEW. But today I’d like to go into a little more detail on views. I’d like to cover updatable views, and some additional options you can add to your CREATE or ALTER VIEW statements. Updatable Views You can write insert s, [...] - sp_executesql Posted on: 20081110
I’m pretty sure this is the first time I’ve brought up dynamically generated SQL statements. But it’s time you learn about them. There are times where you’ll need to run the same statement against multiple tables. Or agains multiple databases. Sometimes, you may not know how many columns you’ll need to create or select [...] - Intro to XML, Part The Second Posted on: 20080505
Last time I gave you a short introduction into XML. I covered the very beginning of what it is, what you use it for, and I even showed you an example file. This time, I’d like to show you how to work with your XML in a web browser, Then I’d like to show [...] - XML, in as Few Posts as Possible Posted on: 20080428
Ok, I know I have a few groups of posts started, so you won’t mind me starting another one, right? Right, so this collection of posts will be all about XML. If you’re not using it already, or completely familiar with what you can do with XML, check out this collection. I’m hoping to [...] - Extract, Transform, and Load Posted on: 20070914
What is ETL? ETL stands for Extract, Transform and Load, a process used to collect data from various sources, transform the data depending on business rules and load the data into a destination database. The need to use ETL arises from the fact that in modern computing business data resides in multiple locations and [...] - Comparing Query Performance Posted on: 20060115
A significant portion of your time as a database programmer or administrator will be to improve the performance of queries. While there are several benchmarks you can compare two or more queries, the actual comparision of the two queries can lean you do make false assumptions. When you run a query, data and execution [...]
