Tuesday, June 27, 2017

Primary Key columns in INCLUDE list

I was reviewing someone's code the other day and I saw they had put primary key columns in the INCLUDE list of a non-clustered index

-- Create a table
if object_id('tempdb.dbo.#PhoneBook') is not null drop table #PhoneBook
create table #PhoneBook
(
    FirstName nvarchar(500),
    LastName nvarchar(500),
    PhoneNumber varchar(20),
    DOB datetime
)

create clustered index #IXC__#PhoneBook__LastName on #PhoneBook (LastName)

create nonclustered index #IXN__#PhoneBook__PhoneNumber on #PhoneBook (PhoneNumber)
  

A little background on the physical structure of non-clustered indexes for those who might not know. A non-clustered index consists of at least two, and up to three components. All non-clustered indexes store the index key (in my example above, this would be [PhoneNumber] in the nonclustered index) as well as a pointer back to the clustered index (in this case, the columns [LastName]).

The Phone Book example is the classic indexing example thrown around. The clustered index is the way the data is physically structure. In a phone book, that's usually by last name. But if you wanted to look something up by a different field, say PhoneNumber, you probably wouldn't want to re-print an entire phone book ordered by PhoneNumber. Instead, you could create an index in the back of the book which lists phone numbers, and a reference to the page you could find those phone numbers in the main part of the book.

So if you wanted to look up the first and last name of the person with the phone number 555-555-5555, it might say "the last name of the person with this phone number is 'Smith'. You would then flip to the page that has the "S"s and start looking for Smith. (note, this is called a "clustered index lookup" when performed in SQL), and then read the persons first and last name.

At a minimum, that index has to have the index key (PhoneNumber) and where you can find that phone number in the phone book. But now say I don't want to have to flip back through the phone book to see the person's first name. I could annotate the index to also contain the first name of the person associated with that phone number so that I never have to flip back through the book looking through all the "S" names. That's what INCLUDE column's are. They're not information you can search on, they're just bits of data from the clustered index which are copied to the index structure so you don't need to do that lookup on the primary key.

So now we get back to the matter at hand. What happens when you include a column that's part of the primary key? Short answer, nothing. Those column's are already part of the index's physical structure because of the pointer from the index to the clustered index. SQL is smart enough to realize this, and doesn't copy those values twice or have to do extra maintenance on them. So the size and overhead of the index is the same.

So should you include them or not? You may also have noticed that if you look at an execution plan, SQL will often suggest indexes which include clustered index. While I'm not personally a fan of including those columns, there isn't any performance hit for doing so. And it was pointed out to me that if you DO explicitly include columns from the primary key to cover a query, should that primary key change, those columns will STILL be covered. There's something to be said for that, but if you're changing a primary key on a production table, that may be the least of your worries.

So what did we learn? INCLUDE-ing primary key columns in a non-clustered index is totally unnecessary as those references already exist. However it also does no harm to the performance of queries or the overhead of maintaining that index. It may just come down to whether you'd like to have your index INCLUDE list nice and tidy, or if you think there's value to explicitly stating that those columns need to be covered. Personally, I prefer tidy, but I can see it the other way too.

Hopefully you now know a little more about what the end result of doing this is.

Tuesday, March 14, 2017

Rolling an Idea Around in Your Hand



I hear from many people (my wife included) that they “just don’t have a mind for programming”. Now maybe that’s just a polite excuse to say they have no interest in programming, which, too, is fine. And while, like with music, there are some people who really have (or do not have) structures in their brains making it difficult if not impossible to do, I think the majority of the skills are generic enough to be learned with practice and more importantly, a drive to do so.

One of the skills you pick up along the way is what, by analogy, I’d like to think of as rolling an object around in your hand. There are few things in our daily lives that we’ve never seen before, let alone something you can hold in your hand. But there are some. Imagine an unsolved Mirror Cube. Or maybe you’ve decided you want to work on your car, and you start removing parts you never knew existed. Or maybe you get a Christmas present still in the packaging and you’re trying to figure out what it might be?

