Skip to main content

Posts

Caching Temporary Table DDL

When creating temporary tables within stored procedures with indexes, there is an opportunity for performance gains.  SQL Server will cache the DDL of temporary tables as long as there are no DDL changes after the creation.  As confusing as that may sound, it will make more sense by looking at the code.  The following are two procedures, one to create a non-cached table and the other cached.
CREATEPROCEDURE dbo.NoncachedTempTable AS /****************************************************************************************   Object Description:   Create a noncached temp table and enter 10000 random numbers then drop
  Revision History:   Date         Name             Label/PTS    Description   -----------  ---------------  ----------  ----------------------------------------   05/21/2017   Dan Andrews      PTS: 00000    Initial Release ****************************************************************************************/ BEGIN CREATETABLE #TempTable(ID        INTIDENTITY , randomVal INT); CREATE…
Recent posts

Understanding Data Warehouse Terms with SQL Server/Azure

It is important to understand several terms before implementing a Data Warehouse in a SQL Server/Azure DB.  There are three common names for database architectures: Star Schema, Snowflake Schema, and Columnar Tables.

Series: Relational Databases VS GraphDB, Part 1

What are Relational Databases?

Relational databases store their data in columns and rows.  Rows are also called tuples or records.  Together, the columns and rows make tables which have relationships (keys) to other tables.  One table has an unique Primary Key, while the other table has a Foreign Key in a One to Many relationship.

What are Graph Databases?

Relational databases are the current most common database technology and have been for many years.  However, they are not the only horse in the race.  GraphDBs are the child of fellow math geeks who found uses for Graph Theory.  Graph Theory is the study of the connections of vertices, nodes, or points which are connected by edges, arcs, or lines.  Like a GraphDB, each of these objects can have their own properties.  For example, lines can have magnitude and nodes can have weight.  GraphDBs such as Neo4j, have two main objects: Nodes and Relationships (which connect the nodes).

SQLCMD for Multiple Databases Using a .BAT (batch) File

I utilized SQLCMD so that we could parameterize the databases and not use dynamic SQL.  If you're not familiar with SQLCMD mode, please read here.  In SSMS, you can turn on SQLCMD mode by going to Query > SQLCMD Mode


Sharing Template Explorer in SSMS

I love SSMS Templates, and before Snippets, I used for both reasons.  To access SMSS Templates, go here: View>Template Explorer or Ctrl+Alt+T.  I use them as a place to store those infrequently used SQL Queries.  Those queries that a DBA doesn't want rewrite and reinvent or spending a lot of time remembering where they were last saved.

Excursions in SQL Server: Recursive CTE explained

Common Table Expressions (CTEs) have been a feature of SQL Server since 2005.  It is an under utilized feature mostly because you can NOT use them and write efficient code.  However, understanding what they can do, what they're good at, and what they're not good at will add to any developer's tool belt.  CTEs are a lot like views, only less rigid.  They can also reference themselves and as will be shown, can be used for recursive calls.