SQL Coding
There’s an EOMONTH function in SQL Server, why no Start-of-Month function?
I responded to a posted question about the EOMONTH() function, which was: if there’s an EOMONTH function, why isn’t there a start of month function. The reason is that DATEFROMPARTS does the trick. It was added in SQL 2012 along with EOMONTH and DATETIMEFROMPARTS and…
Using Column Permissions to Protect a Look-up Table.
This article continues where the last one left off. If you’d like and go back and read that one you can find it here. In that article, an updatable view was created to replace an existing table. The table had been named myData and was renamed myDataBaseTable. The view…
Azure Databricks KKBox Hands-on-Lab with a comparison to SQL Server
I attended a webinar on Microsoft Databricks yesterday. The only link I have is a zoom registration so you’ll have to search. I’m sure they’ll run it again. The hands-on-lab made it very worthwhile. The rest was marketing fluff. They did a good job of the Lab and…
Using a UDF and Computed Column to replace a Column with a Lookup.
A reader, Rebecca, wrote asking: I have a table that contains a column. That table and column are used throughout our web site. The column is no longer valid and needs to come from another table. I need to quickly do this without having to change all of the code in…
Checking for Primary Keys
There are many reasons for almost every table to have a primary key. This articles UDF locates any user tables that don’t have primary keys. Here’s the CREATE FUNCTION script: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE FUNCTION dbo.udf_Tbl_PKeyMissingTAB…
Read any Windows Registry string value from T-SQL
A while ago I did a presentation about software distribution for Smart Client applications using .Net technologies. Part of the discussion was about the history of software distribution. Fifteen years ago problems were often dominated by DLL Hell and the Windows…
Comparing the Performance of Rot13 Implementations
The previous two articles have been devoted to implementations of the Rot13 cipher. This is a character substitution cipher that is used to obscure text strings in the Window registry and in many web cookies. Both implementations were contributed by Luke Schollmeyer. …
Alternative Rot13 Implementation.
This article was about ufn_Rot13 a letter exchange cipher contributed by Luke Schollmeyer. The Rot13 cipher is often used for very basic hiding of text. The Windows Registry uses it and it’s also used in many web cookies. ufn_Rot13 uses a loop to compute each letter…
Obscure Data with the Rot13 Cipher.
How protected is the data in your database? For most databases, the answer is: “Not very”. Have you ever thought of encrypting your database? Make no mistake about it your database is vulnerable to prying eyes. SQL Server now has several options that offer varying…
Validate Credit Card Numbers with the Luhn Function
Credit card numbers are among the most common numbers in commercial use today. Most credit card numbers and many other numbers used in financial services use the Luhn (a.k.a Mod 10) formula for check digits. It’s been formalized as part of the ANSI X4.13…
Improve SQL String Parsing
The number of ways to receive information seems to increase all the time. One of the ways to get information from the web is an RSS feed. If you use a feed reader. This issue features a contribution from reader Nick Barclay from Australia. The function, InString, is…
Translate ZIP Codes to State Codes
It’s time to mail your holiday cards. Whether it’s Christmas, New Years, Chanukah, or something else, there are going to be billions of cards sent in the next few weeks. One of the tasks that my wife and I face each year is getting all the addresses correct, which…
In-Memory Tables with Natively Compiled T-SQL: Blazing Speed for OLTP and More
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…
100 Times Faster: Experiences Making SQL Server Fly
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…
Entity-Attribute-Value (EAV): The Antipattern to Great to Give Up
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…
Natively Compiled T-SQL: The Fastest SQL Ever!
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. …
Writing Faster T-SQL Stored Procedures and Functions: Lessons Learned
Stored procedures and functions are the mainstay of SQL Server coding. This presentation will show techniques to improve their performance.
Formatting a Datetime for use in a File Name
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…
A Readable Data Type from System Tables Columns
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…
Find out Which View Columns can be Indexed
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…