Many times, you’ll just start fiddling with it. Feeling its surface. Rolling it around in your hand. Feeling its weight. Inspecting it from different angles; color, shape, function. If it has parts, how they fit together. Maybe the parts move. Maybe they move and they shouldn’t. Maybe, and most cripplingly, you’re afraid to do the above because you think you might break it, or not be able to put it back the way it was. Maybe you’re right.

The point is, you’re solving a problem. You don’t realize it yet, but you are. Any time you’re presented with something you don’t fully understand you start doing an investigation. The only difference in the software world is you’re lucky enough to be able to hit the undo button, removing much of the risk in the last bit of the previous paragraph.

To a large extent, that’s what software development is. Or more accurately, that’s what software maintenance and troubleshooting is… which is basically what software development is. Maybe that’s what people talk about when they say they don’t “have a mind for programming”. It’s not that they can’t, but they’re not the kind of person who feels the need or desire to question everything, get their hands dirty, and learn about as abstract a concept as an arbitrary piece of code. Personally, I find it a lot of fun. And you sort of do this “rolling around” in levels, going from the simplest to the hardest.

At the simplest, you’re just trying to acknowledge what it is you’re looking at. If it’s a Rubik’s Cube, that’s what you’re trying to come up with. If it’s an encoding issue on a web page, that’s all you’re trying to identify. Then you start fiddling with it. With our Rubik’s Cube example, start twisting things. Start small; make sure you can find your way back. If it’s a piece of text that’s wrong, maybe find where that text comes from, or how it’s being displayed.

All along the way, you’re looking for clues to figure out what you ultimately are trying to get to. If it’s a Cube, maybe you’re trying to solve it. If it’s a bug, maybe you’re trying to find the root cause and possibly fix it.

For your cube, maybe you start trying to solve a side. You draw upon your knowledge of geometry, group theory (or at least the intuitions you have about it), your memory of fiddling with sides and your goal to try to move all the pieces to one side. For your software bug, maybe you open the code that generates that text, and you start substituting other pieces of text seeing if they yield the same bug.

This process can go as deep as it needs to. For the Rubik’s Cube, entire branches of mathematics are in no small part devoted to solving puzzles like the Rubik’s Cube. For software, there are web tools, open source tools, tools by Microsoft or Apache to solve these tools. There are test frameworks and troubleshooting methodologies. All of which will come with practice and experience. Many of which you won’t need for all problems, but are tools none the less at your disposal.

The point is, when you see an intractable problem in front of you, don’t be scared. Pick it up and start playing with it.

Tuesday, January 31, 2017

SQL ISNUMERIC() function

Imagine you have a column of data which is supposed to contain numbers, but you don't know which ones are numbers. Imaging you also live in a world where SQL Server 2012 and above don't exist. Now imagine you stumble across the function ISNUMERIC one day and think to yourself "huzzah! My worries are over!". Now imagine you put that check in to your code, push it to production, and low and behold, it doesn't do what you expect.

This is documented elsewhere as well but ISNUMERIC might not do what you expect. It includes characters which aren't strictly speaking numeric values. For instance, it contains several currency signs. It allows some white space characters, and it allows commas, pluses, minuses and more. Consider the following.

select 
    Idx = num,
    Chr = char(num),
    IsNum = isnumeric(char(num)),
    IsNum_e0 = isnumeric(char(num) + 'e0')
from (select top 255 num = row_number() over (order by (select null))
      from sys.all_objects) a
order by isnumeric(char(num)) desc, num 
 
You can see all the characters (from the first 255 ascii characters) which it will consider to be "numeric". It's also worth noting that this is not a comprehensive list of characters though, as I'll illustrate in a way which improves the functionality of isnumeric in many cases. the trick is to add 'e0' to the end of the string you're converting. So 123 becomes 123e0. Since this is scientific notation, it's considered a numeric value; and rightfully so. but "$\e0" is decidedly NOT numeric. In the statement above, I included a "e0" suffixed column as well as you can see it cuts out everything that's not a number.

