Just in case I need this ever again: How to setup and use a SQL Server alias.
Archive for the ‘SQL Server’ Category
Posted by Jeroen Pluimers on 2014/03/03
How can you force SQL Server 2008 R2 to accept an ORDER BY on a column that is not part of a table? – Stack Overflow
Posted by Jeroen Pluimers on 2014/02/04
I’m sure there are many organizations that only upgrade things until they absolutely have to (i.e. long after mainstream support has ended, often even after extended support has ended). This was from last year: upgrading away from SQL Server 2000 just before extended support ended. While migrating a bunch of applications we inherited from SQL Server 2000 to SQL Server 2008 R2, I came across an ORDER BY style that failed. The queries are generated by an kind of SQL generation layer, so not easy to change. the main questions were:
- is it possible to force SQL Server 2008 R2 to accept this kind of queries and perform the SQL Server 2000 behaviour (so we can fix the SQL generation layer, and perform regression on it)?
- why would SQL Server 2000 happily accept this kind of queries?
First two possible fixes, then the full stack overflow question I posted about the migration.
Posted by Jeroen Pluimers on 2014/01/22
A while ago, I came across an interesting post Bad Habits to Kick : Using AS instead of = for column aliases by Aaron Bertrand, a major contributor on SQLblog.com – The SQL Server blog spot on the web.
The last link indicates my problem with this “AS” versus “=”: it is SQL Server specific.
So, if you mainly use SQL Server, then it is OK (or even preferable) to use “=” for aliasing columns in human written SQL as it makes spotting the names used much easier. Read the rest of this entry »
Posted by Jeroen Pluimers on 2014/01/14
Last year, I had a very odd project at a client.
Their monitoring software was quite odd, and there was no time to create/test/implement a module for it doing SQL query performance measurement any better.
The odd software had two ways of looking at queries:
- in a succeed/fail fashion
- in a count(*) fashion
Don’t ask why it was that way, the monitor was hysterically grown.
So below is a small query script that does what the odd monitoring software can do: provide a select with rows indicating the query response time.
What is does is insert into the #sma temporary table a number of records depending on the query duration.
The partition here is 1 record per 125 milliseconds, aiming for four partitions (green, yellow, orange, red) in half a second.
The script is based on these SQL server features:
- Local temporary tables
- while loops
- T-SQL variables
- A max query duration of about 24 days
Posted by Jeroen Pluimers on 2014/01/04
David Rodriguez posted a few nice SQL related entries on G+:
- Another great tool is DevArt’s SQL Complete. I swear SQL….
- Holy Hell another great tool: http://sqlfiddle.com/ and its….
- Use a LIKE statment on SQL Server XML Datatype – Stack….
- Querying a SQL Server XML Datatype….