Posted by jpluimers on 2014/04/08
Some great venn diagrams of SQL JOINs via Data Visualization – Google+ of which I wish they were PNG:
Thanks to that link, I found the original article and images by C.L. Moffat!
Click on the image to view a larger version.
–jeroen Read the rest of this entry »
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, 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/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')
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/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/01/18
In most database index nodes are doubly linked to allow bi-directional scans. http://is.gd/8CMb7w, however not for InterBase and FireBird, there the reverse link isn’t used because it can be inconsistent due to write order of index pages.
The result is that in Firebird and InterBase, indexes are single-directional (either ascending or descending).
This is for your safety: it guarantees index consistency, even if because of EMP, your machine suddenly reboots after your tank fired a missile.
–jeroen
via Twitter / Avalanche1979: @SQLPerfTips For Firebird the ….
(Wow, did I really wrote 1200 blog posts?)
Posted in Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 7, Sybase | Leave a Comment »