So remember ISNUMERIC is not quite what you think, but if you're expecting a run-of-the-mill integer, float, or decimal value, appending "e0" to the end can get you a long way.

Friday, December 30, 2016

65335 Factorial

45976438392061397988343941847591236277916065429990440654349550068504679738884873696467431791345399245113981907504422033312082268575005594684147487298690895258932269532007959069037148320678218989070422616295627289699421198100146137897939074573452482171256807624829684290289689396236992931996939908058067600600838619258623744520626875148067787822805960954972039192524352601574556171620942308545240195880792034091344218697852064993002279939259639148950370513142588841620267193160859201162085126077699649837299842231104019002928013830999815410175321695075076811244689319384193385798382146785287877750497502616917706719664312621730693106936224794649129170152569947522894194089004329797390959574830664653788255093990014050618406295687886415625452403981822006939528194141275990292559366020439379374326677236820693847113352926031643116322764661990341101753865133758702070800427978271501338812297451757423243932981200672833587464134088249721922680050114210450536576311656621290600774289696746863402817078712605876450642418288543858429263952926556382503968529848512747321233417094524780031061251759451141340573303091587811371232266780624168824343138251271080945539888072547285996725781356582043664237755938597536565095885739831122438601814792170835505979514065782693489812849582337794978517601290342767812743435377597584763209382783436180652384063782721243080562574696587157203070211816840534545846393756474354623678731672283082271423219128257946125179449162582624382887762609457614959925633134956706449714133739666645743987236626356447143225102412221919428364364309289612154763919926279221761411940740305367972394645949150831637315493109480112263097627853302246651567076352337610591201233237181221374556503103647875618802177369749850208187759126116527803462085592868083999199953015540180955257821822217629140915292796735661483912265821572326265339535472080618962954133160497710938525779326434468474330845652817234876920836175612907287960669613128032250198536988156104354471532108771937875660961865765279220578399940613267889451099608255503729827607227550234146494740434952011813302242082056096214536405531797859238735801324473534782553541185994425720091299870308649072836690359443192068922330463491775065029467088777033372632053988338065865164470735512860572761634488685758458591397251855543402298850388752553967597144259772853305336180152716014054427408577992660121813770983565559168176574628382650884957114847543411433717514343152572491533784865316757096788898545094391552215298722685572517173768082118754912454358800491888592504142454219979608479232525220376554784452736090415398331111067715986903911014115016856097173212164648714707689318985484757091910291477977588417156274972843884452864206419174957951527290423421007385755949865046931625888439439306960438866244927081482134900622745889300316823759855220088818135214167753893485933301753236981092339979659413165759934909364112409460501388811730230608483134808770416096397314786244403451675912062231852425997823778839344397666102821896212084363204637941140808792393162814378219495566079415168656383830267445712282796297164471944136231336286072073918487494773614233596943553369228366146806325861166403577143952015853340526278511053542635399960449462755895568592200993670170820207454823796927637331768688080139561822808803948116739335419876998085872601725794019314156337147156290827670764711786134731267142890229229311468301758856911559993251842255081489858116254638311325592791565448752085128761957407235424465962349859754798260741381644201089185957369355502282457459803745041996111563730711471499286842323812751384288471629669394337579654572019103535605018840355430326862921019348108210605561350664017673740150893005505372346289577609293066887563424681432408904374433424432484471509148022436179262962988396188248680237227155732243193463595283563055656466760947240044133602631531582644992184706047272448074019029931755892134764335587528784392251576374892825261000606983188176103248751164809328593185849175484052344952940246200273746312841124200915415724588626357205430876500905400337186790335756222365177577511913354201864265844597463219426822019717996440658466084859490682600858408977798102390246828055464653489642946494135941651187583975358391798058087118182731047163506418197607601485897818658602475203785163348622312319711646123814508882051593247418105013528598641522411640133305454833115619659604728439127465881832038955664584228773899602624258680313011441342266585140351805352632292894844624968681915135702449857355475014082872599431617116110726254732975007653006863125747569677741347895886777224162636928219529036296302045240895160514952794291065639982382447529035672493906680038698129433036879737737087281831218885248389385264027519984499848903549502256091004446799792777200690786057213407186018858818963236362930677820400187626627278382420790680734026700664767256719598991977630795242022232459701500813559429834894157435155960449654190923808202665215072090572449911198056038377331112468686892246674787624609417402599106655819287889404573279330571425358883611495056200690933025707410543253711484975405614342277334229384683404180789853242184722469324930303511218464828886377318518510299525730280814641073762676973062011187489044813266643496516893624796733337836655871600090189247439069608412077310983750744370017674391816344980962390877640758343710597437389072196537948637929177273932950443270790058062151846105569784512035254268844339491123142661156584016995547237010239963837937098343602520144670674924394228513140567607289571034407382355443972830650350372475613351475103234036899819618458818064080160995432290592507329015832753250622817307618003139578991574874102510441342967616019466766539876026171097695664715131998186368429398916744863668147935133069459547662863492047143775981998801476178076197072220024380851784239840116731341014982779506163570030057387446857687071889010826672123210202879576774713050205560569348629049111076491215031958039197569341807220993914469880959886331157308923173649481366277489417043548729413802551850788404433318270011694014641474952137579073293148610039576379923666292858653993231455697251152768132160379626139750207196254394465593281009110469775182373654069530349247982426497397791657727078170878237947375956137826136346467826749146719878378966396525232822058563302272927868641875515753712903662308972272204061404829382942097401235726843979508135046938581582978898393036038943405146229041593367545412842149795993066123561097874691569732083328304739672978064423474811358377642501774176059073644522871594911561723053822566413281293660878431454899040339143138116695448859017257064881697004853266455810556881314023251294528090162639017364524782557419486138095958073026407872423451028242863855023396295232324399145492687188976015116292236100249461736322271164832436778762243243762852868949890448190692680705038556132505905037928026929479158266510338560995313841724416035697652924445339846234985590080240185471598476024547207087738624806386825760122582816027536491412964173476109065349125168561042922869541923037047331254863150261581707126739318975529778964533702416754790302949415638847375945853420445310334668555724089932181594988440175913973426825058135199463958597503411474407564028070982817904318814332347390564790972434224281564941248062627347827365342175422742039266305675639840552991032649533023324267542835605926460523395342671805389481258973590522663128410279586908786987795831623717077198544742760196720814278531812164372252227827260179835748354343053670069166769758024014143583322780138567128374539493336835443701596468846770538938368000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Beware the Errors of SSIS

