Archive for the ‘Database Development’ Category
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/11/29
I just got this error when SQL Server Management Studio 2012 was complaining about the owner of a certain SQL Server 2012 database and tried to copy that message to the clipboard:
This message cannot be copied to the clipboard.
Additional information
Current thread must be set to single thread apartment (STA) mode before OLE calls can be made.
Ensure that your Main function has STAThreadAttribute marked on it. (System.Windows.Forms)
Is there anyone who knows how to workaround this issue in SSMA?
–jeroen
Posted in Database Development, Development, Software Development, SQL Server, SQL Server 2012 | Tagged: SQL Server 2012, SQL Server Management Studio 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 »
Posted by jpluimers on 2013/10/22
I couldn’t find a table with numeric data types in SQL Server 2012 on MSDN, but since they have not changed since SQL Server 2008, I copied the table from Understand the 9 Numeric Data Types in SQL Server 2008, added an entry for bit, and links to the relevant SQL Server 2012 pages at MSDN.
Edit: somehow the WordPress editing system suppressed all the superscripts (for the powers of 2 and 10), so I replaced them with caret signs and powers of 2 and 10 to make it more clear and verified them against Floating point numbers and these Wikipedia pages:
Data Type Range of Values Storage Space
| Data Type |
Range of Values |
Storage Space |
| tinyint |
0 to 255 |
1 byte |
| smallint |
–32,768 to 32,767 |
2 bytes |
| int |
–2^31 to 2^31–1 |
4 bytes |
| bigint |
–2^63 to 2^63–1 |
8 bytes |
decimal(p,s)
numeric(p,s) |
–10^38+1 to 10^38–1 |
5 to 17 bytes |
| smallmoney |
–214,748.3648 to 214,748.3647 |
4 bytes |
| money |
–922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
8 bytes |
| real |
–3.4*10^38 to –1.18*10^38, 0, and 1.18*10^38 to 3.4*10^38 |
4 bytes |
| float(n) |
–1.79*10^308 to –2.23*10^308, 0, and 2.23*10^308 to 1.79*10^308 |
4 bytes or 8 bytes |
| bit |
0 to 1 |
0+ bytes |
Later I found an even more complete table at SQL Server Data Types Reference – ConnectionStrings.com.
–jeroen
via: Understand the 9 Numeric Data Types in SQL Server 2008.
Posted in Algorithms, Database Development, Development, Floating point handling, Software Development, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | Leave a Comment »
Posted by jpluimers on 2013/09/05
Darren Davies answered via SQL query to get the deadlocks in SQL SERVER 2008 – Stack Overflow a while ago listing a great SQL statement by Mladen Prajdić that shows how to do without the deprecated SP_LOCKS and SP_WHO2 (which is undocumented, and slightly different from SP_WHO) or the good old SP_LOCK2.
It is the textual equivalent of the Deadlock Graph, which is part of the SQL Server Profiler.
I like that profiler a lot (read this step-by-step intro if you haven’t used it), but some environments consider it too much power for a developer to use.
The SP_LOCKS documentation directs you to the sys.dm_tran_locks documentation, which is the base of the SQL below. It requires the mostly harmless VIEW SERVER STATE permission.
Finding out what to join in order to get some readable results suited for quick troubleshooting is quite an undertaking.
Mladen did all that, and this is his SQL: 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/08/22
I like this Delphi program very much: it is one of the database tools with the widest support of back-ends, and friendliest user interface I know.
Oh, and it is by a great Dutch company too: UpScene (:
So this is their release information:
2013-08-19:
This new release of Database Workbench brings new features and fixes for issues reported by our users.
The free Lite Editions will be released later.
Multi-DBMS developer tool
Database Workbench works natively with:
- Oracle Database
- Microsoft SQL Server
- Sybase SQL Anywhere
- MySQL
- Firebird
- InterBase
- NexusDB
More information about Database Workbench is available at the Database Workbench page, download your copy today via our downloads page, pricing information is available.
This release includes fixes for the InterBase, Firebird, MySQL and Microsoft SQL Server modules, as well as general fixes and small new features.
The full details and list of changes in 4.4.1 is available here.
–jeroen
via: News @ Upscene Productions.
Posted in Database Development, Delphi, Development, Firebird, InterBase, MySQL, OracleDB, Software Development, SQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, Sybase | 2 Comments »
The Wiert Corner - irregular stream of stuff 