The T-SQL Quiz
-
Hotfix build 2153 claims to fix some CLR memory leak problems (also claimed in SP2 as it includes the build 2153 hotfix).
If you are actively using the CLR in SQL Server, monitor your log files for AppDomain load/unload messages. That's indiciative of a CLR memory leak.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
"A memory leak may occur in the common language runtime (CLR) if you pass a CLR user-defined data type as a parameter by using a stored procedure or the sp_executesql procedure."
That's the "infamous" leak? I've barely seen anyone touch UDTs so far, let alone pass them as parameters. This is certainly not something I would worry about, and you might notice that my solution didn't use UDTs anyway. No leak, infamous or otherwise, will result.
--
Adam Machanic
whoisactive -
Yes, I saw that typo when I posted the query but did not bother to update the post.
And it's good that I would fail the test.
Because if you don't accept applicants because of typos then you rule best guys out of your company.
You gonna end up with full house of "MS certified idiots" (copyright of my friend, holding 5 MS certificates).
I would not be happy to work in such environment.
_____________
Code for TallyGenerator -
Let's assume that we have the numbers populated in some table #numbers (the same solution will work with CTE)
As far as I tested, the following solution about 2 times faster than original Sergiy's solution (and, I guess, more readable) :
set
nocount on
declare
@substitutes table
(
number
int,
substitute
nvarchar(10)
)
insert
@substitutes values (0,'BizzBuzz' )
insert
@substitutes values (1,'Bizz' )
insert
@substitutes values (2,'Buzz' )
insert
@substitutes values (3,null )
SELECT
ISNULL(b.substitute, CAST(a.Number AS nvarchar(10) )) as res
from
#numbers a inner join @substitutes b on sign(a.Number%5) + 2*sign(a.Number%3) = b.number
order
by a.number
-
Nice. I like it. Simple, clean. Good job.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Nice query, violated the rules though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
WHOA!
Great post. Very nice solution. Scalable. That was something I honestly hadn't thought of. We were just having fun with the problem statement. But, in my, and my co-worker's, defense, an expedient solution can be the right one too. Not everything has to scale to a fare thee well all the time (although, you are 100% correct about keeping it mind). Sometimes good enough is good enough.
Nice job again. Looking for work?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I had this query from Itzik too. I didn't want to use it in relation to the article. It works really well for populating a table of numbers, which, as someone else said, should be in place already.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Oh boo. That's so procedural. Not set based at all.
What? Are you some kind of developer or something?
Nice job though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Absolutely more appropriate languages than SQL. I'm not serious about putting this on an interview. This was just some geek fun that we had one morning at work. Actually we get rid of more interviewees by asking them to explain the difference between a clustered and a non-clustered index. That weeds out 3/5 of the people looking for DBA spots in a single question. Just how scared does that make you?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
WOO HOO!
A CLR solution. Thanks Adam.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Yikes.
I haven't seen that many GOTO's in quite a while. Nice job.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Forgive us our typos. Absolutely. The most you'd get gigged for only going to 99 is some good natured ribbing about being premature or something.
It's amazing how much discussion what I thought was a silly little bit of fun has generated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Good article. Nothing like a challenge to get others at work interested who normally might not be. I'd be curious to hear what your gang thinks of all the other solutions posed.
-
Doesn't surprise me at all that asking the difference between clustered and non-clustered indexes weeds out most people. But hopefully only peopel that are interviewing for a DBA position for the first time.
And: Loops are fun! So pffft!
Viewing 15 posts - 46 through 60 (of 309 total)
You must be logged in to reply to this topic. Login to reply