SSIS is a bit of a touchy subject where I work, and the more I use it, the more I sort of understand why.



While SQL Server is my background and I consider myself an expert in TSQL (not like, MVP level or anything), I don't do too much in SSIS. We have a home grown architecture which, in many ways, mimics functionality SSIS can do, and, for our purposes, usually works better and comes with fewer headaches. That said, there are some operations SSIS is simply hands down the best tool for the job. But I didn't come here to talk to you to day about when to or not to use SSIS. No dear reader, I come with a warning/complaint about how it surfaces error.

The description of how it surfaces errors is "not very well". It's undoubtedly something you get better at teasing out over time, but I think largely because of the breadth of systems it needs to interact with, surfacing meaningful errors for specific stages can often seem to have absolutely nothing to do with the error at hand.

Take for instance the following error I got and subsequently spent a day and a half trying to figure out.



[Execute SQL Task] Error: Executing the query "exec alerts.dbo.SyncAddressesBatch @BatchId = ?" failed with the following error: " ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

Here's the setup. Execute SQL Task, which calls a proc with a single parameter.



What the proc does isn't important. Sufhttps://msdn.microsoft.com/en-us/library/ms140355.aspxice it to say it accepts a @BatchId parameter and then does stuff. 

So how would you go about troubleshooting this issue? Well first, let's look at the error text. The first troubling thing is all the "or"s in the error. Could be A... or B... or C... or something. Googling the error you find responses related to pretty much all the cases there; none of which applied to my situation. There's also the empty string error message.

