August 21, 2011 at 4:59 am
Jeff Moden (8/20/2011)
Hmmm... like any table, I thought that all of the rows of a TVP had to have the same number and type of columns in the same order.
Of course they do, it's a table, just like any other table in SQL.
In this case, how would the order header row and order detail rows appear in the same TVP since they're going to have different columns (unless, of course, you're talking about using two TVP's)?
They wouldn't appear in the same TVP, unless you have order header and order detail in the same table in the DB.
If you're passing a single order header and multiple order details (which is about the norm for shopping apps) then the order header can be scalar parameters or a TVP (though I'd go for scalar parameters for a single order header) and then the details, instead of having to go into a pseudo-array or XML (or multiple procedure calls) go into a TVP.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2011 at 9:46 am
SQLkiwi (8/20/2011)
Jeff Moden (8/20/2011)
Hmmm... like any table, I thought that all of the rows of a TVP had to have the same number and type of columns in the same order. In this case, how would the order header row and order detail rows appear in the same TVP since they're going to have different columns (unless, of course, you're talking about using two TVP's)?Yes, two TVPs. Easy, and neat, both for the .NET developer, and the DBA.
Also, I can see how passing values of numbers might be less chatty than passing their string equivalents, but how is passing an entire recordset via TVP going to require fewer round-trips than passing a string that contains the entire recordset (unless you're talking packet level, of course)?
It's denser (no delimiters, native format) and the TVP contents take the same fast code path as a bulk load.
Understood but does that actually reduce the round trips to the server?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2011 at 9:47 am
GilaMonster (8/21/2011)
Jeff Moden (8/20/2011)
Hmmm... like any table, I thought that all of the rows of a TVP had to have the same number and type of columns in the same order.Of course they do, it's a table, just like any other table in SQL.
In this case, how would the order header row and order detail rows appear in the same TVP since they're going to have different columns (unless, of course, you're talking about using two TVP's)?
They wouldn't appear in the same TVP, unless you have order header and order detail in the same table in the DB.
If you're passing a single order header and multiple order details (which is about the norm for shopping apps) then the order header can be scalar parameters or a TVP (though I'd go for scalar parameters for a single order header) and then the details, instead of having to go into a pseudo-array or XML (or multiple procedure calls) go into a TVP.
Thanks for the confirmation, Gail.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2011 at 6:05 pm
Jeff Moden (8/21/2011)
Understood but does that actually reduce the round trips to the server?
I suppose it might, since the request is likely to fit into fewer packets.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 22, 2011 at 1:44 am
Lynn Pettis (8/19/2011)
Monday will be the start of a new day. I will be starting my new job at Progressive and getting back to working with the BEST RDBMS in the business,MySQLMS SQL Server. I am really looking forward to getting back to what I know and love in the databasse world.
Welcome back to SQL Server! Enjoy your first day with your comfortable set of tools.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
August 22, 2011 at 1:54 am
Jeff Moden (8/21/2011)
Understood but does that actually reduce the round trips to the server?
When Gail noted fewer round trips, I was thinking she was contrasting multiple calls from the .net side (one 'scaler' call per each order details row). This is one method, serializing multi rows and splitting them is another, and a TVP is the most elegant (as compared to delimited strings/splitting) and fewest calls from the .net side, and less work for the .net dev and DBA.
Finally, an 'Array' datatype!
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
August 22, 2011 at 5:28 am
Lynn Pettis (8/19/2011)
Monday will be the start of a new day. I will be starting my new job at Progressive and getting back to working with the BEST RDBMS in the business,MySQLMS SQL Server. I am really looking forward to getting back to what I know and love in the databasse world.
Enjoy your first day back on the Light Side! Yay! Always knew you'd return. 😛 Do well (like you can't do anything else. :-D)
-- Kit
August 22, 2011 at 6:02 am
Hi folks, I've not visited here for ages, I hope you are all well.
Anybody recruiting presently? We've been recruiting a DBA and a TSQL developer (with some role overlap for holiday cover) for several weeks. Around 25 cv's have passed over my desk, I've been involved in about 10 first (phone) interviews and three face to face second interviews. We have a set of fairly simple questions which were collected from suggestions here on this forum and elsewhere. The simplest of these were used to weed out the chaff at the phone interview stage.
It's been an eye-opening experience. Given that the first requirement on the job spec is three years' experience of TSQL development, only two candidates could provide a reasonable explanation of the difference between a clustered and a non-clustered index. One self-proclaimed "19/20 TSQL expert" stated that a CI is a "special index which makes queries run faster than a NCI". Few knew the purpose of include columns. Only one knew how to turn a left join into a NOT EXISTS with a filter in the WHERE clause (WHERE [ID or something not-nullable of rhs is null]), and he was also the only candidate who knew what would happen if the filter was changed to a non-null value.
What's really shocked our team here is the extent to which candidates haven't merely exaggerated, but been downright dishonest about their skillset claims. Hence two years sitting near a guy who did some SSIS development from time to time translated as "Two years experience of SSIS".
The last straw was a phone interview with a chap who looked on paper at least to be an excellent fit and was "reassuringly expensive". He didn't have a clue, waffled at length BSing wildly - and loudly - to cover his ignorance, and sounded as if we were a bit cheeky asking him a few simple questions!
Some employment agencies offer a pre-testing service. I can see us reaching a point very soon when only pre-tested candidates will be worth interviewing for all bar the most junior roles. Having seen contractor rates decline steadily in real terms for several years now, I'd be delighted to see this translate into a two (or more)-tier skill/pay structure, even if it means upskilling from time to time. Okay, today then...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2011 at 6:13 am
ChrisM@Work (8/22/2011)
Hi folks, I've not visited here for ages, I hope you are all well.Anybody recruiting presently? We've been recruiting a DBA and a TSQL developer (with some role overlap for holiday cover) for several weeks. Around 25 cv's have passed over my desk, I've been involved in about 10 first (phone) interviews and three face to face second interviews. We have a set of fairly simple questions which were collected from suggestions here on this forum and elsewhere. The simplest of these were used to weed out the chaff at the phone interview stage.
It's been an eye-opening experience. Given that the first requirement on the job spec is three years' experience of TSQL development, only two candidates could provide a reasonable explanation of the difference between a clustered and a non-clustered index. One self-proclaimed "19/20 TSQL expert" stated that a CI is a "special index which makes queries run faster than a NCI". Few knew the purpose of include columns. Only one knew how to turn a left join into a NOT EXISTS with a filter in the WHERE clause (WHERE [ID or something not-nullable of rhs is null]), and he was also the only candidate who knew what would happen if the filter was changed to a non-null value.
What's really shocked our team here is the extent to which candidates haven't merely exaggerated, but been downright dishonest about their skillset claims. Hence two years sitting near a guy who did some SSIS development from time to time translated as "Two years experience of SSIS".
The last straw was a phone interview with a chap who looked on paper at least to be an excellent fit and was "reassuringly expensive". He didn't have a clue, waffled at length BSing wildly - and loudly - to cover his ignorance, and sounded as if we were a bit cheeky asking him a few simple questions!
Some employment agencies offer a pre-testing service. I can see us reaching a point very soon when only pre-tested candidates will be worth interviewing for all bar the most junior roles. Having seen contractor rates decline steadily in real terms for several years now, I'd be delighted to see this translate into a two (or more)-tier skill/pay structure, even if it means upskilling from time to time. Okay, today then...
You just described in a nutshell 2 years of interviews the last time we went looking for someone at my previous employer. It's insane how little skill set is out there. And more than a bit shocking (although, based on the questions here on SSC, maybe not that shocking). My personal favorite was the guy who, after flubbing the basic 10 questions we asked everyone including the difference between a CI & a NCI, proceeded to lecture us that we were "too SQL Server focused." Instead we should be using straight generic structures and code so that we could switch to Oracle or DB2 on a moment's notice (the company has been running on SQL Server non-stop for 11 years now). And that is, by the way, after applying to a job explicitly listed as SQL Server only.
It seriously made me insane (ok, ok, more insane). I feel your pain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2011 at 6:18 am
I can attest to this. There are a lot of mediocre candidates out there that I wouldn't let near an Access database, let alone SQL Server. I suppose this is down to the fact that it is easier to get into SQL Server when compared to Oracle in terms of getting an initial free environment set up with an Express edition.
In terms of database development, I look for experience of other RDBMS systems - especially Oracle. I would also throw in some Oracle questions as a way of finding out how good they really are for general db concepts. This is only a loose criteria though, it does get rid of the blaggers though.
Describing yourself as an expert while not being able to differentiate between clustered and non-clustered indexes is unforgivable.
August 22, 2011 at 6:28 am
ChrisM@Work (8/22/2011)
Some employment agencies offer a pre-testing service. I can see us reaching a point very soon when only pre-tested candidates will be worth interviewing for all bar the most junior roles. Having seen contractor rates decline steadily in real terms for several years now, I'd be delighted to see this translate into a two (or more)-tier skill/pay structure, even if it means upskilling from time to time. Okay, today then...
I think if you try to use a pre-testing service you'll be shooting yourself in the foot; you will see lots of people with plenty of book knowledge (all acquired from one book, not from the real world) but without a clue how to apply it, and lose out on all the people who have real practical knowledge but commit what are, to the employment agency, unforgivable sins like using terminology other than that the agency uses (what do you call a "link" table, for example). When you ask the questions yourself you can detect the both the parrotted waffle and the use of a slightly different but still absolutely correct description of what you were hoping to hear; the agencies (in my experience, anyway) can't do that.
Of course instead of using an agency you should be using your network - who do you know who might know someone who might be suitable for the job - or placing your own adverts somewhere useful (although there are very few useful places now that the agencies dominate the recruitment scene).
Tom
August 22, 2011 at 6:34 am
Why not network here?? What are you looking for Chris? I'm sure 1 of us can recommend someone!
August 22, 2011 at 6:52 am
Lynn,
Good luck on the 1st day back in SQL Server and conrgratulations!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2011 at 7:01 am
ChrisM@Work (8/22/2011)
Hi folks, I've not visited here for ages, I hope you are all well.Anybody recruiting presently? We've been recruiting a DBA and a TSQL developer (with some role overlap for holiday cover) for several weeks. Around 25 cv's have passed over my desk, I've been involved in about 10 first (phone) interviews and three face to face second interviews. We have a set of fairly simple questions which were collected from suggestions here on this forum and elsewhere. The simplest of these were used to weed out the chaff at the phone interview stage.
It's been an eye-opening experience. Given that the first requirement on the job spec is three years' experience of TSQL development, only two candidates could provide a reasonable explanation of the difference between a clustered and a non-clustered index. One self-proclaimed "19/20 TSQL expert" stated that a CI is a "special index which makes queries run faster than a NCI". Few knew the purpose of include columns. Only one knew how to turn a left join into a NOT EXISTS with a filter in the WHERE clause (WHERE [ID or something not-nullable of rhs is null]), and he was also the only candidate who knew what would happen if the filter was changed to a non-null value.
What's really shocked our team here is the extent to which candidates haven't merely exaggerated, but been downright dishonest about their skillset claims. Hence two years sitting near a guy who did some SSIS development from time to time translated as "Two years experience of SSIS".
The last straw was a phone interview with a chap who looked on paper at least to be an excellent fit and was "reassuringly expensive". He didn't have a clue, waffled at length BSing wildly - and loudly - to cover his ignorance, and sounded as if we were a bit cheeky asking him a few simple questions!
Some employment agencies offer a pre-testing service. I can see us reaching a point very soon when only pre-tested candidates will be worth interviewing for all bar the most junior roles. Having seen contractor rates decline steadily in real terms for several years now, I'd be delighted to see this translate into a two (or more)-tier skill/pay structure, even if it means upskilling from time to time. Okay, today then...
The thing that bothers me most about this post is the guy BSing. I know how little I really know and I when I am interviewed I make that clear. Granted I know the answers to the basic questions you mention, but, if all you are doing is writing queries to return data, yes, you've spent 3 years developing for SQL Server, but you may not know anything about indexes because either someone else is taking care of it, or no one knows enough to think about it. We've all seen the databases that 3rd party apps have, how many are good ones. For goodness sake, if you use SQL Server to hold SSIS configurations, where you have to define a configuration filter, the table created by SSIS has no indexes, not even on the filter column that is used in every query.
And honestly, in my first 6 years working with SQL Server, which is 1/2 of the time I've worked with SQL Server, I rarely created indexes other than a primary key (using an identity surrogate). Did I know what an index was? Yes, but did I really understand them? No.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2011 at 7:04 am
Tom, there's an interesting and very valid point you make there. One of our two top candidates was skilled in a practical sense, plenty of experience, but wasn't used to communicating about SQL Server in the shared language which we use here, for instance. A few of our questions threw him, but with a little nudging he was able to provide explanations using his own language.
Ninja's_RGR'us (8/22/2011)
Why not network here?? What are you looking for Chris? I'm sure 1 of us can recommend someone!
We're sorted for now thanks Remi. One candidate has accepted an offer, the other post is on hold for a while. I hadn't considered posting here because Swindon is a bit out in the sticks! A London role would have been quite different.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 29,191 through 29,205 (of 66,549 total)
You must be logged in to reply to this topic. Login to reply