Schema Z – Remove Temp Tables

Local temporary tables are the unsung heroes of SQL programming — created in a flash, used extensively, and then often… left behind like forgotten leftovers in the fridge. Imagine a long script that creates temporary tables and references them throughout. Once executed, if you need to run it again in the same session, you’ll first … Read more

Simplifying Object Removal in Schema z

Removing objects in SQL Server can be straightforward, but it often requires you to know the specific type of the object you’re working with. For example: if object_id('obj1') is not null drop table obj1; or drop table if exists obj1; While these methods work for many object types, they require you to specify the object’s … Read more

Schema Z – String Operations

Schema z introduces objects manipulating string and simplifies your work, https://github.com/sqlnotes/sql/wiki/FunTricks#string-operations . select z.fn_LettersOnly('Hello World 123') — HelloWorld select z.fn_NumbersOnly('Hello World 123') — 123 declare @LongString nvarchar(max) = replicate('a^^', 300) select * from z.fn_SplitString(@LongString, '^^') option(MaxRecursion 0) declare @LongString nvarchar(max) = replicate('a^^', 300) select * from z.fn_SplitStringFixedLength(@LongString, 3) option(MaxRecursion 0) select z.fn_SecondsToTimeString(98765) — 1.03:26:05 declare @str … Read more

Eliminate Your Blockers

Picture this: you’re ready to unleash your groundbreaking SQL code, only to find it blocked at every turn. Suddenly, you’re the “hero” of the office, staring intensely at sys.dm_exec_requests, identifying blocking sessions like a detective in a mystery novel, and manually KILLing them one by one. You look extremely professional, your keyboard clattering like a … Read more

Schema Z – Naming Convention

The importance of object naming convention in a database system is a well known subject which will not be discussed in this article. Instead, this article focuses on how it’s defined and enforced in Schema Z. To enforce the naming conventions, function z.fn_GetExpectedName generates standard names for database objects. This function is referenced by views … Read more

Welcome to Schema Z

Over the past 30 years of working with SQL Server, I’ve realized that many routines—like user migration, partition management, and more—can be streamlined with a shared library. So, I decided to create one to simplify these complexities. The code is available at: https://github.com/sqlnotes/sql. Documentation is available at https://github.com/sqlnotes/sql/wiki. This library isn’t designed to solve every … Read more

Run T-SQL in Parallel

Writing CLR procedures to run T-SQL concurrently is not an extremely new idea. I have seen a lot of implementations and I have written and improved it many times by myself as well. After those coding exercises, I found few important things were not (or just partially) addressed.

  • Termination of launcher session: Either the launcher session get cancelled or killed, running asynchronous workers should be cancelled.
  • Different ways to shut down a batch: Waiting Workers should be abandoned. Executing workers should be either cancelled or waited to be completed.
  • Effective monitoring: People want to see which session is running what.
  • Adjustable maximum threads in the course of execution.

Read more

Get All Referenced Tables and Columns

I was always challenged when my customers asked me what tables and columns are referenced by a stored procedure which was written many years ago by the guy who left the company 5 years ago. When I Google the solution, I was always told that sys.sql_dependencies and sys.sql_expression_dependencies can tell. At the end of the day, I figured that depending on the complexity of the procedure, those 2 views couldn’t always give me accurate information as needed, dynamic SQLs for instance. Even worse, my customer also asked me if a table was accessed by anyone and what columns were referenced. I realized that I have to write something to get it done.

Read more

Print Long String

Quite often, I output information through PRINT command. It works well only when message is shorter than 8000 bytes. When the message is greater than 8000 bytes, extra-characters will be removed. the simplest way to overcome this is to make a loop and print 8000 characters at a time

declare @line nvarchar(max) = Replicate(cast('0123456789' as varchar(max)), 800)+'abc
'
select @line = replicate(@line, 3)
declare @pos int = 1
while @pos <= len(@line)
begin
	print substring(@line, @pos, 8000)
	select @pos = @pos + 8000
end
/*
012345678901234567890....
abc
012345678901234567890....
6789abc
012345678901234567890....
23456789abc
*/

Read more

Changed SoundEx Disables Index While Upgrading to SQL 2012

Implementation of SoundEx function is changed in SQL Server 2012, described here. For an example, the result of SOUNDEX(‘Csomorova’) in SQL Server 2012 is C561 whereas the result of it from SQL Server 2008 is C256. It does not seem very significant to our life. But it gives you surprise when this function is used … Read more