So with the internet not being able to help, what would you look at? first, you need a little background on Execute SQL Task Parameterization (a topic I'll let you read up on your own if you're interested). The syntax for parameters is a little funky, but basically you put question marks in your SQL where you want to substitute a value, and then on the Parameter Mapping page, you supply variables and name them by the ordinal position the question mark appears (so the first question mark in the script is Parameter Name: 0, the second question mark is Parameter Name: 1, etc). Everything looked on the up and up. I put in a script stage to verify that the parameter I was passing indeed had a value, and wasn't invalid or null or something. Finally I threw up my hands in frustration and went home for the day. 

When I came back I just started fiddling with things. I tried omitting the (required) @BatchId parameter. It correctly surfaced an error from SQL Server saying I didn't provide a required parameter. I tried replacing the @BatchId with a hard coded "1" (getting rid of any parameterization altogether), but still got the same error.

Finally, I think by accident, I put a return statement early on in the proc, and without knowing that, re-ran the package. To my confused delight, the package succeeded. Through a little more sleuthing, I figure out that what was happening was the underlying procedure was hitting a primary key error, and the empty string it was surfacing just happened to be the result of a null concatenation. Easy fix on both counts, but very frustrating to have to arrive at that conclusion in such a circuitous and laborious process

tl;dr, it's difficult to know when to take SSIS's errors seriously and when to assume it's a different component. Other than making these mistakes yourself and learning from them, there's not any deterministic way I can think of to decide which to do when. All I can say is take any errors you see with a healthy dose of skepticism and if you find yourself banging your head against a wall trying to figure out what a given, vague, error really means, take a step back, gather your thoughts, and perhaps approach it from a completely different angle. We often rely on informative error messages to guide our development (and rightfully so). But SSIS has a habit of trolling us developers, whether it knows it or not. Please, do not feed the trolls.

Wednesday, December 21, 2016

Big Crazy Linked Server Query

I got bored and was fiddling around with a statement I'd been doing manually in several steps to try to make it as easy to use (not read) and as quick as possible. The rundown is this:

    I have a query I want to run which spans two databases.

    The relevant schema for these two servers is as follows:


-- Server 1

    -- Has about 500m rows
    create table dbo.orders
    (
        order_id varchar(55) primary key clusterd,
        Data varchar(8000) -- just a placeholder to show that there's other stuff in here
    )

    -- Server 2
    create table dbo.orderLog
    (
        order_id varchar(55) primary key clustered,
        PayloadId int unique,
        OtherData varchar(8000) -- Placeholder for other columns. 
       --Not that it matters, but this data is different than the data on server 1
    )
    -- Has about 20 million rows
    create table dbo.events
    (
        RecordId int identity(1,1) primary key clustered,
        PayloadId int,
        TriggerDate datetime,
        OtherData varchar(8000)
    )

    exec master.dbo.sp_addlinkedserver
        @datasrc = 'Server1',
        @server = 'lnkServer1',
        @srvproduct = 'SQL Server

I need to query Server1.dbo.Orders.Data using a list of order_ids having a certain bad data point in them.The issue is that a subset of the orders have a bad data point (TriggerDate) which can't be derived from the data on Server 1, but CAN be derived from the data on server 2. The bad rows are any rows in Server2.dbo.Events where TriggerDate < '1900-00-00'.

