The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,575 other followers

Archive for the ‘SQL Server 2005’ Category

SQL Server Management Studio does have a Registered Servers window (via: Server Fault)

Posted by jpluimers on 2014/03/26

One of the things I thought were missing from SSMS when coming from an ISQLW background was the “registered servers” window.

Well: it is still there, and this is how to set it up on an efficient way (:

–jeroen

via: mssql ssms – How can I have SQL Server Management Studio 2008 connect to my instances automatically? – Server Fault.

Posted in Database Development, Development, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »

SQL Server: learned that you can pass a Style to CAST and CONVERT (Transact-SQL)

Posted by jpluimers on 2014/03/19

Funny way to learn something new:

I hardly use CAST and CONVERT (Transact-SQL), and when I do, it is the standard SQL-92 way.

But a while ago, I came across some code like this:

CONVERT(DATETIME, "31/12/2013", 105);

and wondered what the 105 was.

And it appeared to convert from the Italian date format to DateTime. And that it has been there since at least SQL Server 2000, probably earlier.

Not sure why the passed slashes (/) in stead of dashes (-) as separators though.

There are styles for these groups of conversions:

  • Binary
  • Date/Time
  • Float/Real
  • Money/Smallmoney
  • XML

Never to old to learn something new (:

–jeroen

Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »

How to setup and use a SQL Server alias

Posted by jpluimers on 2014/03/03

Just in case I need this ever again: How to setup and use a SQL Server alias.

Posted in Database Development, Development, SQL, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »

SQL Server: strange way of getting query statistics.

Posted by jpluimers 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.

Note the maximum accuracy is about 3.3 milliseconds.

The script is based on these SQL server features:

I might add a try/catch to fake a finally in case the #sma insert fails. Read the rest of this entry »

Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »

floating point – SQL Server: Calculation with numeric literals requires to cast to obtain the right precision (via: Stack Overflow)

Posted by jpluimers on 2013/12/24

This has bitten me so many times, so I’m glad I found the below question/answers on StackOverflow.

When you perform calculations in SQL Server involving numeric literals, you have to take into account which precision you want your result to be, and CAST/CONVERT  the literals accordingly.

The reason is condensed to this statement by Lieven Keersmaekers:

SQL Server uses the smallest possible datatype.

He follows with examples to view the actual representation of a literal/expression using SQL_VARIANT_PROPERTY (which has been there since at least SQL Server 2000).

SELECT SQL_VARIANT_PROPERTY(1.0, 'BaseType')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Precision')
SELECT SQL_VARIANT_PROPERTY(1.0, 'Scale')
SELECT SQL_VARIANT_PROPERTY(1.0, 'TotalBytes')

Read the rest of this entry »

Posted in Algorithms, Database Development, Development, Floating point handling, Software Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | Leave a Comment »

 
%d bloggers like this: