tag:blogger.com,1999:blog-44046256514147607072024-03-04T20:03:53.294-08:00Es Cue ElNormalize? Darling, they're extraordinary.Unknownnoreply@blogger.comBlogger22125tag:blogger.com,1999:blog-4404625651414760707.post-46928766030895101102012-10-10T05:31:00.001-07:002012-10-10T05:31:43.289-07:00Home on the (Date) RangeHere's an interesting problem that came across my desk this week. We have, to generalize the data a little, a table containing various product discounts, each of which has a date range during which it applies. <br />
<br />
Here's what the table looks like:<br />
<blockquote class="tr_bq">
CREATE TABLE productdiscounts (startdate DATE,enddate DATE,keyval CHAR(3),discount int) </blockquote>
<blockquote class="tr_bq">
INSERT INTO productdiscounts VALUES<br />
('1/1/12','1/31/12','abc',1)<br />
,('1/17/12','2/15/12','abc',8)<br />
,('2/7/12','3/7/12','abc',4)<br />
,('1/15/12','5/13/12','def',2)<br />
,('4/17/12','4/28/12','def',16)<br />
,('2/12/12','4/20/12','def',32)</blockquote>
The challenge was, we need to derive a set that has a row for each date range for which a different set of discounts applies. So, for product abc, we have a $1 discount from 1/1/12 through 1/16/12, and then on 1/17/12, an additional discount of $8 takes effect. On 2/1/12, the $1 discount goes out of effect, and then on 2/7/12, a new $4 discount takes effect. And so on.<br />
<br />
Basically, the set we need to create should look like this:<br />
<blockquote class="tr_bq">
keyval<span class="Apple-tab-span" style="white-space: pre;"> </span>startdate<span class="Apple-tab-span" style="white-space: pre;"> </span> enddate<span class="Apple-tab-span" style="white-space: pre;"> </span> discount<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-01-01 <span class="Apple-tab-span" style="white-space: pre;"> </span>2012-01-16<span class="Apple-tab-span" style="white-space: pre;"> </span>1<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-01-17<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-01-31<span class="Apple-tab-span" style="white-space: pre;"> </span>9<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-01<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-06<span class="Apple-tab-span" style="white-space: pre;"> </span>8<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-07 <span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-15<span class="Apple-tab-span" style="white-space: pre;"> </span>12<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-16<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-03-07<span class="Apple-tab-span" style="white-space: pre;"> </span>4<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-01-15<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-11<span class="Apple-tab-span" style="white-space: pre;"> </span>2<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-12<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-04-16<span class="Apple-tab-span" style="white-space: pre;"> </span>34<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-04-17<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-04-20<span class="Apple-tab-span" style="white-space: pre;"> </span>50<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-04-21 <span class="Apple-tab-span" style="white-space: pre;"> </span>2012-04-28<span class="Apple-tab-span" style="white-space: pre;"> </span>18<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-04-29<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-05-13<span class="Apple-tab-span" style="white-space: pre;"> </span>2</blockquote>
So how do you take a set of staggering, overlapping date ranges, and come up with one continuous set of ranges, aggregating the discounts as you go? <br />
<h3>
Our old friend, the numbers table</h3>
My first instinct was to use a numbers table and compute the aggregates against that set, like this:<br />
<br />
<blockquote class="tr_bq">
SELECT pd.keyval,d.dayval,SUM(pd.discount) FROM<br />
(SELECT DATEADD(d,number,'1/1/12') AS dayval FROM master..spt_values WHERE type='p') d<br />
INNER JOIN dbo.productdiscounts pd ON pd.startdate<=d.dayval AND pd.enddate>=d.dayval<br />
GROUP BY pd.keyval,d.dayval</blockquote>
The derived table d gives us a set of days from 1/1/12 through sometime in 2017 (2048 days), and by joining to the product discounts, we can add up the discount in effect on each day. The resultant set looks like this (I omitted the records for every single day):<br />
<br />
<blockquote class="tr_bq">
keyval<span class="Apple-tab-span" style="white-space: pre;"> </span>dayval<span class="Apple-tab-span" style="white-space: pre;"> </span>totaldiscount<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-01-01<span class="Apple-tab-span" style="white-space: pre;"> </span>1<br />
...<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-01-16<span class="Apple-tab-span" style="white-space: pre;"> </span>1<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-01-17<span class="Apple-tab-span" style="white-space: pre;"> </span>9<br />
...<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-01-31<span class="Apple-tab-span" style="white-space: pre;"> </span>9<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-01<span class="Apple-tab-span" style="white-space: pre;"> </span>8<br />
...<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-06<span class="Apple-tab-span" style="white-space: pre;"> </span>8<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-07<span class="Apple-tab-span" style="white-space: pre;"> </span>12<br />
...<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-15<span class="Apple-tab-span" style="white-space: pre;"> </span>12</blockquote>
Now the question becomes, how do we find the starting and ending dates for each unique range? It's not enough to just look for MIN(dayval) and MAX(dayval) for a given aggregate, since the total discount might go from 1 to 9 and then back to 1 again. SQL 2012 has some window functions that would help with this, but our shop is barely on 2008. <br />
<br />
We were pretty close to (ugh) walking this table with a cursor. But then we did the math. For two years of discounts, we'd have over 700 rows for each product, multiplied by the number of products. Using a cursor on millions or rows wasn't a pleasant prospect. <br />
<h3>
A fresh approach</h3>
Looking at the data, we realized that the number of dates involved was fairly small. Discounts tended to start and end at month end, so in a two year range, we had maybe 40 dates on which a discount started or ended. And so that got me thinking. <br />
<br />
Our desired date ranges would always start on either the start date of a particular discount, or on the day after the end date of a discount. If a discount wasn't starting or ending, there'd be no change in the total discount that day. <br />
<br />
With this in mind, we can create a CTE of "interesting dates":<br />
<br />
<blockquote class="tr_bq">
WITH interestingdates AS (<br />
SELECT DISTINCT keyval,startdate FROM dbo.productdiscounts<br />
UNION SELECT DISTINCT keyval,DATEADD(d,1,enddate) FROM dbo.productdiscounts)</blockquote>
Note that the DISTINCT will eliminate any duplicate dates in the list. Note also that the keyval (i.e. the product id) is also included, since a date is interesting only for the product affected by that discount.<br />
<br />
Armed with our list of interesting dates by product, we can calculate the end date of each range, which is just the day before the next start date:<br />
<br />
<blockquote class="tr_bq">
,interestingranges as<br />
(SELECT a.keyval,a.startdate,<br />
(SELECT DATEADD(d,-1,MIN(startdate)) FROM interestingdates b WHERE b.keyval=a.keyval AND b.startdate>a.startdate) AS enddate<br />
FROM interestingdates a)</blockquote>
<div>
Which leaves us in a pretty good spot. Here's what the interestingranges CTE looks like:</div>
<div>
<blockquote class="tr_bq">
keyval<span class="Apple-tab-span" style="white-space: pre;"> </span>startdate<span class="Apple-tab-span" style="white-space: pre;"> </span> enddate<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-01-01<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-01-16<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-01-17<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-01-31<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-01<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-06<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-07<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-15<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-16<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-03-07<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-03-08<span class="Apple-tab-span" style="white-space: pre;"> </span>NULL<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-01-15<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-11<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-12<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-04-16<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-04-17<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-04-20<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-04-21<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-04-28<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-04-29<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-05-13<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-05-14<span class="Apple-tab-span" style="white-space: pre;"> </span>NULL</blockquote>
Notice that the last date range for each product is open-ended, since our calculation of the end date is based on the next start date. These ranges might be handy; if not, it's easy to filter them out.<br />
<br />
Having identified the interesting ranges, we just need to aggregate the discount amounts:<br />
<br />
<blockquote class="tr_bq">
SELECT ir.keyval, ir.startdate, ir.enddate, SUM(pd.discount) AS discount<br />
FROM interestingranges ir<br />
INNER JOIN dbo.productdiscounts pd ON pd.startdate<= ir.startdate AND pd.enddate>=ir.enddate AND pd.keyval=ir.keyval<br />
GROUP BY ir.keyval, ir.startdate, ir.enddate</blockquote>
<div>
Here's our completed aggregate:</div>
<div>
<blockquote class="tr_bq">
keyval<span class="Apple-tab-span" style="white-space: pre;"> </span>startdate<span class="Apple-tab-span" style="white-space: pre;"> </span> enddate<span class="Apple-tab-span" style="white-space: pre;"> </span> discount<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-01-01 <span class="Apple-tab-span" style="white-space: pre;"> </span>2012-01-16<span class="Apple-tab-span" style="white-space: pre;"> </span>1<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-01-17<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-01-31<span class="Apple-tab-span" style="white-space: pre;"> </span>9<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-01<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-06<span class="Apple-tab-span" style="white-space: pre;"> </span>8<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-07 <span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-15<span class="Apple-tab-span" style="white-space: pre;"> </span>12<br />
abc<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-16<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-03-07<span class="Apple-tab-span" style="white-space: pre;"> </span>4<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-01-15<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-02-11<span class="Apple-tab-span" style="white-space: pre;"> </span>2<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-02-12<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-04-16<span class="Apple-tab-span" style="white-space: pre;"> </span>34<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-04-17<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-04-20<span class="Apple-tab-span" style="white-space: pre;"> </span>50<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-04-21 <span class="Apple-tab-span" style="white-space: pre;"> </span>2012-04-28<span class="Apple-tab-span" style="white-space: pre;"> </span>18<br />
def<span class="Apple-tab-span" style="white-space: pre;"> </span> 2012-04-29<span class="Apple-tab-span" style="white-space: pre;"> </span>2012-05-13<span class="Apple-tab-span" style="white-space: pre;"> </span>2</blockquote>
I purposely chose discount amounts that were powers of 2, so it's easy to check which discounts are in effect on any day against the aggregate. A $50 discount on def from 4/17 through 7/20, for instance has to be $32 + $16 + $2. In reality, it's possible that two (or more) contiguous date ranges to result in the same aggregate-- for example, a $5 discount could expire, and two new discounts for $3 and $2 could take effect on the same day. In our case, the aggregation was a little more involved, in such a way that the unique set of individual discounts still made each range unique, even if the total discount ended up the same.<br />
<br />
The total script ends up looking like this:<br />
<br />
<blockquote>
--an interesting day is one where a promotion starts or the day after a promotion ends<br />
;WITH interestingdates AS (<br />
SELECT DISTINCT keyval,startdate FROM dbo.productdiscounts<br />
UNION SELECT DISTINCT keyval,DATEADD(d,1,enddate) FROM dbo.productdiscounts<br />
) </blockquote>
<blockquote>
--an interesting range is just each interesting date and the next interesting date, by key<br />
,interestingranges as<br />
(SELECT a.keyval,a.startdate,<br />
(SELECT DATEADD(d,-1,MIN(startdate)) FROM interestingdates b WHERE b.keyval=a.keyval AND b.startdate>a.startdate) AS enddate<br />
FROM interestingdates a<br />
) </blockquote>
<blockquote>
SELECT ir.keyval, ir.startdate, ir.enddate, SUM(pd.discount) AS discount<br />
FROM interestingranges ir<br />
INNER JOIN dbo.productdiscounts pd ON pd.startdate<= ir.startdate AND pd.enddate>=ir.enddate AND pd.keyval=ir.keyval<br />
GROUP BY ir.keyval, ir.startdate, ir.enddate</blockquote>
</div>
</div>
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-7863231348365674502011-12-13T09:52:00.000-08:002011-12-13T18:28:24.339-08:00T-SQL Tuesday #025 : Tricks of the Trade<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://sqlblog.com/blogs/allen_white/archive/2011/12/05/t-sql-tuesday-025-invitation-to-share-your-tricks.aspx" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipUqiNdq4pw-L44c1f-lcxfG2694IG2Um7czPMUJ0YCyR2cVfxu9hM4sguqIP3L-yrCMOkJhiatedW4zOUxOW-LmzY1k-xalTb4tjrBZyy8ckMWrDzMDJ3HKm46ffrFJQo1BFLTl5N9nc/s1600/tsqltuesday.jpg" /></a></div>
This week's SQL Tuesday theme is "Tips and Tricks", which led me to start making an inventory of the tricks and tools I use almost daily as a SQL developer.<br />
<div>
<br /></div>
<div>
As an old(er) dog, many of the tricks I use are things I've learned over the past couple of decades, and as I document them, I'm finding that in many cases, there's new ways of doing them (DMO's, for example). It's no excuse for not learning the new, better methods, but in my defense I'll just say that I have to support clients back to SQL 2000 (and occasionally even 7!) so I find that the old ways still work really well, even if newer versions introduce newer features.</div>
<div>
<br /></div>
<div>
<b><span class="Apple-style-span" style="font-size: large;">First, some tweaks to the environment:</span></b><br />
<br />
My first trick is the only one with a price tag. I've been working with <a href="http://www.red-gate.com/products/sql-development/sql-developer-bundle/">Redgate's SQL Developer Bundle</a> all summer, and I can't imagine ever <i>not</i> using it for SQL development. It's pricey, but I'd estimate that I work 50-100% faster with it. If there's money in the budget, ask your boss to get you the best tools available. I'll be happy to talk to your boss if you want a testimonial of how much productivity you can buy with these tools.<br />
<br />
Next, color your connections! SSMS will do this if you <a href="http://sqlserverperformance.wordpress.com/2010/03/28/using-custom-connection-colors-in-ssms/">tell it to</a>, but I prefer the coloring provided as part of the free <a href="http://www.ssmstoolspack.com/">SSMS Tools Pack</a>. Nothing reminds you that you're on the production server like a giant red bar at the edge of the query editor. SSMS Tools Pack also automatically saves all your tabs periodically with a searchable history, gives you SQL snippets (similar to SQL Prompt's), helps analyze execution plans, and about ten other handy things, all free.<br />
<br />
Finally, if you're staring at the query editor all day, why not try a soothing color scheme? I really prefer a black background with light text. Tomas Restrepo wrote a <a href="http://winterdom.com/2007/10/colorschemesinsql2005managementstudio">handy little tool</a> for porting color themes between Visual Studio and SSMS. I've found it handy to backup the following registry key after I set the colors I like:<br />
<blockquote class="tr_bq">
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\FontAndColors</blockquote>
which makes it handy to switch between themes. I use a dark background for development, and a white background for presentations. There's lots of free themes out there at <a href="http://studiostyl.es/">Studio Styl.es</a> for you to try. (Note: if you import a registry key with a new color scheme, you can implement the changes without starting SSMS by going to Tools/Options, touching Fonts and Colors, and hitting OK.)</div>
<div>
<div>
<br />
<b><span class="Apple-style-span" style="font-size: large;">Everyone's a DBA Sometimes:</span></b><br />
<div>
<div>
<br />
Even though I'm usually in development mode, when things get slow, any database person will do.<br />
<br />
Activity monitor will help you find blocking processes, but often when there's blocking, there's panic. I keep this code in my snippets (or create a stored procedure and add it to SSMS's keyboard shortcuts):</div>
</div>
<div>
<div>
<blockquote class="tr_bq">
SELECT * FROM master..sysprocesses WHERE blocked<>0 and blocked<>spid<br />
SELECT * FROM master..sysprocesses WHERE blocked=0 and spid in<br />
(select blocked from master..sysprocesses where blocked<>0 and spid<>blocked)</blockquote>
</div>
</div>
<div>
It immediately tells me if anyone's being blocked, and the head of the blocking chain. It also works in every version of SQL. DBCC INPUTBUFFER(spid) is usually not far behind.<br />
<br />
Yes, you can do this from Activity Monitor and most tools have a way to find it relatively quickly, but having a snippet defined lets me run it with two keystrokes, immediately and repeatedly.<br />
<br />
The other thing I setup in any environment is a snippet or stored procedure to gather information from a variety of application-specific logs table. Being able to see the last ten errors from a few apps, the ETL processes, maybe even some realtime stats like number of sessions, is invaluable when things are going wrong.<br />
<br />
<span class="Apple-style-span" style="font-size: large;"><b>Development Tricks</b></span><br />
<br />
----------------------------------------------<br />
<br />
I've come to rely on Redgate's SQL Seach (it's free!) for finding anything in the database. If I don't have access to it, I usually have this in a snippet for searching the text of stored procedures, triggers, etc:<br />
<blockquote class="tr_bq">
<span style="color: blue; font-family: 'Courier New'; font-size: x-small;"><span style="font-size: 10pt;">SELECT</span></span><span style="font-family: 'Courier New'; font-size: x-small;"><span style="font-size: 10pt;"> <span style="color: magenta;">schema_name</span><span style="color: grey;">(</span>SO<span style="color: grey;">.</span><span style="color: magenta;">schema_id</span><span style="color: grey;">)+</span><span style="color: red;">'.'</span><span style="color: grey;">+</span>SO<span style="color: grey;">.</span>NAME<span style="color: grey;">,</span>SO<span style="color: grey;">.</span>TYPE_DESC<span style="color: grey;">,</span>SM<span style="color: grey;">.</span><span style="color: blue;">DEFINI<wbr></wbr>TION</span><span style="color: grey;">,</span>SO<span style="color: grey;">.</span>CREATE_DATE<span style="color: grey;">,</span>SO<span style="color: grey;">.</span>MODIFY_<wbr></wbr>DATE<br /><u></u><u></u></span></span><span style="color: blue; font-family: 'Courier New'; font-size: x-small;"><span style="font-size: 10pt;">FROM</span></span><span style="font-family: 'Courier New'; font-size: x-small;"><span style="font-size: 10pt;"> <span style="color: green;">SYS</span><span style="color: grey;">.</span><span style="color: green;">SQL_MODULES</span> SM<br /><u></u><u></u></span></span><span style="color: grey; font-family: 'Courier New'; font-size: x-small;"><span style="font-size: 10pt;">INNER</span></span><span style="font-family: 'Courier New'; font-size: x-small;"><span style="font-size: 10pt;"> <span style="color: grey;">JOIN</span> <span style="color: green;">SYS</span><span style="color: grey;">.</span><span style="color: green;">OBJECTS</span> SO <span style="color: blue;">ON</span>SO<span style="color: grey;">.</span><span style="color: magenta;">OBJECT_ID</span><span style="color: grey;">=</span>SM<span style="color: grey;">.</span><span style="color: magenta;">OBJECT_ID<br /><u></u><u></u></span></span></span><span style="color: blue; font-family: 'Courier New'; font-size: x-small;"><span style="font-size: 10pt;">WHERE</span></span><span style="font-family: 'Courier New'; font-size: x-small;"><span style="font-size: 10pt;"> SM<span style="color: grey;">.</span><span style="color: blue;">DEFINITION</span> <span style="color: grey;">LIKE</span> <span style="color: red;">'%blah%'</span></span></span></blockquote>
</div>
<div>
</div>
<br />
----------------------------------------------<br />
<div>
<br /></div>
<br />
Before I started using <a href="http://www.simple-talk.com/sql/database-administration/creative-solutions-by-using-a-number-table/">numbers tables</a> (sometimes called "<a href="http://www.sqlservercentral.com/articles/T-SQL/62867/">tally tables</a>"), I thought my life was complete. Now I use them for all kinds of things, particularly populating data. Anytime I need a set of integers, my go-to set is:<br />
<blockquote class="tr_bq">
SELECT number FROM master..spt_values WHERE type='p'</blockquote>
It contains integers 0-2047, and if you need bigger, you can join it to itself to get 4 million, 8 billion, etc.<br />
<br />
You can check out the above links for ideas about how to use numbers tables, but for example if I need a table of days for the next five years or so, you can whip one up with:<br />
<blockquote class="tr_bq">
SELECT DATEADD(d,Number,'1/1/12') as MyDate, Number%7+1 as DayOfWeek<br />
FROM (SELECT Number FROM master..spt_values WHERE type='p') numbers </blockquote>
<br />
----------------------------------------------<br />
<div>
<br /></div>
<br />
Hardly a day goes by when I don't have to convert to or from a comma-delimited list, usually for passing sets around when table-valued parameters aren't an option. FOR XML PATH is a quick way to get a list of values into a single, comma-delimited string:<br />
<blockquote class="tr_bq">
DECLARE @mylist VARCHAR(MAX)<br />
SET @mylist=(SELECT TOP 10 ','+name FROM sysobjects WHERE type='p' FOR XML PATH(''))<br />
SET @mylist=STUFF(@mylist,1,1,'')<br />
SELECT @mylist</blockquote>
You can use a numbers table to quickly split the delimited string back into its components. (Also see Jeff Moden's definitive piece on <a href="http://www.sqlservercentral.com/articles/Tally+Table/72993/">tally-table splitters</a>):<br />
<blockquote class="tr_bq">
SET @mylist=','+@mylist+','<br />
;WITH n AS (SELECT number FROM master..spt_values WHERE type='p' AND number < LEN(@mylist))<br />
SELECT SUBSTRING(@mylist,number+1,CHARINDEX(',',@mylist,number+1)-number-1) FROM n WHERE SUBSTRING(@mylist,number,1)=','</blockquote>
----------------------------------------------<br />
<br />
Finally, hardly a day goes by when I don't need to get something random. Although RAND() will get you a random number, that doesn't help if you want to generate a set of random numbers. You can always use a loop or a cursor, but there's a better way. <br />
<blockquote class="tr_bq">
SELECT ABS(CHECKSUM(NEWID()))%9+1 FROM MASTER.sys.columns</blockquote>
will return a list of random numbers between 1 and 9. This can be particularly handy for generating test data, particularly when combined with TOP() and CROSS APPLY (more on this in another post).<br />
<br />
Of course, NEWID() also comes in handy for getting a random selection of existing rows.<br />
<blockquote class="tr_bq">
SELECT TOP 15 * FROM MyTable ORDER BY NEWID()</blockquote>
will return a random set of 15 records every time it's run.<br />
<br />
<div>
<b style="font-size: x-large;">Social Tricks</b></div>
<div>
<br /></div>
<div>
If you're reading this blog, you probably know the most important trick: be connected! There's a huge network of wicked-smaht, generous SQL experts out there, and more free blogs and videos out there than you'll ever have time for. Here's some of my favorites:</div>
<div>
<ul>
<li>Brent Ozar PLF's <a href="http://us2.campaign-archive1.com/home/?u=9082566fb63d87be35c0662bc&id=8e3e861dd9">Weekly News</a> letter is the best thing about Monday, and usually points me to a dozen cool things, SQL and not.</li>
<li>There's probably a <a href="http://www.sqlpass.org/PASSChapters.aspx">SQL PASS chapter</a> near you. And if there's not, they have <a href="http://www.sqlpass.org/Community/VirtualChapters.aspx">Virtual Chapters</a>. These are great way to learn about SQL topics, meet great SQL people, network, and much much more. Check yours out!</li>
<li><a href="http://www.sqlsaturday.com/">SQL Saturdays</a> are a great, free alternative to the incomparable <a href="http://www.sqlpass.org/summit/2012/">PASS Summit</a>. Some pretty phenomenal speakers donate their time and wisdom to these, and again they're a great chance to meet fellow SQL geeks. (Hint: many presenters also generously upload their presentation materials to the SQL Saturday sites.)</li>
<li> <a href="http://sqlbits.com/">SQL Bits</a>, <a href="http://www.sqlservercentral.com/">SQL Server Cental</a> (particularly their <a href="http://www.sqlservercentral.com/stairway/">Stairway</a> series), and<a href="http://msdn.microsoft.com/en-us/sqlserver/bb671054"> SQL Server Team Blogs</a> are all great spots to go read and watch tons more great SQL content.</li>
<li>And of course, if you're not on Twitter, get on! Almost all of the SQL MVP's, presenters, and even the SQL development team follow #sqlserver and #sqlhelp. Brent Ozar even wrote a <a href="http://www.brentozar.com/twitter/book/">free book</a> specifically for SQL Twitter virgins, so you have no excuse. A typical Twitter experience: I tweeted thanks for some SQL feature I liked, and a few minutes later, the Microsoft engineer who wrote it tweeted back, "You're welcome!" How cool is that?</li>
</ul>
</div>
<div>
<br /></div>
<div>
<br /></div>
</div>
</div>
<div>
<br /></div>
<div>
<br /></div>Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-4404625651414760707.post-63621595171503190672011-12-06T09:55:00.001-08:002011-12-06T13:03:31.311-08:00Can Your Network Do This?<div style="text-align: right;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: left;">
A couple of months ago, my wife and I took a road trip from Cleveland to Pittsburgh. Stuck in bumper to bumper traffic, the woman behind me suddenly lept from her car, ran up behind my car, and took a picture of my license plate. She hollered something like "I love SQL!" and disappeared back into traffic.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
It took three days for a friend to send this back to me in a tweet:</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgi7lhoZR5DHK-UdQuChA2yNysS799m-OMNWB0Z3hSfTEn_9uqDZe2ECQY1kIV40gubyBTjQODB8R5GerTohigvMeKrAQ57DBvwvC1Q3n_bzYFjczEVXsyzyeVPr05tX9OI4gzthuk38M8/s1600/coolplate.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgi7lhoZR5DHK-UdQuChA2yNysS799m-OMNWB0Z3hSfTEn_9uqDZe2ECQY1kIV40gubyBTjQODB8R5GerTohigvMeKrAQ57DBvwvC1Q3n_bzYFjczEVXsyzyeVPr05tX9OI4gzthuk38M8/s320/coolplate.JPG" width="284" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Aside from the cosmic coolness of having attractive, nerd-appreciating women accosting me in traffic, it occurred to me that this was an indication of a pretty good professional network. Can <i>your</i> network do this? How many degrees of Kevin Bacon are <i>you</i> from a random stranger on the freeway?</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
One of my personal and professional goals this year was to do more networking. I don't suppose I'm alone in the tech world in putting "networking" right up there with "root canal" in the list of things I'd rather not be doing or having done to me. Images come to mind of schmoozing and making uncomfortable small talk, and telling people we should do lunch, or take lunch, or whatever verb it is people do to lunch while networking.<br />
<br />
As it turns out, networking can largely be whatever you want it to be, so long as you're connecting to other people. Yes, I've "done" a couple of really uncomfortable lunches with people trying to sell me something, or see if I could sell something for them. But I've also been writing this blog, tweeting, drinking beer with other SQL geeks, and attending some interesting (for me) conferences. Yes, it's still an effort-- I'm an introvert, and as much fun as I have hanging out with SQL people, I'd often much rather just be at home with my wife and cats. But really, it hasn't been a <i>chore</i>-- just a conscious effort to get more involved in the SQL community.<br />
<br />
And you can do it, even if you have major social anxiety.<br />
<br />
Here's some things that I've found good luck with:<br />
<br />
<ul>
<li><a href="http://www.sqlpass.org/PASSChapters.aspx">Find a PASS chapter</a>. There are groups in most major cities, and if there isn't one near you, there are <a href="http://www.sqlpass.org/Community/VirtualChapters.aspx">virtual chapters</a>. It's a chance to hang out with other SQL people, learn some things, and just get a feel for what's going on in the SQL world, both at large and in your town.</li>
<li>Get involved with <a href="http://www.sqlsaturday.com/">SQL Saturday</a>, <a href="http://www.sqlpass.org/sqlrally/2012/dallas/">SQL Rally</a>, or <a href="http://www.sqlpass.org/summit/2012/">SQL Summit</a> events. Some of these are run on shoestring budgets and eager for any help you can give. Even if your job is just handing out flyers or stuffing bags, being a volunteer gets you backstage access to presenters, MVPs, and others in the community. If you can't volunteer, at least try to attend. </li>
<li>Blog something. I should spend more time on this, and web presence in general, but having a blog is an opportunity to share your knowledge and get your name out there a little bit. </li>
<li>Read other people's blogs. <a href="http://sqlbits.com/">SQL Bits</a>, <a href="http://www.sqlservercentral.com/">SQL Server Cental</a> (particularly their <a href="http://www.sqlservercentral.com/stairway/">Stairway</a> series), and<a href="http://msdn.microsoft.com/en-us/sqlserver/bb671054"> SQL Server Team Blogs</a> are all great places to find some great SQL blogs to start.</li>
<li><a href="http://oauth.twitter.com/SteveSmithSQL">Tweet</a> something. The SQL community is hugely involved in Twitter, and that includes the guys and gals who write SQL Server. I once tweeted "Thanks, Microsoft!" about some SQL feature that I really liked, and a few minutes later, the developer who wrote that feature tweeted back, "Glad you like it!". How cool is that? Follow #sqlserver and #sqlhelp to start, and read Brent Ozar's <a href="http://www.brentozar.com/twitter/book/">great introduction</a> to Twitter for SQL folks.</li>
<li>Of course, get yourself <a href="http://www.linkedin.com/">LinkedIn</a>. It's not like Facebook, honest-- it's just a good way to keep in contact with people you've worked with.</li>
</ul>
<div>
Notice that some of these don't even require you to leave the living room. Of course, you'll make more meaningful connections if you actually get out and talk to people. You might even-- gasp-- make a friend or two. But at a minimum, the goal is to stay connected to the community. </div>
<div>
<br /></div>
<div>
One big epiphany I had this year was gleaned from the classic What Color Is Your Parachute: a big goal of networking is to increase the number of people you can call (or email) to get introduced to someone who can help you. That's it. These people don't have to be able to <i>vouch</i> for your work personally (although that helps), they just have to be able to introduce you. </div>
<div>
<br /></div>
<div>
So, for example, I volunteered to help organize <a href="http://www.sqlsaturday.com/60/eventhome.aspx">SQL Saturday</a> with my local PASS chapter, which introduced me to dozen or so SQL people here in Cleveland. Now, aside from my ability to order great sandwiches, they don't know much about my SQL skills. But collectively they probably know someone in every SQL shop in town, or they know someone who does. So now I not only have some cool SQL friends, but I have a little network of people, and when I find myself asking, "I wonder who I can talk to at XYZ Corp," chances are one of these people can hook me up with a name and an introduction.</div>
<div>
<br /></div>
<div>
Further out at the edges of my network are the presenters I've met and watched at SQL Saturday, bloggers I've read, and the aforementioned SQL developers who tweet or lurk on the same hash tags as I do. I don't expect Tim Ford or Jeremiah Peschka or Brent Ozar know who I am... but I know them. (And I know people who know them, bwuahaha.) More importantly, I know they're the experts in their areas, and that they have a wealth of knowledge and materials that they generously share in talks and blogs. They're also active twitterers (tweeters?). If I had some question about DMO's, window functions in Denali, or how SANs work, I know where to go. </div>
<div>
<br /></div>
<div>
As you're making your New Year's resolutions next year, think about what you can do to increase the size of your SQL network. Also, think about becoming a nicer driver-- the person behind you might be closer than you think!</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-75874251505423774972011-09-23T13:00:00.000-07:002011-09-23T13:02:42.028-07:00America's Next Top (ER) Model: Plowed!<i>I've been doing a lot of modeling lately, and it's starting to affect my personal life. In today's article, I'll explore the personal story of how the stress of modeling found me getting plowed every day, and how I managed to stop.</i><br />
<br />
<span class="Apple-style-span" style="font-size: large;">A Day in the Life of a Model</span><br />
As I mentioned, I'm do a lot of data modelling these days. My team uses Visio for all of our ER diagrams. It works pretty well, and with the help of a free plug-in from <a href="http://www.orthogonalsoftware.com/toolboxinstallation.html">Orthogonal</a>, we can generate our initial DDL scripts right from the diagram.<br />
<br />
Here's where the stress of modeling starts:<br />
<br />
I have a nice ER diagram, all organized in a visually pleasing way:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgw9ouqzuQ7ZrKK_BGFwHQj0n95r5vtmGB8vBnul_vZNAr8dUQz2HcNhCVPZfglKF8NrMJ0xTWodUBEeDzdORmtwJM8o0lucqMybPssVp1gaIUKxuKGkB4M3__ub2IMFo9812igdZMKEqs/s1600/v1.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="161" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgw9ouqzuQ7ZrKK_BGFwHQj0n95r5vtmGB8vBnul_vZNAr8dUQz2HcNhCVPZfglKF8NrMJ0xTWodUBEeDzdORmtwJM8o0lucqMybPssVp1gaIUKxuKGkB4M3__ub2IMFo9812igdZMKEqs/s400/v1.bmp" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
.. and then I go to move one of the entities a little bit, and that's when it happens:</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtfn_PeM_E_YjclP701JVI2ZV3zCdMzIvS3i9mh27T3yB5Za9Hvzxs_DVln46XwpsnxaHMsVUsfC7e_BDtnD1XBjFnWd_aY6eVooNmiCTZRWEmiaC-Wb4u1dAaMDk2Cwcl6ONuUWo35Qk/s1600/v2.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="127" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtfn_PeM_E_YjclP701JVI2ZV3zCdMzIvS3i9mh27T3yB5Za9Hvzxs_DVln46XwpsnxaHMsVUsfC7e_BDtnD1XBjFnWd_aY6eVooNmiCTZRWEmiaC-Wb4u1dAaMDk2Cwcl6ONuUWo35Qk/s400/v2.bmp" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
A whole bunch of other entities scatter out of the way, often landing outside the page borders. Rounding up each of these stray sheep typically results in a few more sheep wandering off in random directions, and it's a long, frustrating day on the ranch.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
I'm not even sure how to begin Googling, because there isn't a good word for the behavior. I tried things like "visio object repel" or "visio objects rearrange" or "visio sucks sucks sucks sucks er diagrams". </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-size: large;">Getting Plowed</span></div>
<div class="separator" style="clear: both; text-align: left;">
In the end, I found it the old fashioned way: randomly clicking around until I found something that looked dodgy. It turns out, the behavior is called "plowing," and as usual, Microsoft is trying to help you. With plowing enabled, objects behave much like the girls did towards me in junior high school: they jump way out of the way as soon as you get anywhere near them.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
What's happening above is something I'd dubbed a "cascading plow" (mmm, cascading plow...). The entity I moved got close to another entity, which jumped ten feet in some random direction, where it in turn ended up perilously close to another entity, which jumped out of the way, and so on, and so on... </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
From what I can tell, there's two ways to turn this behavior off:</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-size: large;">Per Object</span></div>
<div class="separator" style="clear: both; text-align: left;">
Right-click any entity and choose Format, Behavior, and go to the Placement tab. Here you can set one of two properties: </div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<ul>
<li>"Move other shapes way on drop", which you want to set to "Plow no shapes"</li>
<li>"Do not allow other shapes to move this shape away on drop.</li>
</ul>
Which one you set is kind of a world-view thing:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzD4zd1Ep2fmQ_EcdeJ3arrR719ZHCotm7FYHo4Hjtzr8c1TTl4LjhKyaUbkyBWnQ0yNFj9mYn_UobDWCY5eHDDGd5McyuwEOqUbyrUX4krGrPnIagmrO_vLxxQT0tiKRqBy25NYZricg/s1600/eye_poke_defense.jpg" imageanchor="1"><img border="0" height="249" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzD4zd1Ep2fmQ_EcdeJ3arrR719ZHCotm7FYHo4Hjtzr8c1TTl4LjhKyaUbkyBWnQ0yNFj9mYn_UobDWCY5eHDDGd5McyuwEOqUbyrUX4krGrPnIagmrO_vLxxQT0tiKRqBy25NYZricg/s320/eye_poke_defense.jpg" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Do you stop Moe from poking everyone else in the eye, or do you teach everyone else to do the Nose Salute every time Moe comes at them? I suppose you could do both, but really I don't think Larry should have to always be on the lookout, so I set the first option to "Plow no shapes." (Nyuk nyuk nyuk)</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
With this enabled, you can now move your entity anywhere you like, and it won't push other entities around anymore. Of course, you'll run the "risk" of being able to have entities touch or even --gasp!-- overlap, but you're in control of that. Connectors (i.e. relationships) will continue to route themselves automatically, but I find Visio does this pretty well.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Of course, you'll have to set this property on each and every entity you add to your diagram.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span class="Apple-style-span" style="font-size: large;">Forever and Ever, Amen</span></div>
<div class="separator" style="clear: both; text-align: left;">
I don't really want to have to set the behavior on every entity from now until forever. Having fixed one of my existing ER diagrams, the Plow No Shapes behavior works perfectly, and I don't want to ever go back to bumper car mode.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
You can't officially modify the default stencils that ship with Visio (a stencil is the official name for a category of like controls-- when you select File --> Shapes, the little green window-pane items are stencils). So if you like, you can right-click Entity, and choose Add to My Shapes, and add it to your Favorites stencil or a new stencil. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Me, I'm fine with all the other controls in the Entity Relationship stencil, which opens by default with any new "Database Model Diagram". I just want to change the behavior of the standard Entity, forever. If you want to do that, you'll want to open up the standard Entity Relationship stencil, which on my system is located in c:\Program Files\Microsoft Office\Visio11\1033\ENTITY_U.VSS. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
With the stencil opened for editing (either your own or the standard ENTITY_U one), you can now right-click the Entity object, and choose Edit Master --> Edit Master Shape. Right-click the shape, Format, Behavior, Placement, Plow No Shapes, and save.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Now when you create new ER diagrams, the Entity object won't act like an ass anymore. You can move your entities around any way you like, and nothing with jump (or be plowed) out of the way. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
It's made my modelling life a whole lot easier, although I still have trouble with the four-inch heels.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-4404625651414760707.post-54881138669413258962011-04-24T15:41:00.000-07:002011-04-24T19:17:46.507-07:00Do You Know .NET?I got an email from a friend recently, saying:<br />
<br />
<br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px;"><tbody>
<tr><td style="font: inherit; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;" valign="top"><blockquote style="font-family: arial, sans-serif;">"As I search for my next situation, I stop when I see .NET as a requirement.</blockquote><blockquote style="font-family: arial, sans-serif;">So... what does it take to learn .NET?"</blockquote><div style="font-family: arial, sans-serif;">This turns out to be the same friend who inspired <a href="http://es-cue-el.blogspot.com/2010/08/can-left-join-restrict-your-resultset.html">this blog post</a>. I guess he just has a knack for asking blog-worthy questions.</div><div style="font-family: arial, sans-serif;"><br />
</div><div style="font-family: arial, sans-serif;">Like many people I know from my last job, this guy's what I'd call a technology generalist. He's spent most of his career supporting, troubleshooting, tweaking, and business-analyzing software. A lot of shops have people like this around: they know enough about a variety of technologies to move data around from spreadsheet to database, maybe a little scripting skills, enough SQL knowledge to write a query or ten. They're often the people who respond to the constant stream of ad-hoc queries coming from senior leadership or finance. </div><div style="font-family: arial, sans-serif;"><br />
</div><div style="font-family: arial, sans-serif;">As a database specialist and sometimes-programmer, I've dealt with this question myself. What does it mean to "know" .NET? I once tried to explain to a sales team what .NET was, and it just about killed us all. "Dot NET" as a marketing term has come to stand for as broad-- and as vague-- a set of technologies as "green" has: it may very well have a very specific meaning to the person using it, or it might just be that person's shorthand for "cutting edge" or "Microsoft". </div><div style="font-family: arial, sans-serif;"><br />
</div><div style="font-family: arial, sans-serif;"></div><div style="font-family: arial, sans-serif; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">However, if ".NET" is listed further down in the job description, I understand it to mean three things:</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"></div><ol><li>"We work primarily on MS Windows-based software"</li>
<li>"We use C# (probably), VB.NET (maybe), or some other .NET programming language"</li>
<li>"We're not using "old" technology like VB6, C++, Fortran, etc.</li>
</ol><br />
<br />
<div style="font-family: arial, sans-serif;">How much experience they expect you to have, and how much exposure to .NET languages the job actually has, can vary a lot.</div><div style="font-family: arial, sans-serif;"><br />
</div><div style="font-family: arial, sans-serif;">Here's how I approach it, and my advice to my friend: if the primary focus of the job is .NET <i>development</i>, look elsewhere. These jobs are for people who are computer programmers by trade. It's a little like the difference between a General Practitioner and a brain surgeon. A GP <i>could</i> operate on your brain in a pinch, and do a much better job than a non-doctor. But you're still probably better off with a mediocre brain surgeon than an exceptional GP.</div><div style="font-family: arial, sans-serif;"><br />
</div><br />
<div>The two answers I want to be able to give (honestly) in an interview are the following:</div><div><ol><li>I've worked with/supported/extended/etc programs that were written using .NET technologies-- I think it was mostly ASP.NET for the Web/UI layer, C# for business logic, and SQL Server on the back-end. </li>
<li>I've done a little C# programming recreationally-- not enough to consider myself a programmer, but I wrote a little web app to store recipes in a database, and I'm familiar enough with what a C# program looks like-- if I looked through someone's else's code, I could probably work out what it's doing.</li>
</ol><div>I want to establish right up front that I'm not a programmer, but that I have enough experience and professional curiosity to have dabbled in it a bit. </div></div><div><br />
</div><div>At this point, the employer should have enough to tick (or not tick) that requirement off the list. If they really need someone who can take over primary coding responsibilities, I'm not the guy, and I don't want to be. If they just need someone who can maybe maintain and tweak a couple little C# utilities that the last guy wrote, as 10% of the job duties, I can probably do that. It might take me ten times longer than a "real" programmer, but let's talk about how my skills fit the other 90% of the job.</div><div><br />
</div><div>So, how does someone like my friend get from "I get the idea that .NET is a framework" to being able to cover these two points? Here's my advice:</div><div><br />
</div><div>For the first answer, ask questions about the software you're working on-- even if you're just doing tech support, or gathering requirements, or whatever. Talk to the programmers and ask them what languages and technologies they're using, and why. Don't be afraid to ask stupid questions: programmers have a reputation for being brusque, but at some point they have to explain what they do to their spouses and moms. Ask them, "What exactly <i>is </i>Ruby on Rails anyway?" and the good ones should be able to give you some analogies at least, or tell you why it differs from other development languages. </div><div><br />
</div><div>Also, as long as you're willing to assume the "dumb guy" role, most people get a big ego boost expounding on their area of expertise to a newbie. It's why guys love explaining the finer points of football to an interested female (and why girls do so well asking, "Tell me again about the guys in the stripey outfits?") Programmers <i>love</i> being the smartest guy in the room, and you can get a lot of knowledge by starting questions with, "I understand you're the X guru here..."</div><div><br />
</div><div>Ask these questions, all the time, about whatever web site or app you're supporting. If you're no longer at the job, contact some former colleagues and ask them. The goal isn't to <i>learn</i> all these technologies-- just to be able to talk about them a little. Good sales guys do this all the time-- they may not know a rack from a pinion, but they want to be able to tell the average Joe why he wants one.</div><div><br />
</div><div>For the second answer, you'll have to work a little harder. You're going to want to teach yourself-- or have someone teach you-- C#. This seems to be the most common language in use out there, so it's probably the place to start, although VB.NET is a bit friendlier and will work just as well for this objective. If you have some familiarity with any kind of computer programming-- maybe you took a Fortran class way back when-- and you have some familiarity with variables and parameters, you've got a head start. If not, you'll be learning new concepts as well as a new language, so it might take longer.</div><div><br />
</div><div>Get yourself a copy of Microsoft Visual Studio Express <a href="http://www.microsoft.com/express/Windows/">here</a> (it's free!) and pick up a beginner's C# book-- lots of people seem to like the Head First series, like <a href="http://www.amazon.com/Head-First-2E-Real-World-Programming/dp/1449380344/ref=sr_1_4?s=books&ie=UTF8&qid=1303676377&sr=1-4">this one</a> on C#. A lot of these are broken down into one or two hour lessons, and if you dedicate a few nights a week to studying, you can learn the basics in a month or two. Alternatively, check with your local community college or continuing ed program. Most offer introductory classes. As you work through each exercise, make up little variants to test yourself: "What if I want my window to be red instead of blue?" "What if I want to let the user choose the window color?" </div><div><br />
</div><div>Make sure the book you use shows you how to interact with a database, and how to write a web application as well as a Windows/desktop one. This will force you to learn a little SQL, and understand how IIS and web sites work.</div><div><br />
</div><div>From there, it's kind of up to you. Maybe you'll get the bug and want to take some more classes on computer programming, or you'll decide you want to write the next Angry Birds and make your millions $3 at a time.</div><div><br />
</div><div>Most likely, you'll learn a little bit about programming-- much of which you'll forget-- and maybe pick up some skills you can use at work. I've found that the most useful thing to do, once you know a little of a language, is to challenge yourself to some business task: write a little application that looks through all the documents in folder for and reports how many times the word "mortgage" appears. You'll have to search the web to learn how to loop through files in a directory, how to open up various file types, how to search. These are the things a technology generalist often needs to be able to do with .NET: just complicated enough that you don't want to do it by hand, but simple enough to accomplish with 100 lines of code or less.</div><div><br />
</div><div>Remember, the goal here isn't to fake your way through a test, or to just learn enough "buzz words" to impress the interviewer (actually, that <i>is </i>the goal of the first answer). The goal here is to understand enough about C# to be able to use it to do some basic tasks for you. You want to be the GP who can do a sloppy brain surgery if you absolutely have to. When the job calls on someone to write a little routine that sends an email if the stock goes over $40 a share, you want to have just enough skills to Google "<a href="http://www.google.com/search?rlz=1C1AVSA_enUS422US422&sourceid=chrome&ie=UTF-8&q=c%23+monitor+stock+price#sclient=psy&hl=en&rlz=1C1AVSA_enUS422US422&source=hp&q=c%23+get+stock+price&aq=f&aqi=&aql=&oq=&pbx=1&bav=on.2,or.r_gc.r_pw.&fp=5ba78afa4617dfcf">C# get stock price</a>" and "<a href="http://www.google.com/search?rlz=1C1AVSA_enUS422US422&sourceid=chrome&ie=UTF-8&q=c%23+send+email">C# send email</a>" and be able to cobble the examples together to make something that gets the job done. Then you can tell the <i>next </i>interviewer, "I'm not a programmer, but once I had to write a little app to monitor the stock price and send an email, and it worked."</div><div><br />
</div><div>I can't stress this last point enough. People go to school for years to learn professional programming techniques, and then often spend years honing these skills on the job. And many of these people still might not describe themselves as really "knowing" .NET. You're not trying to become the next rockstar programmer in a month.</div><div><br />
</div><div>But, there's a ton of little tasks on the job that can be solved with a few lines of the right code. The powerful thing about .NET is that there are thousands of libraries out there to open spreadsheets, send emails, move files around, monitor RSS feeds, get the weather, and any other of a million little tasks, and there are millions of web sites and forum posts explaining how to use them. A tech generalist has to be a little like a librarian: you might not know everything, but you know how to find out. A business analyst, or tech support rep, or QA engineer who can cobble together a little .NET code when they have to is often a very valuable person to have on your team.</div><div><br />
</div><div>Now, go out and "learn" .NET!</div><div><br />
</div><div><br />
</div></td></tr>
</tbody></table>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-31696932513490973202011-03-22T18:46:00.000-07:002011-03-22T18:46:12.319-07:00Entity Framework and the Inheritance Problem[tl;dr Don't use EF's default TPT mapping if you care about... anything]<br />
<br />
A friend and I were having lunch, and I mentioned a recent session I'd attended at the last 24 Hours of PASS called "<a href="http://www.sqlpass.org/24hours/spring2011/Home/EntityFrameworkNotasEvilasYouMayThink.aspx">Entity Framework: Not as Evil as You May Think</a>". I talked about how much I hate ORM's and the hideous SQL that they generated. <br />
<br />
As it turns out, my friend had recently started using Entity Framework for a project he was working on. As it turns out, his was the kind of project that EF is great for: the database won't ever hold more than a few thousand records in a handful of tables, and the whole thing will run in SQL CE in a single-user database. In short, performance and scalability aren't huge concerns-- he just wants a basic data store.<br />
<br />
But he had a problem. During the initial population of the database, they might insert 1000 or more records into a certain table. This process was taking <i>minutes, </i>and he'd tracked it down to a single line of code that checked to see if the record was already in the database. A little snooping revealed that EF was generating a SQL statement that was <i>37,000 characters </i>long. He suspected that the parse and compile time alone accounted for the 1+ second each lookup took.<br />
<br />
My interest piqued, I had him take me back to his place and fire up Visual Studio so we could take a look. I've never actually used EF or SQL CE, and I wanted to watch it all in action. After fumbling around trying to remember how to see the query EF was generating (SQL CE doesn't have a Profiler), we found the ugly offender. It was a mess.<br />
<br />
What we found, as described in <a href="http://www.codeproject.com/Articles/66313/The-Entity-Framework-v1-and-v4-Deal-Breaker-TPT-In.aspx">this article</a>, is that Table-Per-Type inheritance is pretty much a nightmare in EF. (Microsoft claims this is a "high priority work item" for the next version of EF, so we'll see how that goes.)<br />
<br />
Let's take a look at how this goes. Suppose I have a base class, "Animal", and four classes which inherit from Animal: Dog, Cat, Tutle, and Fish. The Entity Model is shown here:<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNnX-WkKtpOaflSDwj-fb9NGESUDKMkNZxGPfyi4O6mwuZjxCUj2fkeSuv41KULwk1LXkGPndaBWNxUz_SX2b-dREKitniYDNaJtcLJMN9jSOfCxBwJ8TZ0_4aRDaLDyKoGUxRXUcrEo0/s1600/ef1.jpg" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="217" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNnX-WkKtpOaflSDwj-fb9NGESUDKMkNZxGPfyi4O6mwuZjxCUj2fkeSuv41KULwk1LXkGPndaBWNxUz_SX2b-dREKitniYDNaJtcLJMN9jSOfCxBwJ8TZ0_4aRDaLDyKoGUxRXUcrEo0/s400/ef1.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">(click to enlarge)</td></tr>
</tbody></table>When you generate a database from this model, you get a SQL table for each class (hence, Table Per Type, duh), with foreign keys joining Cat, Dog, etc to Animal. So far so good.<br />
<br />
Now let's run a simple line of code to check whether an Animal named "Bob" already exists:<br />
<br />
Animal a= animalContext.Animals1.FirstOrDefault<animal>(Animal => Animal.Name == "Bob");</animal><br />
<br />
And, let's see what EF came up with (sorry, you'll need to page down a few times):<br />
<br />
<br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">SELECT </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Limit1].[C1] AS [C1], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Limit1].[Id] AS [Id], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Limit1].[Name] AS [Name], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Limit1].[Age] AS [Age], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Limit1].[C2] AS [C2], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Limit1].[C3] AS [C3], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Limit1].[C4] AS [C4], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Limit1].[C5] AS [C5]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">FROM ( SELECT TOP (1) </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent1].[Id] AS [Id], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent1].[Name] AS [Name], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent1].[Age] AS [Age], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CASE WHEN (( NOT (([UnionAll3].[C6] = 1) AND ([UnionAll3].[C6] IS NOT NULL))) AND ( NOT (([UnionAll3].[C7] = 1) AND ([UnionAll3].[C7] IS NOT NULL))) AND ( NOT (([UnionAll3].[C8] = 1) AND ([UnionAll3].[C8] IS NOT NULL))) AND ( NOT (([UnionAll3].[C9] = 1) AND ([UnionAll3].[C9] IS NOT NULL)))) THEN '0X' WHEN (([UnionAll3].[C6] = 1) AND ([UnionAll3].[C6] IS NOT NULL)) THEN '0X0X' WHEN (([UnionAll3].[C7] = 1) AND ([UnionAll3].[C7] IS NOT NULL)) THEN '0X1X' WHEN (([UnionAll3].[C8] = 1) AND ([UnionAll3].[C8] IS NOT NULL)) THEN '0X2X' ELSE '0X3X' END AS [C1], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CASE WHEN (( NOT (([UnionAll3].[C6] = 1) AND ([UnionAll3].[C6] IS NOT NULL))) AND ( NOT (([UnionAll3].[C7] = 1) AND ([UnionAll3].[C7] IS NOT NULL))) AND ( NOT (([UnionAll3].[C8] = 1) AND ([UnionAll3].[C8] IS NOT NULL))) AND ( NOT (([UnionAll3].[C9] = 1) AND ([UnionAll3].[C9] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C6] = 1) AND ([UnionAll3].[C6] IS NOT NULL)) THEN [UnionAll3].[C2] WHEN (([UnionAll3].[C7] = 1) AND ([UnionAll3].[C7] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C8] = 1) AND ([UnionAll3].[C8] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) END AS [C2], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CASE WHEN (( NOT (([UnionAll3].[C6] = 1) AND ([UnionAll3].[C6] IS NOT NULL))) AND ( NOT (([UnionAll3].[C7] = 1) AND ([UnionAll3].[C7] IS NOT NULL))) AND ( NOT (([UnionAll3].[C8] = 1) AND ([UnionAll3].[C8] IS NOT NULL))) AND ( NOT (([UnionAll3].[C9] = 1) AND ([UnionAll3].[C9] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C6] = 1) AND ([UnionAll3].[C6] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C7] = 1) AND ([UnionAll3].[C7] IS NOT NULL)) THEN [UnionAll3].[C3] WHEN (([UnionAll3].[C8] = 1) AND ([UnionAll3].[C8] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) END AS [C3], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CASE WHEN (( NOT (([UnionAll3].[C6] = 1) AND ([UnionAll3].[C6] IS NOT NULL))) AND ( NOT (([UnionAll3].[C7] = 1) AND ([UnionAll3].[C7] IS NOT NULL))) AND ( NOT (([UnionAll3].[C8] = 1) AND ([UnionAll3].[C8] IS NOT NULL))) AND ( NOT (([UnionAll3].[C9] = 1) AND ([UnionAll3].[C9] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C6] = 1) AND ([UnionAll3].[C6] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C7] = 1) AND ([UnionAll3].[C7] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C8] = 1) AND ([UnionAll3].[C8] IS NOT NULL)) THEN [UnionAll3].[C4] END AS [C4], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CASE WHEN (( NOT (([UnionAll3].[C6] = 1) AND ([UnionAll3].[C6] IS NOT NULL))) AND ( NOT (([UnionAll3].[C7] = 1) AND ([UnionAll3].[C7] IS NOT NULL))) AND ( NOT (([UnionAll3].[C8] = 1) AND ([UnionAll3].[C8] IS NOT NULL))) AND ( NOT (([UnionAll3].[C9] = 1) AND ([UnionAll3].[C9] IS NOT NULL)))) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C6] = 1) AND ([UnionAll3].[C6] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C7] = 1) AND ([UnionAll3].[C7] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([UnionAll3].[C8] = 1) AND ([UnionAll3].[C8] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) ELSE [UnionAll3].[C5] END AS [C5]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM [dbo].[Animals1] AS [Extent1]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>LEFT OUTER JOIN (SELECT </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll2].[C1] AS [C1], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll2].[C2] AS [C2], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll2].[C3] AS [C3], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll2].[C4] AS [C4], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll2].[C5] AS [C5], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll2].[C6] AS [C6], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll2].[C7] AS [C7], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll2].[C8] AS [C8], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll2].[C9] AS [C9]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM (SELECT </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll1].[Id] AS [C1], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll1].[IsClawed] AS [C2], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll1].[C1] AS [C3], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll1].[C2] AS [C4], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll1].[C3] AS [C5], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll1].[C4] AS [C6], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll1].[C5] AS [C7], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll1].[C6] AS [C8], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[UnionAll1].[C7] AS [C9]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM (SELECT </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent2].[Id] AS [Id], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent2].[IsClawed] AS [IsClawed], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C1], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C2], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C3], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(1 as bit) AS [C4], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C5], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C6], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C7]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM [dbo].[Animals1_Cat] AS [Extent2]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>UNION ALL</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent3].[Id] AS [Id], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C1], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C2], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C3], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent3].[FreshWater] AS [FreshWater], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C4], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C5], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C6], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(1 as bit) AS [C7]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM [dbo].[Animals1_Fish] AS [Extent3]) AS [UnionAll1]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>UNION ALL</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent4].[Id] AS [Id], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C1], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent4].[IsHouseTrained] AS [IsHouseTrained], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C2], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C3], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C4], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(1 as bit) AS [C5], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C6], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C7]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM [dbo].[Animals1_Dog] AS [Extent4]) AS [UnionAll2]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>UNION ALL</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>SELECT </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent5].[Id] AS [Id], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C1], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C2], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>[Extent5].[ShellColor] AS [ShellColor], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(NULL AS varchar(1)) AS [C3], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C4], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C5], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(1 as bit) AS [C6], </span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>cast(0 as bit) AS [C7]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>FROM [dbo].[Animals1_Turtle] AS [Extent5]) AS [UnionAll3] ON [Extent1].[Id] = [UnionAll3].[C1]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;"><span class="Apple-tab-span" style="white-space: pre;"> </span>WHERE N'Bob' = [Extent1].[Name]</span><br />
<span class="Apple-style-span" style="color: blue; font-size: xx-small;">) AS [Limit1]</span><br />
<br />
Notice the goofy kind-of-bit-mapped discriminator columns C4-C7. Clever, I'm sure. On my friend's database, there were five derived classes, and lots more columns, resulting in the famed 37kb SQL statement. And, of course, EF is going to use a beast similar to this for any table access to any of the base or derived classes. <br />
<br />
This, it turns out, is how Entity Framework handles a simple inheritance with TPT. Neither my friend or I are experts on EF, but there doesn't appear to be a workaround here, without manually hacking the hell out of the mapping files-- which, as an added benefit, seems to make the graphical model designer in VS choke. <br />
<br />
Here, I think, is the best way to work around this for now. Luckily, there's no legacy databases to worry about, so redesigning the table structure is an option.<br />
<br />
1. Download and install the <a href="http://blogs.msdn.com/b/adonet/archive/2010/02/08/entity-designer-database-generation-power-pack.aspx">Entity Designer Database Generation Power Pack</a> (EDDGPP to its friends).<br />
2. Restart Visual Studio<br />
3. Now in the entity model properties, you'll find a whole lot of new xaml files installed under the Database Generation Workflow property, as shown below.<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNFHnkTGHeI8sfAbY6b9PO9vYGRf_vzJZUpKk5DS7hgG30fdWufMxqFV0WNRPfs1Bx6KlTnPsvSh7__ZV-NZNxaMcPc_bFzAdSVkMkDiAsmvmheeCgKU0Or_qtAHnxJBasp54atVlVapU/s1600/ef2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="52" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNFHnkTGHeI8sfAbY6b9PO9vYGRf_vzJZUpKk5DS7hgG30fdWufMxqFV0WNRPfs1Bx6KlTnPsvSh7__ZV-NZNxaMcPc_bFzAdSVkMkDiAsmvmheeCgKU0Or_qtAHnxJBasp54atVlVapU/s400/ef2.jpg" width="400" /></a></div>4. Choose "Generate T-SQL Via T4 (TPH).xaml (VS)<br />
5. Re-generate the database from the model.<br />
<br />
This creates a single table for Animal (TPH stans for Table-Per-Hierarchy), with all the additional attributes from Dog, Cat, etc added as additional columns in Animal. EF also automatically generates a discriminator column in Animal, which is not mapped to a property in the object model.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAWaLu50InWXNVgyR9A-LvIUIQh1yGZtSCWBptKAvuV-pBFAkWhzwerI7lErRW7FLvIWAaDfaEHsPgq6rk7ivjvxEGiGtjJrpV33ksY3xwxUsJIFxki0ZOh8Awy6FImxtkZtMLk3eEZ44/s1600/ef3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAWaLu50InWXNVgyR9A-LvIUIQh1yGZtSCWBptKAvuV-pBFAkWhzwerI7lErRW7FLvIWAaDfaEHsPgq6rk7ivjvxEGiGtjJrpV33ksY3xwxUsJIFxki0ZOh8Awy6FImxtkZtMLk3eEZ44/s1600/ef3.jpg" /></a></div><br />
There's a couple of caveats here: <br />
<br />
<ol><li>Each property in each of the classes needs to be mapped as nullable, or have a default value defined. (Which makes sense: Fish won't have an IsClawed property, so we need a valid value for that column in SQL).</li>
<li>Each property in all the classes-- both base and derived-- need to be unique (which should be self-evident from the table structure).</li>
</ol><div>With that all done, the object model is exactly the same as it was using TPT mapping, but the SQL is a LOT cleaner. Here's our code again:</div><div><br />
</div><div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Animal a= animalContext.Animals1.FirstOrDefault<animal>(Animal => Animal.Name == "Bob");</animal></div></div><div><br />
</div><div>And here's the SQL generated:</div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;"><br />
</span></div><div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">SELECT TOP (1) </span></div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Extent1].[__Disc__] AS [__Disc__], </span></div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Extent1].[Id] AS [Id], </span></div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Extent1].[Name] AS [Name], </span></div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Extent1].[Age] AS [Age], </span></div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Extent1].[IsClawed] AS [IsClawed], </span></div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Extent1].[IsHouseTrained] AS [IsHouseTrained], </span></div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Extent1].[ShellColor] AS [ShellColor], </span></div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">[Extent1].[FreshWater] AS [FreshWater]</span></div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">FROM [dbo].[Animals1] AS [Extent1]</span></div><div><span class="Apple-style-span" style="color: blue; font-size: xx-small;">WHERE ([Extent1].[__Disc__] IN ('Animal','Cat','Dog','Turtle','Fish')) AND (N'Bob' = [Extent1].[Name])</span></div></div><div><br />
</div><div>That makes a lot more sense all around, and I'm guessing it will perform a ton better. The drawback (if it's really a drawback) is that now all animals are stored in one table, which may not be desirable in some situations, and of course we had to redesign the database, which could be a bit of an issue if there's existing data in the TPT tables.</div><div><br />
</div><div>So, is Entity Framework evil? Not always: in most cases, the default EF mappings were fine for my friend, and as a developer-not-a-DBA, he's able to load and persist objects with ease. But, God help you when the EF magic decides to do something truly ponderous like this to persist a pretty simple object model.</div>Unknownnoreply@blogger.com9tag:blogger.com,1999:blog-4404625651414760707.post-29259457961059679632011-02-24T04:13:00.000-08:002011-02-24T04:17:22.991-08:00A SQL PoemOne of the jobs I recently applied for asked for a SQL poem as part of the application. I love stuff like this so I took a little time and came up with the following. (This actually did happen to me once...)<br />
<br />
I didn't originally title it, but I think I'll call it...<br />
<br />
<div style="text-align: center;"><span id="internal-source-marker_0.8176219316665083" style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">"Wait... STAT!" by S</span></div><div style="text-align: center;"><br />
</div><div style="background-color: transparent; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><div style="text-align: center;"><span id="internal-source-marker_0.8176219316665083" style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">The week was starting bad for Joe</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">His app was running way too slow.</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">He summoned Steve, from regions nether</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">To patch performance back together.</span></div><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"></span><br />
<div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">“Well, here’s your problem-- clear as Windex:</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">You’ve got a missing clustered index.</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">This calls for not just any geek; we'll</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Need one who speaks fluent SQL."</span></div><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"></span><br />
<div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Functions, unctions, query plans:</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">"We'll need more seeks, and fewer scans.</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">And then, to get some extra speed, yer</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Gonna need a stored procedure."</span></div><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;"></span><br />
<div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Steve worked fast, with mumbled zeal,</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Declared, "Success! Just watch 'er squeal!"</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">Said Joe, "While I applaud your fervor,</span></div><div style="text-align: center;"><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;">My problem's on this other server."</span></div></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-47045569066543586452011-02-15T16:47:00.000-08:002011-02-15T16:47:53.928-08:00Google Interview Question: "Explain a database in three sentences to your eight-year-old nephew."<div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">[Reposted from my non-SQL blog. Apologies to the handful of readers who follow both.]</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">From this list of <a href="http://blog.seattleinterviewcoach.com/2009/02/140-google-interview-questions.html">Google interview questions</a>, I found the question: "Explain a database in three sentences to your eight year old nephew." Here's my attempts.</div></div><div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div></div><div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><b>Attempt 1: Trying to Capture Third Normal Form</b></div></div><div><ol><li>On a sheet of notebook paper, make a list of all your video game systems, and number them 1 through whatever.</li>
<li>On another sheet of paper, make a list of all your video games, also numbered 1 through whatever.</li>
<li>For each video game, find the game system that it goes with on the first list, and write the number from the first list next to the game on the second list.</li>
</ol></div><div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><b>Attempt 2: Eight-year-olds Are Still Kinda Into Santa</b></div></div><div><ol><li>Santa has a list of every kid in the world, which includes the kid's name, address, age, and naughty/nice score.</li>
<li>Santa has another list of every toy in the world-- how much it costs, what color it is, how much it weighs (for the sleigh).</li>
<li>Santa has a special "many-to-many" list with just the kid-number and toy-number, to keep track of which kids want which toys, so it's really easy to figure out things like how many of each toy he needs to buy.</li>
</ol></div><div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><b>Attempt 3: They Learn This Stuff in the Womb, I Swear</b></div></div><div><ol><li>You know how you implemented MongoDB for the social networking site you wrote last year, after the metadata structure got sufficiently gnarly for a simple key/value store, and users were demanding the ability to search on any attribute?</li>
<li>Well, when I was a kid, we had these things called Relational Database Management Systems, which were really good at enforcing relational integrity (which was important because the computers still depended on algorithms instead of magic like they do today) but you had to know a thing or two about relational data design, and the RDMS got kind of slow when everyone on the planet started uploading holographic 3D videos of what they had for lunch every day.</li>
<li>Now go get your Uncle S a beer.</li>
</ol></div><div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div></div><div></div>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-4404625651414760707.post-36083645959109329582011-02-11T04:08:00.000-08:002011-02-11T04:47:10.598-08:00SQL Saturday "Action Items"<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzRQs-PMRM4l3LpfV3KIAGNabGlOfzqIrzab_9wBviMV-r8zTkYtfuR3UhGQ-psf8BWzT7xKNebdcFiIVsvCa5MiWmxWCJP5N9jNJCOKKNBGQR6MugZJgBPYd7tYWZu-yAL8VSUACyTbM/s1600/Cerealizable2-300x213.gif" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzRQs-PMRM4l3LpfV3KIAGNabGlOfzqIrzab_9wBviMV-r8zTkYtfuR3UhGQ-psf8BWzT7xKNebdcFiIVsvCa5MiWmxWCJP5N9jNJCOKKNBGQR6MugZJgBPYd7tYWZu-yAL8VSUACyTbM/s1600/Cerealizable2-300x213.gif" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Kendra Little's "Cerealizable" Isolation Level</td></tr>
</tbody></table><a href="http://www.sqlsaturday.com/60/schedule.aspx">SQL Saturday #60</a> was a huge success! As a volunteer, I'm proud to be part of a team that put on a great event. As an attendee, I'm kind of blown away at the quality of the event, especially knowing that it was all donated time and money. (None of the speakers were paid, and many actually <i>flew to Cleveland </i>in February on their own dimes! You guys rock!)<br />
<br />
The best thing about going to events like SQL Saturday and the <a href="http://www.sqlpass.org/summit/2011/">PASS Summit</a> is that you get to meet and hear all these fantastic speakers. (The worst thing is, there's never time to meet and listen to all of them!) This week's new "discoveries" for me: <a href="http://facility9.com/">Jeremiah Peschka</a>, <a href="http://sqlblog.com/blogs/aaron_bertrand/">Aaron Bertrand</a> (although I'm a huge fan of Aaron's "<a href="http://sqlblog.com/blogs/aaron_bertrand/archive/tags/bad+habits/default.aspx">Bad Habits</a>" series already), <a href="http://www.littlekendra.com/">Kendra Little</a>, and <a href="http://thomaslarock.com/">Tom LaRock</a>. All of these folks are wicked smaht and fun to listen to, and Kendra even draws groovy little cartoon drawrings like the one I shared here. <br />
<br />
As usual, I went home with visions of new SQL tools and techniques dancing in my head. Here's some things on my "to investigate" list:<br />
<br />
<ul><li><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=60&sessionid=3143">Erin Stellato</a> introduced me to <a href="http://www.scalesql.com/cleartrace/">ClearTrace</a>!!! Die, die, read80trace!!! Okay, that's not fair-- read80trace has helped me through many performance audits, but it's kind of unruly to use. ClearTrace looks like the answer to a SQL Profiler-analyzer's dreams. Must learn it.</li>
<li>About everyone made it clear that Twitter is not just for posting your breakfast intentions anymore. Brent Ozar wrote a <a href="http://www.brentozar.com/twitter/">little book</a> to help old folks like me catch up. I actually joined twitter last night, and I feel more connected already, at least to the SQL community.</li>
<li><a href="http://dbasurvivor.com/">Tom LaRock</a> finally explained waitstats in a way that made sense. I'm vowing to finally start looking at waitstats to diagnose performance issues.</li>
<li>Kendra Little got me excited about <a href="http://www.littlekendra.com/2011/02/08/isoposter/">snapshot isolation level</a> again. I thought it was about the coolest thing ever when it came out, but then never had the time or load test facilities to try it out. I'm still convinced this will provide huge performance boosts to clients of my former employer, where blocking and deadlocking reign.</li>
<li>Lastly, I'm going to get on the schedule of my local <a href="http://www.ohionorthsqlserverug.org/">SQL user group</a> to present on some SQL topic. Giving SQL presentations was my favorite part of my last job, and seeing all these great presenters has me itching to get back on the other side of the lectern again.</li>
</ul><div>That should be enough to keep me busy for a while. </div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-48975739007482204922011-02-09T18:57:00.000-08:002011-02-09T18:59:14.178-08:00An Encryption ProblemHere's the scenario: we have a SQL Server database that until recently stored sensitive information in plaintext (yikes!) And we have an Access 2003 database in which we built a custom little front-end, with linked tables to the SQL database. The vendor recently encrypted some of the fields in the database (thanks!) and now we need to update the Access database to comply.<br />
<div><br />
</div><div>The documentation from the vendor provided a basic example of accessing the encrypted information:</div><div><br />
</div><div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">OPEN SYMMETRIC KEY APPKEY DECRYPTION BY CERTIFICATE APPCERT</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">SELECT CONVERT(VARCHAR,DECRYPTBYKEY(CUSTOMER.SSN)) AS SSN FROM CUSTOMER</div><div class="MsoNormal" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
That should be a pretty easy change in most code-- just open the key, wrap the encrypted field in a couple of functions, and you're good to go. But linked tables in Access present an interesting problem. I'll spare you the laundry list of things I tried, but suffice it to say that lots of approaches <i>almost </i>worked, but nothing quite let me get the key open before calling DecryptByKey.<br />
<br />
What saved me was a little function called DecryptByKeyAutoCert, which extracts the private key from the cert and uses it to decrypt the value, all in one fell swoop. Without the need to open the key separately, I could create a SQL view, and use INSTEAD OF triggers for the updates. With baited breath, I linked my new view in Access... and it worked!<br />
<br />
In short, here's the SQL that makes it all possible:<br />
<blockquote>create view my_customer as<br />
select customerid, fname, lname, convert(varchar(12),DecryptByKeyAutoCert(cert_id('APPCERT'),null,customer.ssn)) as ssn<br />
from customer</blockquote><br />
<blockquote>create trigger trg_my_customer_upd on my_customer<br />
instead of update as<br />
begin<br />
open symmetric key APPKEY decryption by certificate APPCERT<br />
update customer set fname=i.fname, lname=i.lname, ssn=EncryptByKey(key_guid('APPKEY'),i.ssn)<br />
from inserted i where i.customerid=customer.customerid<br />
close symmetric key APPKEY<br />
end</blockquote><br />
<blockquote>create trigger trg_my_customer_ins on my_customer<br />
instead of insert as<br />
begin<br />
open symmetric key APPKEY decryption by certificate APPCERT<br />
insert into customer (customerid, fname, lname, ssn)<br />
select customerid, fname, lname, EncryptByKey(key_guid('APPKEY'),ssn)<br />
from inserted<br />
close symmetric key APPKEY<br />
end</blockquote>With this view created, we can now issue simple SELECT, UPDATE, and INSERT queries against my_customer, treating the ssn column as a plaintext column. The view decrypts the data as it comes into the resultset, and the triggers ecrypt the data as it goes back into the database. In short, the my_customer view now acts just like the original customer table.<br />
<br />
All we had to do then was to delete the original linked table in Access, link to the new view, and rename the linked table to the original name. No need to rewrite any other logic in our mini-application! <br />
<br />
I simplified a few things here, and in our case the cert was created without a password, but it's pretty easy to add that in if you need do. </div></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-23883456584698896732010-12-14T12:33:00.000-08:002010-12-14T12:33:54.830-08:00SQL Saturday: A Free Event for SQL GeeksI'm not sure if anyone reads or subscribes to this blog, but if you do, here's an event to put on your calendar.<br />
<br />
SQL Saturday is a free event in Brunswick, OH (just Southwest of Cleveland), and will feature 36 sessions on SQL and SQL-related topics. Speakers range from local professionals to national Microsoft MVP's who have written for popular SQL magazines and presented at national conferences like PASS.<br />
<br />
<a href="http://www.sqlsaturday.com/60/eventhome.aspx"></a><a href="http://www.sqlsaturday.com/60/schedule.aspx">http://www.sqlsaturday.com/60/schedule.aspx</a> <br />
<br />
Yours truly is on the organizing committee and is organizing the food and drink, so you know at least the eatin' will be good. I'm pretty excited about the roster of sessions that we're lining up, and it should be a good geeky day.<br />
<br />
<a href="http://www.sqlsaturday.com/60/register.aspx">Register here</a> if you plan on coming, or if you're a kind soul who is able to volunteer or if you or your company are able to sponsor.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-54244761527674359422010-08-19T07:18:00.000-07:002010-08-19T07:54:12.159-07:00Sorting Text Fields NumericallyJust a little tidbit that I came across this morning, involving sorting fields that have numeric data but in a text field.<br /><br />In this instance, I'm using a cursor to loop through my tables and run sp_spaceused on them, and then put the results into a temporary table:<br /><blockquote><pre>insert into #TableSizes exec sp_spaceused @objname = @name;</pre></blockquote><br />The problem is, when I select from the temp table:<br /><blockquote><pre><br />select [Table Name]<br />, [Number of Rows]<br />, [Reserved Space]<br />, [Data Space]<br />, [Index Size]<br />, [Unused Space]<br />from [#TableSizes]<br />order by [Reserved Space] desc<br /></pre></blockquote><br />the Reserved Space column isn't sorted correctly. I end up with something like this:<br /><table border="1"><br /><tbody><tr><td>Table Name</td><td>Number of Rows</td><td>Reserved Space</td></tr><br /><tr><td>TableD</td><td>60</td><td>96 KB</td></tr><br /><tr><td>TableB</td><td>587</td><td>952 KB</td></tr><br /><tr><td>TableA</td><td>111151</td><td>8728 KB</td></tr><br /><tr><td>TableC</td><td>665290</td><td>8717752 KB</td></tr><br /></tbody></table><br />Because Reserved Space is a varchar column, it sorts as text, based on the first character first. So 9's sort to the top, even small numbers like 96.<br /><br />The correct solution is probably to trim off the " KB" from the end, cast to an integer, and sort by that:<br /><blockquote><pre><br />order by cast(left([Reserved Space],len([Reserved Space])-3) as int) desc<br /></pre></blockquote><br />which then also allows you to use SUM, etc. on the column.<br /><br />I was in a hurry and didn't want to mess around with all the parenthesis, and came up with a quick and dirty solution:<br /><blockquote><pre><br />order by len([Reserved Space]) desc,[Reserved Space] desc<br /></pre></blockquote><br />This sorts the longest strings (hence the biggest numbers) to the top of the list, and then sorts each string alphabetically from 9 to 0. <br /><br />Not something you'll need to do every day, but I thought it was a neat solution.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-49488548315553153622010-08-12T17:47:00.000-07:002010-08-13T10:23:28.991-07:00It's the Color of the Sky, See?<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNBB-dYF_WlM6H79cVStMyLcuzPyy8o1V2LB4Cb7rDeo_dePN4jc7nBni03MN1Q3bylvawS4_MevIG2dYRoAQONyyIQZLLXy_A_nqwqDvIPQ-7akCSSFnrzd0Irhr6iqpBIr9UPFfopcM/s1600/250px-Judea_2_by_David_Shankbone.jpg"><img style="float: left; margin: 0pt 10px 10px 0pt; cursor: pointer; width: 250px; height: 188px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNBB-dYF_WlM6H79cVStMyLcuzPyy8o1V2LB4Cb7rDeo_dePN4jc7nBni03MN1Q3bylvawS4_MevIG2dYRoAQONyyIQZLLXy_A_nqwqDvIPQ-7akCSSFnrzd0Irhr6iqpBIr9UPFfopcM/s320/250px-Judea_2_by_David_Shankbone.jpg" alt="" id="BLOGGER_PHOTO_ID_5504912295941315522" border="0" /></a><br />"I want to know where my data is, <span style="font-style: italic;">physically</span>!" This was the repeated demand of the gentleman sitting next to me at last night's SQL SIG talk on <a href="http://www.microsoft.com/windowsazure/sqlazure/">SQL Azure</a>-- Microsoft's new cloud-based RDBMS. (Of equal concern was, once his data was inevitably lost or compromised, "Who do I sue???") The pitch was so perfect, he could have been a plant-- those of us who were skeptical about data in the cloud, having just heard our prejudice given an angry voice, were suddenly defending it.<br /><br />Still, it's easy to understand his frustration. In the cloud-heavy world of NetFlix, Rhapsody, and Amazon Kindles, an increasing percent of the media and information in our private lives isn't on our bookshelves-- it's <span style="font-style: italic;">out there</span> somewhere. I used to buy CDs and DVDs obsessively, but now spend $9 a month to access a vastly larger collection online. Like the old chestnut about beer, you don't <span style="font-style: italic;">buy</span> media anymore, you <span style="font-style: italic;">rent</span> it.<br /><br />Microsoft wants to turn your SQL Server into such a commodity, and they have a pretty good start on it. SQL Azure is part of the <a href="http://www.microsoft.com/windowsazure/">Windows Azure</a> platform, that lets you host web applications in the cloud. What's appealing is that the interfaces for both managing and programming SQL Azure are the ones you're used to: you can connect SQL Management Studio and BIDS (at least the latest versions) to your Azure server and they behave exactly the same as they would with your real SQL Server, at least in most cases. Connecting to an Azure database with ADO.NET is transparent --but for the connection string-- and you use the same Connection and Command objects.<br /><br />In short, once you've set up your Azure server and database, you can almost forget that the data's in the cloud and not on your local server. Almost. Context switching ("USE Database2") and three-part naming ("select * from database2.dbo.mytable") don't work-- you have to stay in your own database. Connections have some new behaviors and errors to respond to connectivity issues and other throttles-- Azure kills transactions that run longer than 5 minutes, for example. But for smaller, non-complicated apps, it's pretty smooth.<br /><br />So where <span style="font-style: italic;">is</span> your data, physically? When you sign up for an Azure account, you pick a geographic location (currently pretty large regions) and Microsoft holds your database on a farm of servers across multiple locations, with replication handled automatically. Essentially your server becomes a black box to you-- your traffic will be routed through load balancers to the right place, and server resources can be added instantly as activity grows and shrinks.<br /><br />How about performance? Here's where your application architecture may need to change. If you have a stateless web app that's already designed to live in a web farm nicely, you're probably set-- host your web application on Windows Azure servers, and set some affinity settings to make sure your app and database servers in the cloud have good connectivity. This is called Near Computing, because your business logic is close to the database.<br /><br />If you have more of a two-tier app, you'll need to adjust to the increased latency of accessing data in the cloud, and some of the new behaviors mentioned above. Depending on how chatty your app is with the database server, this isn't trivial. If you're already optimized for poor network latency (say, if your database server is across a wan from the application), it may not be that much different. <br /><br />There's also some hybrid methods to sync your local SQL database with an Azure database, asynchronously, so you can migrate parts of your app to the cloud and leave others local where it makes sense. This sounds like a bit of a nightmare but might be a good choice for broad applications that might benefit from moving certain functions into the cloud.<br /><br />Certainly, if your application is entirely local, connected, and speed-sensitive (say, an inventory and routing app where the users are inside the warehouse and connected directly to the data at all times), moving to the cloud might not be a fit. But for bringing small apps online quickly, and particularly when your users are already geographically scattered, it's a tempting architecture.<br /><br />Licensing plans are still fairly immature, and more complicated features like service broker haven't been implemented yet, but Microsoft is pretty excited about this, and they're certainly not the only ones with their head in the Cloud.<br /><br />If I were building a new app from scratch today, I'd give Azure a good solid look-- and I might also have my lawyer figure out who to sue.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-11770823600423381432010-08-04T12:31:00.000-07:002010-08-04T12:38:35.098-07:00Summer ReadingEs Cue El is going on vacation to beautiful Toronto, Ontario for the weekend, so no blog posts unless Toronto is really boring.<br /><br />Your weekend reading assignment: Aaron Bertrand's classic "<a href="http://sqlblog.com/blogs/aaron_bertrand/archive/tags/habits/default.aspx">Bad Habits To Kick</a>" series. There's lots of good gems in here, and most of them are things (like always terminating statements with semicolons) that you can easily add to your finger memory with a couple days' effort.<br /><br />See you Monday!Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-67270691785703940352010-08-04T07:22:00.001-07:002010-08-23T11:48:56.190-07:00Oracle XE<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimmhbg29WsNCQGwI-saJFn0IOXewG3CxjZE0fX50NIHRjK5DjwwnyUGy_cinWInqTUwshXaB4Mf07VgS0yesj68GCPoPlBSf9pfRPYd5W3Ro1TuuOZW5Od2cVc0MugSW7L7xxoDvDFWp8/s1600/oracle.jpg"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 320px; height: 246px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimmhbg29WsNCQGwI-saJFn0IOXewG3CxjZE0fX50NIHRjK5DjwwnyUGy_cinWInqTUwshXaB4Mf07VgS0yesj68GCPoPlBSf9pfRPYd5W3Ro1TuuOZW5Od2cVc0MugSW7L7xxoDvDFWp8/s320/oracle.jpg" alt="" id="BLOGGER_PHOTO_ID_5501560100206132178" border="0" /></a>If you're looking to get your feet wet in the world of Oracle, then <a href="http://www.oracle.com/technetwork/database/express-edition/overview/index.html">Oracle XE</a> is for you.<br /><br />Somewhat comparable to SQL Server Express Edition, Oracle 10g XE is the free version of Oracle-- free to develop, free to deploy, free to distribute. It's limited to one database per machine, 4 GB per database, 1 GB of memory, and one processor (you can install it on multi-processor machines but it will only use one).<br /><br />Combined with <a href="http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html">Oracle Application Express</a> (APEX) -- a free toolkit for building applications, including web-based user interfaces, reporting, security, and dashboards -- you can actually build little web-based database applications pretty quickly without much learning.<br /><br />I think the hope is that smaller organizations will consider using it for internal applications and then grow into the more expensive versions. I'm not sure how likely that is, but I am working with a friend to build a small inventory/billing app for her small business, and I was going to use MS Access just because it's fast and familiar. After installing Oracle XE, I'm considering using it, although admittedly I'd be doing so just as an excuse to learn a new skillset.<br /><br />Oracle has a reputation of being somewhat of a monster to install, and if you've ever read anything about the Universal Installer, you may think you need a PhD just to get it installed. Oracle XE and APEX are quick installs that ask you a handful of questions (install path, etc) and for the most part you can sail right through with the defaults. I had to change the default port and still don't quite know why, but even that was easy-- the installer told me that 8080 was in use, so I tried 8081, then 8082, until I found one that was open. That was the biggest glitch I ran into.<br /><br />The other great thing about the XE/APEX is the tutorials available on both web sites. Oracle has created a "2 Day Developer Guide" that walks you through the tools, basic PL/SQL, and everything you need to know to get a quick and dirty app up. There's also some great tutorials.<br /><br />I don't expect this will make anyone an expert at Oracle, but for SQL Server types like myself looking to teach themselves PL/SQL and maybe create a little sample app, you can at least get yourself some exposure to the world of Oracle without a lot of hassle.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-53172059503439630362010-08-03T07:14:00.000-07:002010-08-03T10:40:16.912-07:00Can a LEFT JOIN restrict your resultset?A colleague came to me the other day with this question: "Isn't it true that a left join shouldn't restrict the number of records returned?"<br /><br />This was one of those crisis-of-understanding moments: we're all taught that if you query a table and get 100 rows back, adding on a left join may <span style="font-style: italic;">add</span> rows to your resultset, but should never <span style="font-style: italic;">remove </span>any.<br /><br />His query was something like this:<br /><blockquote>select od.*, p.ISBN from OrderDetails od left join Products p on od.ProductUid=p.UID where od.OrderDate='3/11/10' and p.Type in ('B','T')<br /></blockquote>He told me that when he ran this query:<br /><blockquote>select od.* from OrderDetails od where od.OrderDate='3/11/10' </blockquote>he got 100 records back. But when we added on the left join, suddenly he only got 87 records. Since when, he wanted to know, could a left join do that? Not all the OrderDetail records had a ProductUid (some were for taxes, miscellaneous charges, etc), which was why he used the left join to begin with. And sure enough, those records were the ones that were missing from the results. Shouldn't the left join have returned those records, with no p.ISBN value?<br /><br />The problem here is actually our old friend, Mr. NULL. The left join <span style="font-style: italic;">did</span> return the OrderDetail records without a ProductUid. But the <span style="font-style: italic;">where clause</span> filtered them out-- specifically, the phrase "p.Type in ('B','T')". When I told my friend that this was the problem, he protested. "But Product.Type is a required field!" It was defined as NOT NULL in the table.<br /><br />So, how could p.Type be the problem, and when would it ever be null?<br /><br />Remember, when a match isn't found in a left join, the fields in the right table (in this case, "p") are all null. So, after evaluating the left join, there still were 100 records. However, now when we come to evaluating "p.Type in ('B','T')", we run into the situation where p.Type is null (because no record was found in p). <br /><br />Since"null=<span style="font-style: italic;">anything</span>" always returns false (technically "unknown"), and because "in" evaluates to a series of "or"s in the query engine:<br /><br />p.Type = 'B' or p.Type = 'T'<br /><br />,we end up filtering out the very records that the left join was supposed to leave in.<br /><br />There are three quick solutions to this:<br /><blockquote>select od.*, p.ISBN from OrderDetails od left join Products p on od.ProductUid=p.UID and p.Type in ('B','T') where od.OrderDate='3/11/10'<br /></blockquote>or<br /><blockquote>select od.*, p.ISBN from OrderDetails od left join Products p on od.ProductUid=p.UID where od.OrderDate='3/11/10' and (p.Type in ('B','T')or p.Type is null)<br /></blockquote> or<br /><blockquote>select od.*, p.ISBN from OrderDetails od left join Products p on od.ProductUid=p.UID where od.OrderDate='3/11/10' and isnull(p.Type,'') in ('B','T')<br /></blockquote>The first solution is probably preferable-- move the p.Type condition into the join expression. What we're really trying to join to is "products with a type of B or T"-- if none are found, then p.ISBN should be null in our resultset.<br /><br />It's equivalent to:<br /><blockquote>select od.*, p.ISBN from OrderDetails od left join (select * from Products where p.Type in ('B','T')) p on od.ProductUid=p.UID where od.OrderDate='3/11/10'<br /></blockquote> Testing for "p.Type is null" in the where clause should accomplish the same thing, but consider the condition where Product.Type is a nullable field, and there are records where p.Type is null and p.ISBN is not. By testing for "p.Type is null" in the where clause, we'd be including records with no match from the left join, but we'd also be including ISBNs for products with a null Type, which isn't what we want.<br /><br />This is particularly troublesome when the data you're working with almost always has a match in the right table. In this case, 99% of the OrderDetails records had a ProductUid, so the developer never had to worry about p.Type. When he found a rare data condition, it seemed like the rules of SQL had changed on him.<br /><br />The bottom line is this: be careful when using <span style="font-style: italic;">where</span> clauses on columns from a right table-- even when those columns are defined as NOT NULL in the database. For that matter, get in the habit of <span style="font-style: italic;">always </span>asking yourself "what if this column were null?" when testing for conditions in a SQL statement.<span style="font-style: italic;"></span><span style="font-style: italic;"></span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-12298912357678516022010-08-02T05:05:00.000-07:002010-08-02T05:34:03.835-07:00CTEs and readabilityI've been working lately on keeping my queries more readable. Here's a pattern I use a lot when I'm not thinking about readability.<br /><br />Say I want to know the average number of unique items contained in a set of orders. This is a classic aggregate-of-an-aggregate problem. First you need to find how many unique items are in each order:<br /><br /><blockquote>select count(distinct prodcode) from orderdetail group by orderid<br /></blockquote><br />Then you can take that set and get an average:<br /><br /><blockquote>select avg(prodcount) from (select count(distinct prodcode) prodcount from orderdetail group by orderid) as x<br /></blockquote><br />I'm always tempted to do this because it just involves wrapping my first query in parentheses, adding a column alias, and giving the whole thing a table alias. Here's two problems with this:<br /><ol><li>I used "x" as a table alias. Syntax requires me to give the CTE a name, but I don't really care what it is. Until I add another layer to the query. I've seen queries that are multiple pages long and full of "as x", "as x1", "as x2" etc.</li><li>It's already starting to get a little difficult to tell which <span style="font-style: italic;">from</span> clause applies to which tables, and which columns came from where. Scope is also difficult to infer-- if I use this query as part of something else and want to add a date range, where do I add it?</li></ol>Consider this syntax instead:<br /><br /><blockquote>with items_per_order as<br />(select count(distinct prodcode) as prodcount from orderdetail group by orderid)<br /><br />select avg(prodcount) from items_per_order ipo<br /></blockquote><br />The query benefits from a better alias for the CTE, but also we've simplified the intent: the last line is a very simple query getting the average count from a set. Items_per_order encapsulates the logic of getting the item count, and yet it's right there in the query if I want to delve into it. Scope is also much clearer: orderdetail and its columns can be referenced from within the items_per_order CTE, but not outside of it. It's clear where items_per_order starts and stops.<br /><br />If I continue down the road of building more logic into the query, I can add more CTEs:<br /><blockquote>with orders_denormalized as<br />(select o.orderid, s.name as salespersonname, c.name as customername, sc.code from orderheader o left join salesperson s on o.sid=s.id<br />left join customer c on o.custid=c.id<br />inner join statuscodes sc on o.statusid=sc.id),<br /><br />items_per_order as<br />(select orderid, count(distinct prodcode) as prodcount from orderdetail group by orderid),<br /><br />orders_with_itemcount as<br />(select o.orderid,o.salesersonname,o.customername,o.code,i.prodcount<br />from orders_denormalized o inner join items_per_order i on o.orderid=i.orderid )<br /><br />select avg(prodcount) from orders_with_itemcount owi where customername='steve smith'<br /><div style="text-align: left;"></div></blockquote><div style="text-align: left;"><br />I have some distinct encapsulations here that help keep track of where things came from. The definition of each CTE is obvious, and the end query is simple and easy to read.<br /><br />Performance should definitely be considered here; I'd want to see the query plans to see how well this all performs. But as a way to keep ad hoc encapsulations in order (as often happens in SSRS as you continue to hang logic on your query to build complex segment logic), this has made maintenance a whole lot easier for me.<br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-53241966258411228992010-08-01T19:16:00.000-07:002010-08-01T19:25:37.307-07:00A quick gripe<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjikkzXSyVqGbggkiMFs-Nqd1q2kS2NvlxWTO2on4arkS3Ii5fC0eBwmx1v2yr61musSvY_b42c2Z4yd3BK_dGj7S6TGuQ8ziyByzEzVGYKtMLpDYQgaIqs1YWVaXGk2zDgk4Wn1B_EVi0/s1600/sideways.JPG"><img style="float: right; margin: 0pt 0pt 10px 10px; cursor: pointer; width: 206px; height: 320px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjikkzXSyVqGbggkiMFs-Nqd1q2kS2NvlxWTO2on4arkS3Ii5fC0eBwmx1v2yr61musSvY_b42c2Z4yd3BK_dGj7S6TGuQ8ziyByzEzVGYKtMLpDYQgaIqs1YWVaXGk2zDgk4Wn1B_EVi0/s320/sideways.JPG" alt="" id="BLOGGER_PHOTO_ID_5500632917000231810" border="0" /></a><br />My Eee netbook has a tiny little keyboard, and keys like page-up, page-down, and home are reached by pressing two keys: function-left arrow is the combination for "home". <br /><br />On my ThinkPad, the ctrl and function keys are and always have been oddly reversed from other keyboards.<br /><br />Consequently, the keystrokes for alt-home (which I use all the time while browsing), on the Eee PC are function-alt-left arrow. On the ThinkPad, doing the same thing with my fingers ends up with them pressing ctrl-alt-left arrow. I'll let you figure out what that does on your own computer. Let's just say it's annoying and I wish my fingers had two sets of presets like some fancy car seats, so I could program the same muscles to do different things on different keyboards.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-89433405833430627562010-08-01T09:36:00.000-07:002010-08-01T10:10:10.423-07:00Sunday Reading: SQL Server MVP Deep Dives<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="http://www.manning.com/nielsen/nielsen_cover150.jpg"><img style="float: right; margin: 0pt 0pt 10px 10px; cursor: pointer; width: 150px; height: 187px;" src="http://www.manning.com/nielsen/nielsen_cover150.jpg" alt="" border="0" /></a><br />Today's recommendation is "<a href="http://www.manning.com/nielsen/">SQL Server MVP Deep Dives</a>," a collection of excellent articles by SQL MVP on a variety of topics.<br /><br />I just got this this weekend but so far have learned about ten things that I can use right away. Here's what makes the book such a gem: the target audience for each chapter seems to be experienced DBAs who haven't worked in that particular topic before, but who need a good quick start with examples they can actually use. They're each about 10 pages long but have enough details to give you a solid understanding of the concepts and usually some good examples that you can use immediately.<br /><br />There are some more conceptual chapters like "What does it mean to be a DBA?" and "Louis and Paul's 10 key relational database design ideas" that are good summaries of some things you probably already know (or should know) but make great touchstones. Others like "BI for the relational guy" solve that classic "I wish someone had just told me X before I started" scenario.<br /><br />As an added bonus, the authors are donating all their royalties to <a href="http://www.warchild.org/">War Child</a>, an organization that helps war-affected children worldwide.<br /><br />The ebook version is only $35 and is worth every penny.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-45055922258664177332010-07-31T17:50:00.000-07:002010-07-31T18:50:49.270-07:00No-SQL Saturday: GearsSaturdays are time to take a break from databases and look at other things that amuse me.<br /><br />This week: <a href="http://su.pr/1fgCRw">non-circular gears</a>.<br /><br />This guy supposedly hand-makes these things out of paper:<br /><object width="480" height="385"><param name="movie" value="http://www.youtube.com/v/70dKZjP4NOo&hl=en_US&fs=1"><param name="allowFullScreen" value="true"><param name="allowscriptaccess" value="always"><embed src="http://www.youtube.com/v/70dKZjP4NOo&hl=en_US&fs=1" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="480" height="385"></embed></object><br /><br />There's a great series of videos on old Navy analog computers, which are basically complex gear systems: part <a href="http://www.youtube.com/watch?v=_8aH-M3PzM0">1</a>, <a href="http://www.youtube.com/watch?v=w-wemKmlaBk">2</a>, <a href="http://www.youtube.com/watch?v=mQhmmTX5f9Y">3</a>, <a href="http://www.youtube.com/watch?v=T_xhMykK5Hw">4</a>, <a href="http://www.youtube.com/watch?v=KIWj8Md4Zx0">5</a>, <a href="http://www.youtube.com/watch?v=quodLgEiZh4">6</a>, <a href="http://www.youtube.com/watch?v=TkVak9wU4DY">7</a>.<br /><br />This is probably the best explanation I've ever seen of how differential gears work:<br /><br /><object width="480" height="385"><param name="movie" value="http://www.youtube.com/v/K4JhruinbWc&hl=en_US&fs=1"><param name="allowFullScreen" value="true"><param name="allowscriptaccess" value="always"><embed src="http://www.youtube.com/v/K4JhruinbWc&hl=en_US&fs=1" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="480" height="385"></embed></object><br /><br />And of course, no discussion of differentials would be complete without mention of the <a href="http://odts.de/southptr/scale.htm">South-pointing Chariot</a>, rumored to have been invented by the Chinese over 4500 years ago. South-facing chariots use a series of gears to keep a figure pointing south no matter which way the wagon turns. These days geeks can't resist building their own out of legos or whatnot, and in looking for links I came across a <a href="http://makersmarket.com/products/1420-the-chinese-south-pointing-chariot">kit</a> you can buy for $59 and I think I might just have to.<br /><br />Lastly, here's some random gear-related links:<br /><br /><a href="http://www.youtube.com/watch?v=LbvmKzf_wr4">Spiromania!</a><br /><a href="http://www.animatedengines.com/index.shtml">Animated Engines</a><br />The <a href="http://en.wikipedia.org/wiki/Trammel_of_Archimedes">Kentucky</a> <a href="http://www.google.com/imgres?imgurl=http://www.grandpagenes.com/sitebuildercontent/sitebuilderpictures/DoNothingMachine.jpg&imgrefurl=http://www.grandpagenes.com/id2.html&usg=__JhyxariS-4eh7W4W86uWRG2HwOk=&h=225&w=300&sz=119&hl=en&start=0&sig2=RNQdxJhQdZR2SmAPzeQodQ&tbnid=KjCI02Bcfkm99M:&tbnh=147&tbnw=213&ei=8dFUTNqCNYT48AbhpPD3Ag&prev=/images%3Fq%3Ddo-nothing%2Bmachine%26um%3D1%26hl%3Den%26safe%3Doff%26sa%3DN%26rlz%3D1G1GGLQ_ENUS357%26biw%3D1024%26bih%3D594%26tbs%3Disch:1&um=1&itbs=1&iact=hc&vpx=621&vpy=86&dur=722&hovh=180&hovw=240&tx=98&ty=107&page=1&ndsp=14&ved=1t:429,r:2,s:0">Do-Nothing</a> <a href="http://www.youtube.com/watch?v=GzQfQa8uv6M">Machine</a><br /><a href="http://www.youtube.com/watch?v=grUySw87TQE">The German Do-Nothing Machine<br /></a>The Eames (yes, <span style="font-style: italic;">that</span> Eames) <a href="http://www.treehugger.com/files/2010/06/charles-eames-do-nothing-machine.php">Do-Nothing Machine</a><br /><br />A wooden orrery!<br /><object width="480" height="385"><param name="movie" value="http://www.youtube.com/v/v6KB3ctC5as&hl=en_US&fs=1"><param name="allowFullScreen" value="true"><param name="allowscriptaccess" value="always"><embed src="http://www.youtube.com/v/v6KB3ctC5as&hl=en_US&fs=1" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="480" height="385"></embed></object>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-38150954495735315582010-07-30T16:45:00.000-07:002010-07-30T16:46:29.084-07:00CertifiableI recently studied for (and passed!) my exams for MCITP Database Developer certification.<br /><br />In the past, I've always glanced askance at certification. I've run into too many people in my career with a lot of letters after their names and not much sense or experience in their heads. Conversely, some of the smartest people I know in IT have no professional schooling or certification.<br /><br />An old joke comes to mind:<br />Q: What do you call the person who graduates last in their class from medical school?<br />A: "Doctor"<br /><br />Passing your boards and completing your residency doesn't make you a good doctor. Years of experience, ambition, passion, and curiosity generally do. But, would you take your sick child to see a doctor who "never really got around to getting licensed"?<br /><br />Getting IT certification shows the world:<br /><ul><li>I care enough about this skillset to actually study for and pass the exams</li><li>At one point or another, I read and understood something about all the topics in this area, not just the ones I use every day.</li></ul>Cynically, I'd say that most anyone can cram enough facts into their head to pass a test, and then never actually apply that knowledge and really <span style="font-style: italic;">learn</span> it. But assuming you're not a complete phony and you actually do know your specialty pretty well, being forced to study for exams benefits you in a lot of ways, even if you forget a lot of the facts shortly after.<br /><br />Here's what I got out of it:<br /><ul><li>I demoted a lot of "unknown unknowns" to "known unknowns." It's kind of like going through your toolbox and learning what some of the more obscure tools do. You still might not ever use your double offset ring spanner, but at least now you know you've got one and what it's for.<br /></li><li>I got a good solid dose of humility. In my little work world, I'm the SQL guru. But really I use a subset of SQL's features, and there's always plenty to learn.</li><li>On the opposite side, I got acknowledgement that I do really know my stuff on the "known known" topics. I'm pretty good at writing complex queries, and tuning them, and those questions were a snap.<br /></li><li>I was able to put a few things to work right away. I've just recently started using recursive CTE's, and lately I'm finding all kinds of things to do with CTE's. I knew about them, but until I started playing with them, they never entered my go-to lexicon.</li><li>I reaffirmed how much I like working in SQL. The Microsoft exams give you a "real" business scenario that you've been hired to implement, and then ask you about various aspects of the project. It was actually kind of fun digging into an interesting problem and thinking about how I'd go about tackling it.<br /></li></ul><br />Next on my plate: MCITP Developer 2008.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4404625651414760707.post-47192016253781450552010-07-29T13:47:00.000-07:002010-07-30T06:07:57.587-07:00Welcome, and License Plate WoesWelcome to Es Cue El.<br /><br />This blog will be a place to post random thoughts about SQL projects I'm working on, things like the No SQL movement that interests/scares/annoys/intrigues me, and just generally a place to geek out. Hopefully I pick up some SQL friends along the way.<br /><br />A little about me. I work for a software company in Cleveland, OH, and have held various positions over the past couple of decades, most recently "technical consultant." Although I've wandered a bit from programming to support to consulting to product management, I've always been heavily involved in database work, whether it's writing SQL, tuning performance issues, building data access layers, etc. <br /><br />I've been looking for a creative outlet for a while, and decided that blogging about something I love would probably be a good place to start. Maybe that's a little like a professional race car driver taking up painting to relieve the stress of racing, and then ending up painting nothing but cars and ovals. But hey, it's a start. And I'll try to throw in a non-database post from time to time.<br /><br />Oh, about the license plate. I decided to go all geek and get an obscure techie license plate. I surveyed my SQL friends to help decide between some good candidates: "SP WHO2", "NOLOCK", "VARCHAR", "ES CUE EL", "SQL FTW" (that last one from a leet-spoken young'n). In the end I went with a slightly different spelling:<br /><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqOv2y0f7hfVuuIPrbEt5PThVHsN5WvoRTmzogPk8rxHFTlnrwu5K3y8s6K7pGoeZ4nl3HwteBmOZNQLrhlwKg_Zdiv84fetirBhBdCU3a3HtUsCRmi1lnK1pEsN1tWwiR3JhmlqN1p9E/s1600/esqueel.jpg"><img style="display: block; margin: 0px auto 10px; text-align: center; cursor: pointer; width: 320px; height: 240px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqOv2y0f7hfVuuIPrbEt5PThVHsN5WvoRTmzogPk8rxHFTlnrwu5K3y8s6K7pGoeZ4nl3HwteBmOZNQLrhlwKg_Zdiv84fetirBhBdCU3a3HtUsCRmi1lnK1pEsN1tWwiR3JhmlqN1p9E/s320/esqueel.jpg" alt="" id="BLOGGER_PHOTO_ID_5499436593056882674" border="0" /></a><br />I still like it, but somehow didn't notice the Spanish "que" or realize how many people would read it as "ess kay ell" (in Spanish it translates roughly to "it that the"). I took German in school and somehow missed it. Anyway, it's on my car for the next two years now, so I guess I'm stuck explaining it a lot-- but that's kind of what I wanted in the first place. If I had my life to live over, I might have gone with ES CUE EL. C'est la vie.Unknownnoreply@blogger.com0