Server1.dbo.orders has close to half a billion rows in it, and just about any way you slice it, doing a linked server join ends up doing  a scan on Server1.dbo.Orders.Data. However if I use OPENQUERY with the results of a query on server 2 hard coded in there, it compiles the SQL locally on Server1 and performs admirably. The trouble is then constructing a query to do this. Actually this is not that hard to do, but  because I'm feeling particularly masochistic, I decided to try to compact it into as few sql statements as possible. And it is here, dear reader, where the fun begins.


  1. Events has the information I need, but to trace back to a specific order, I need the PayloadID, which is not unique in EventLog
  2.  So I query OrderId from AlertLog where such a payload matches the criteria from step 1 exists in EventLog
  3.  I then use a FOR XML PATH statement to serialize all these orders so I can concatenate them into a DSQL clause
  4.  Also, apparantly OPENQUERY doesn't allow a query larger than 8000 characters. So I have to select the top n rows where n = 8000 (max field size) / the length of an order_id (55)
  5.  I use quotename() on the OrderID to wrap it in single quotes. However since this is nested in several levels of DSQL, I'll have to do more to it (more on that later)
  6.  I use stuff() to cut off the leading comma
  7.  I use replace() on all single quotes and make them two single quotes. This way, as it exists in the openquery code portion, the quotes are properly escaped. This could have been accomplished in any on several different ways. This is just the route I chose.
  8.  I can now almost do my select from OPENQUERY, but OPENQUERY doesn't allow parameterization of the code portion, or anything but a string literal
  9. So the final tactic here is to put all this in DSQL. That way the DSQL executes the openquery statement as though it were literal SQL. Within the context of the DSQL, the OPENQUERY doesn't realize it's being tricked into executing dynamic code.  It then executes the dynamic SQL using a linked server, while executing locally compiled code on the target server.
  10. The dynamic SQL is executed. Although it irks me that I have two statements here, going the exec ('/*sql code*/') route actually introduces a bunch of unnecessary complexities which I feel just aren't worth making to consolidate the query further. 
Sadly, the setup of this is so convoluted I can't really easily provide test data for this, but trust me, it works.

declare @sql nvarchar(max) = '
    select *
    from openquery(lnkServer1, ''
        select *
        from Server1.dbo.orders with (nolock)
        where order_id in
        (' + replace(stuff((select ',' + quotename(OrderId, '''')
                            from (select top (8000 / 55) OrderId
                                  from Server2.dbo.AlertLog o with (nolock)
                                  where exists (select 1
                                                from Server2.dbo.EventLog i with (nolock)
                                                where i.TriggerDate < '1900-01-01'
                                                    and o.PayloadId = i.PayloadId)) a
                            for xml path('')), 1, 1, '') , '''', '''''')
        + ')'')'

exec sp_executesql @sql


Monday, November 23, 2015

Database Size Script

This is just a fancier version of sp_spaceused which writes the data to a temp table which you can sort on
use ManGroup
set nocount on
go

declare 
    @TableName varchar(128),
    @RID int,
    @MaxRID int,
    @SQL nvarchar(max)
if object_id('tempdb.dbo.#LoopSrc') is not null drop table #LoopSrc
create table #LoopSrc
(
    RID int identity(1,1) primary key clustered,
    TableName varchar(128)
)

if object_id('tempdb.dbo.#Tabs') is not null drop table #Tabs
create table #Tabs
(
    TableName varchar(128),
    nRows int,
    nReserved as cast(replace(sReserved, ' KB', '') as int),
    nData as cast(replace(sData, ' KB', '') as int),
    nIndexSize as cast(replace(sIndexSize, ' KB', '') as int),
    nUnused as cast(replace(sUnused, ' KB', '') as int),
    sReserved varchar(30),
    sData varchar(30),
    sIndexSize varchar(30),
    sUnused varchar(30)

)
/*****************************
*** INSERT LOOP ITEMS HERE ***
*****************************/
insert into #LoopSrc
(
    TableName
)
select name
from sys.tables

select 
    @RID = 1,
    @MaxRID = @@rowcount
/**********************
*** LOOP STRUCTURE  ***
**********************/
while @RID <= @MaxRID
    begin

        select 
            @TableName = TableName,
            @sql = 'exec sp_spaceused ' + TableName
        from #LoopSrc
        where RID = @RID
        begin try
            insert into #Tabs
            exec sp_executesql @sql
        end try 
        begin catch
        end catch

        select @RID += 1
    
    end


select *
from #Tabs
order by nRows desc