SQL Coding

SQL Coding

In-Memory Tables with Natively Compiled T-SQL: Blazing Speed for OLTP and More December 1, 2016 in-memory-tables-with-natively-compiled-t-sql-blazing-speed-for-oltp-and-more
The SQL Server 2014 and 2016 Hekaton project brings us In-Memory tables and Natively Compiled T-SQL objects that run at amazing speed.  What you can accomplish is astounding. This presentation will introduce you to the Hekaton technology and show how big the differences can be and where you can put it to best use starting … Read More

100 Times Faster: Experiences Making SQL Server Fly September 20, 2016 Download the presentation: 100-times-faster-experiences-making-sql-server-fly-sql-saturday-prov-2015-12-12
How do you go about making a program run a lot faster!  Like 100 times faster?   It’s difficult.  Microsoft tried in the Hekaton project, its in-memory tables feature of SQL 2014, and they got close.  Inspired by that effort, this presentation looks at some recent cases where I’ve been able to achieve … Read More

Entity-Attribute-Value (EAV): The Antipattern to Great to Give Up September 19, 2016 Download the presentation: entity-attribute-value-eav-the-antipattern-too-great-to-give-up-andy-novick
The Entity-Attribute-Value (EAV) is a design pattern for relational databases that uses an extreme form of normalization to achieve flexibility and the ability to efficiently store temporal data that changes frequently.  EAV has its critics and the flexibility it provides creates opportunities for abuse and introduction of bugs leading some to call … Read More

Natively Compiled T-SQL: The Fastest SQL Ever! September 18, 2016 Download the Presentation .Natively-Compiled-T-SQL-Andrew-Novck-presentation
Demo files: Natively-Compiled-T-SQL-Andrew-Novck-demo-sql
SQL Server’s In-Memory tables are accompanied by Natively Compiled Stored Procedures and Functions that run at amazing speed.  However, the speed boost comes with tradeoffs.
The complete T-SQL language is not implemented, only key parts and we’ll discuss these limitations and how to get around some of them.

Transaction handling is different … Read More

Writing Faster T-SQL Stored Procedures and Functions: Lessons Learned April 28, 2015 Stored procedures and functions are the mainstay of SQL Server coding. This presentation will show techniques to improve their performance.
Read More

Formatting a Datetime for use in a File Name September 1, 2014 I’ve been working with scripting SQL traces over the last few weeks. To minimize the overhead of the trace, it can be sent to a file instead of the SQL Profiler or a table. I’m working on an article for Database Journal on the topic.
As part of the process, a unique file name has to … Read More

A Readable Data Type from System Tables Columns August 31, 2014 I’ve written a new article on Indexed Views that has been www.DatabaseJournal.com As part of that article I included a UDF, udf_View_ColumnsIndexableTAB, that shows which columns in a view are eligible for indexing. It’ll be next week’s UDF of the week. But before I show you that UDF, I have to show you this week’s UDF.
This … Read More

Find out Which View Columns can be Indexed August 31, 2014 Indexed views are the topic of an article that I’ve written. It was published last week on DatabaseJournal. You can see it at: http://www.databasejournal.com/features/mssql/article.php/2119721 You probably got the special notice that I sent last week when it was published.
There was a correction to the article posted on Friday. It relates to how indexed views work … Read More

Transact-SQL User Defined Functions – PDF available for free August 31, 2014 It was written quite a while ago but if you’re starting out using functions in T-SQL, it’s still a good resource. Except for the section on system UDFs the rest of the book remains correct. There just haven’t been that many changes in how functions work since SQL 2000.
Transact-SQL User-Defined Functions by Andrew Novick (A … Read More

Retrieving a Random Record in SQL Server using udf_Num_RanInt August 1, 2014 This UDFs week solves a problem posed to me last week by a friend Gary.  Gary wants to select a random row from one of his tables and was hoping that SQL Server would have some support for this task.
The idea of selecting a random row runs counter to the ideas that are the basis of the … Read More