Archive for the ‘SQL Server 2008 R2’ Category
Posted by jpluimers on 2014/03/25
As a SQL Server developer, most of us make extensive use of SQL Server Management Studio.
With lots of production database, I have a directory full of shorcuts that pre-fill server/database/… on the command-line.
One of the drawbacks these shortcuts have is that the Object Explorer stays empty, and does not sync with the currently active Server/Database.
While browsing to solve that, I found that the
Alt+F8 shortcut will focus the SERVER in the Object Explorer.
This was explained by StackOverflow user Andrei Rantsevich who had this great answer on StackOverflow: Read the rest of this entry »
Posted in Database Development, Development, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
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 »
Posted by jpluimers 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.
Aaron Bertrand very quickly posted two fixes, which I paraphrased and extended. Read the rest of this entry »
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2008 R2 | Leave a Comment »
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 »
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 »
Posted by jpluimers on 2013/12/04
From my link archive:
Note that for importing decimal/numeric columns, you have two options:
- Cast through FLOAT using a FORMAT file
- Use OpenRowSet with VARCHAR, then CAST afterwards
Weird rounding for decimal while doing a bulk insert from a CSV.
Some more links on this:
–jeroen
Posted in Algorithms, CSV, Database Development, Development, Floating point handling, Software Development, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2013/10/30
I need to do some research to automate the backups and restore sequences of some SQL Servers.
Here are some links and notes to get started:
Posted in Database Development, 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 »
Posted by jpluimers on 2013/10/24
“there’s nothing so permanent as temporary” can apply to many things, for instance Kitchen and software development (there technical debt is very applicable), the financial top gap measures (which are real debt) of fanfiction. You can apply it to SQL Server as well. The TempDBhas been there since before SQL Server 7, which means it has established a permanent feature for quite some time now.
Your DBA (which might be you) needs to watch the temdb size or space on the separate volume where temdb is stored, or someday the TemDB access patterns will cause havoc.
The most used feature (there are more) in TempDB is temporary tables (often abbreviated to “temp tables”), which – since TemDB got there – has come in three flavours:
The table variables are created and released implicitly. The temporary tables (one of the Special Table Types) can be created either explicitly using a CREATE TABLE, or implicitly using SELECT … INTO. You’d think that temporary tables are indeed temporary. But they are not:
Temporary tables are semi-temporary. Not actually permanent, but not fully temporary either.
All flavours of temporary tables are not being fully deleted when they go out of scope. When they go out ot scope, they will get an implicit/automatic truncate to empty them (so there is no manual TRUNCATE TABLE or DROP TABLE needed). But the table itself lives on including any cached plan information. They can, and often will be reused. And that’s where you should start reading these links:
One more thing: as of SQL Server 2012, the OBJECT_ID associated with temporary tables is negative.
–jeroen
via:
Posted in Database Development, Development, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7 | 2 Comments »
Posted by jpluimers on 2013/10/23
Thanks StackOverflow users George Stocker for asking, Örjan Jämte and Sir Crispalot for answering.
Below is the SQL Server 2012 table, in which I added links to the various data types.
I also added two columns with linked references to the types from the C# data types, C# Keywords, Reference Tables for Types (C# Reference) and Data Type Summary (Visual Basic).
One of the things I need to check is against the LINQ SQL-CLR Type Mapping.
It is very important to keep in mind that in SQL, each combination of precision and digits gets you a different decimal type, and all of them are different from the .NET decimal type. See for instance the answers on these questions:
Read the rest of this entry »
Posted in .NET, .NET 2.0, .NET 3.0, .NET 3.5, .NET 4.0, .NET 4.5, Algorithms, C#, C# 1.0, C# 2.0, C# 3.0, C# 4.0, C# 5.0, Database Development, Development, Floating point handling, Software Development, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
The Wiert Corner - irregular stream of stuff 