64,339 reputation
886168
bio website
location Rhode Island
age 40
visits member for 3 years, 7 months
seen 3 mins ago

Senior Consultant at SQL Sentry, SQL Server MVP, @AaronBertrand on twitter, blogger at sqlperformance.com and sqlblog.com, Mac user, volleyball player, curler, hockey/football/tennis fan (Go Bruins!), proud father and Canadian, eh?


7h
comment How can I use expressions as arguments to EXEC?
@Martin sure, but not RAISERROR('String %s', 1, 0, @var1 + @var2); :-[)
10h
answered How can I use expressions as arguments to EXEC?
10h
comment How can I use expressions as arguments to EXEC?
Did you mean @schedule_name = @ScheduleName,?
12h
comment How to get OUTPUT $action, Inserted.*,Deleted.* records in In SSIS
Before you invest too much in MERGE, please read this. IMHO you're better off keeping your inserts, updates and deletes separate.
13h
comment Database with only text fields
sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/… sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/…
13h
revised SQL SERVER – Find Most Expensive Queries
added 380 characters in body
13h
comment SQL SERVER – Find Most Expensive Queries
Did you search? sqlskills.com/blogs/paul/…
13h
comment SQL SERVER – Find Most Expensive Queries
Sure, you can clear out the procedure cache nightly using DBCC FREEPROCCACHE; - just note that it will cause a performance hit the first time any of these folks run queries because the plans will have to be recompiled. As for recommendations, SQL Sentry Performance Advisor. Disclaimer: I work for SQL Sentry.
14h
answered SQL SERVER – Find Most Expensive Queries
14h
comment ASSIGN value for cmd in sysprocesses? What does it mean?
You haven't answered my question though - at the time sysprocesses is showing ASSIGN, what is sys.dm_exec_requests showing? This could help solve the question...
1d
comment SQL database rejoined to new SQL Server 2008 R2. Users are still unable to connect
So try FQDN and IP address (and please try a tool that takes away at least two or three layers of complications, like Management Studio). If there's nothing in the SQL Server error log, then the request isn't even getting there.
1d
comment SQL-Server 2012: Same query takes different Query Plans - but why?
sommarskog.se/query-plan-mysteries.html
1d
comment SQL database rejoined to new SQL Server 2008 R2. Users are still unable to connect
Also verify that remote connections are enabled, the browser service is running. My guess is that if you connect with Management Studio you would get this much more common error: A network-related or instance-specific error occurred while establishing a connection to SQL Server..
1d
comment SQL database rejoined to new SQL Server 2008 R2. Users are still unable to connect
Are you sure that the machine can actually reach that server? Tried pinging it by name? How about telnet on the port SQL Server is running? This could be a firewall issue (say a rule that was created for the old server that wasn't re-created for the new server) or a DNS issue. Have you tried reaching it by FQDN or IP address? Also try with Management Studio or something other than some custom Access application - much better chance at proper debugging. Check the SQL Server error log and see if the request is even making it to the server.
1d
comment SQL-Server 2012: Same query takes different Query Plans - but why?
Could also be different plans because of different set options (or if you don't schema prefix, different default schemas) for different users/apps. Impossible to tell without seeing the two plans and being able to compare the fast one with the slow one. Please upload actual, post-execution plans, not estimated plans or the XML pulled from the cached plans DMV.
1d
comment Strange query results for DELETE with ROW_NUMBER
Just FYI result sort order without an outer ORDER BY is arbitrary. I'm not saying the delete behavior is correct but don't make assumptions about how results should be ordered.
2d
comment Why does a UNIQUE constraint allow only one NULL?
Artificial unique identifiers are a joke, sorry. How are you going to do that for a VIN? If you don't know what it is, why make something up? Just to take up extra disk space? Seems like nonsense to work around some other problem (like not wanting to write the application in such a way that it gracefully handles NULLs). If you absolutely need to know why something is NULL (exists but unknown vs. know it doesn't exist vs. don't know or care if it exists, for example), then add some kind of status column. Tokens just lead to awkward trickle-down code to deal with them.
2d
comment Why does a UNIQUE constraint allow only one NULL?
@supercat I'm getting lost about your actual objection here. Are you objecting to NULL values in general? Or only when used in a key column? As I stated before, there are cases where you might want a NULLable key, and you only need to enforce uniqueness on the non-NULL values (in fact the way it's defined in the standard, just not the way SQL Server implemented it). That doesn't mean you should be able to identify any of those NULL rows by that key column - presumably you would be identifying rows by key columns that are not NULLable. People enforce uniqueness for reasons other than identity
2d
comment Why does a UNIQUE constraint allow only one NULL?
@supercat So you're going to store that long string in every row where you don't know the color? What does that buy you over NULL, except for using a lot more disk space and memory for no reason? Please don't come near any of the database instances I need to maintain and make perform. That also doesn't address how you would do that in a unique column... Are you suggesting a sequence you increment at insert time, like couleur {1}, couleur {2}, etc.? sigh
2d
comment Why does a UNIQUE constraint allow only one NULL?
@supercar forget SSN, I already said it was a terrible example. Try to think about it conceptually. There are certainly cases where you would want to allow more than one NULL; in those cases, this is how you could still enforce uniqueness on the non-NULL values. Not useful to you? Okay, don't use it. Use garbage unique token values instead. Have fun!