Curious..about data

2021 was a strange year…mid way through a pandemic, enormously depressing on many fronts to me..and yet, it bought with it some unexpected joys. I never imagined, in the wildest of dreams, that I would get a chance to present at a prestigious non-US conference. But this year, I was selected to present at SQLBits, the biggest conference for SQL Server Data professionals in Europe and among the best professionally run events there is. They are doing a blended event this year, so you can attend from where you are- although given a chance I’d be in London in an absolute heartbeat!! Sign up to attend if you are reading this!

Details of my session are as below. I will be co presenting with the awesome Koen Verbeeck (b | t).

Finding and Fixing T-SQL Anti-Patterns with ScriptDOM

Quality code is free of things we call ‘anti-patterns’ – nolock hints, using SELECT *, queries without table aliases, and so on.
We may also need to enforce certain standards: naming conventions, ending statements with semicolons, indenting code the right way etc. Furthermore, we may need to apply specific configurations on database objects, such as to create tables on certain filegroups or use specific settings for indexes.

All of this may be easy with a small database and a small volume of code to handle, but what happens when we need to deal with a large volume of code? What if we inherit something full of these anti-patterns, and we just don’t have time to go through all of it manually and fix it? But suppose we had an automated utility that could do this for us? Even better, if we could integrate it in our Azure Devops pipelines?

ScriptDOM is a lesser-known free tool from SQL Server DacFx which has the ability to help with finding programmatic and stylistic errors (a.k.a linting) in T-SQL code. It can even fix some of these errors!
In this session, we will learn about what it is, how we can harness its power to read code and tell us what it finds, and actually fix some of those anti-patterns.
Join us for this highly interactive and demo-packed session for great insights on how to improve the quality of your code. Basic knowledge of T-SQL and Powershell is recommended to get the most out of this session.

ROOM 03

Thu 14:10 – 15:00

Oh, I did miss saying that the awesome Ben Weismann(b | t) will be our moderator as well..Hope to see some of you there if you are reading this!!

I was planning a blog post for a TSQL2sday..instead would up writing an obituary blog post for a dear friend who passed on yesterday.

Brian Moran was among the older members of the community. I was introduced to him by a mutual friend at a SQL saturday many years ago. I recognized him instantly as the person who with a beard (he didn’t have one when i met him) who wrote articles in SQLServerMagazine. There was one on statistics, i think, which was well written that i could recall well and i spent some time discussing that with him. It was actually a funny discussion because it was a really old article, one that he could not recollect writing, but one that I could recollect really well because it was one I read during my early years as a SQL Server DBA. But he managed to keep up the conversation with ‘hmmm’, ‘did i really say that’, ‘oh wow your memory is great’ and so on. He didn’t seemed bored or tired discussing something that old and was keen we become friends. I appreciated that and it started the beginning of a friendship that will remain among my sweetest memories.

What I really appreciated about Brian was his sense of humor – ability to laugh at just about anything. We were discussing careers once after he moved to microsoft in a sales position, and was doing more recruiting for them. He reached out to me and asked if i’d be interested in a salesy role and i said no, ‘i can’t sell a pin’. At the next sql saturday at DC , I was sitting at the swag table with some PASS Related swag to hand to attendees. There were some pins ..he came up and said ‘what do you have here?’… I handed him a pin, he turned his head and went ‘look what you just did..you sold me a pin..bwahahaha’…and then we met the Microsoft manager for the role, and he introduced me ‘This is Mala, she can sell a pin, really well. She is great ‘…we laughed a good amount of time after that.

I liked how much Brian enjoyed life, or seemed to. He spent a lot of time in beaches, loved naps and wearing crocs around even when he wasn’t at a beach. To me, tech is a way of finding time to do things like that, not an end in of itself and he was exactly that. I also liked informal, sloppy footwear a lot. With an arch problem I could not wear flip flops even when i wanted to and that left me mostly with crocs, most of which were oversized for a female with small feet like me. When i finally found some that matched my size..i actually texted Brian from the shoe store… ‘they are bright yellow, but seem to fit..what do you think?’…he responded ‘go for it!’. I bought those and they are still with me.

In Brian’s memory, when COVID is over, I will wear those bright yellow crocs to the beach..and enjoy a nap after. Or two. And laugh and hug more. RIP dear, kind, funny friend. Thank you for the cheer and brighness you bought all of us.

I will be delivering a lightning talk at DataMinutes on June 7th on this topic. I thought it would help to write a synopsis of what am going to talking of on my blog post.

SMO – essentially stands for SQL Server Management Objects…this is a .net based scripting model Microsoft created to perform several SQL Server based tasks using scripts. It has been around since SQL Server 2005 and many people have written scripts using .net languages or powershell to accomplish various tasks. Using SMO is increasingly important in ‘Infrastructure as code’ world. Databases are a really important part of this infrastructure. Most of us find it easy and familiar to generate scripts using SSMS or Azure Data Studio and would prefer to keep doing it the same way. But, when you have a large code base – this can become problematic in many ways. People have varied SSMS settings that can allow room for non standard scripting. Scripting out what we create to add to our database repository becomes an additional task to do. Automating scripting using SMO helps to generate scripts that are uniform/standard in appearance and save the time lost in generating them as well.

SMO can accomplish a great many tasks within SQL server – those include backp/restore, creating agent jobs, changing server settings…on and on. I choose to focus my attention on just scripting SQL Server objects in this blog post and in my talk.

My script i will be using for this talk can be found here . My talk is at 11 am EST and the conference schedule is here. There are many awesome speakers..hope you will consider attending if you are reading this. Thank you.

I restarted speaking with New Stars of Data today – I gave a talk on Database documentation. Below is the script I created to pull metadata into an excel file if you need it. Thank you Mladen Prajdic(t) for being the original inspiration for this.

--Query to pull data dictionary from sql server metadata.
--Author: Mala Mahadevan
--Date 3/11/2021 V1.00
--Read:
--Have xp_cmdshell enabled for export to excel file
--Run on database you need metadata from
--Works on SQL Server 2012+
--Set variable @outputfilename to whatever filename you desire output to go to.
DROP TABLE IF EXISTS ##TempExportData

DECLARE @DBName varchar(100),
		@SQLStmt nvarchar(4000),
		@ShellStmt varchar(8000),
		@OutputFilename varchar(100)

SELECT @OutputFilename = 'c:\ugpresentation\datadictionary.xls'

BEGIN TRY
SELECT @DBName = db_name()	
SELECT @SQLStmt = 'USE ' + @DBName + ';'+'
	SELECT  
	''Database'' AS [Database Name],
	''Schema'' AS [Schema Name],
	''Table Name'' AS [Table Name],
	''Column Name'' AS [Column Name],
	''DataType'' AS [Data Type],
	''Length'' AS [Length],
	''Precision'' AS [Precision],
	''Scale'' AS [Scale],
	''IsNullable'' AS [IsNullable],
	''IsPrimaryKey'' AS [IsPrimaryKey],
	''Primary Key Constraint'' AS [PK Constraint],
	''IsIndexed'' AS [IsIndexed],
	''IsIncludedIndex'' AS [IsIncludedIndex],
	''Index Name'' AS [Index Name],
	''Foreign Key Constraint'' AS [FK Constraint],
	''Parent Table'' AS [Parent Table],
	''Default Constraint'' AS [Default Constraint],
	''Comments'' AS [Comments]
	INTO ##tempExportData  
	FROM sys.tables
	UNION
	SELECT
	DB_NAME() AS [Database Name],   
	OBJECT_SCHEMA_NAME(T.[object_id]) AS [Schema Name],
	T.[name] AS [Table Name],    
	C.[name] AS [Column Name],      
	UPPER(TY.[name]) AS DataType,    
	CAST(C.[max_length] AS VARCHAR(10)) AS [Length] ,     
	CAST(C.[precision] AS VARCHAR(5)) AS [Precision],    
	CAST(C.[scale] AS VARCHAR(5)) AS [Scale],    
	IIF(C.[is_nullable] = 0,''N'',''Y'') AS IsNullable,   
	IIF(ISNULL(I.is_primary_key,0) = 0, ''N'',''Y'') AS IsPrimaryKey,   
	KC.name as [Primary Key Constraint],   
	(CASE WHEN IC.index_column_id > 0 THEN ''Y'' ELSE ''N'' END) AS IsIndexed,   
	IIF(ISNULL(is_included_column, 0) = 0, ''Y'', ''N'') AS IsIncludedIndex,   
	I.name AS [Index Name],   
	OBJECT_NAME(FK.constraint_object_id) as [Foreign Key Constraint],   
	OBJECT_NAME(FK.referenced_object_id) as [Parent Table],   
	DC.name AS [Default Constraint],   
	EP.value AS Comments 
	FROM sys.tables AS T	
	INNER JOIN 
		sys.all_columns C 
	ON T.[object_id] = C.[object_id]  
	INNER JOIN 
		sys.types TY 
	ON C.[system_type_id] = TY.[system_type_id] 
	AND C.[user_type_id] = TY.[user_type_id]   
	LEFT JOIN 
		sys.index_columns IC 
	ON IC.object_id = T.object_id 
	AND C.column_id = IC.column_id
	LEFT JOIN 
		sys.indexes I 
	ON I.object_id = T.object_id 
	AND IC.index_id = I.index_id
	LEFT JOIN 
		sys.foreign_key_columns FK 
	ON FK.parent_object_id = T.object_id AND FK.parent_column_id = C.column_id
	LEFT JOIN 
		sys.key_constraints KC 
	ON KC.parent_object_id = T.object_id AND IC.index_column_id = KC.unique_index_id
	LEFT JOIN 
		sys.default_constraints DC 
	ON DC.parent_column_id = C.column_id AND DC.parent_object_id = C.object_id
	LEFT JOIN 
		sys.extended_properties EP 
	ON EP.major_id = T.object_id AND EP.minor_id = C.column_id
	--ORDER BY T.[name], C.[column_id]
'
	EXECUTE sp_executesql @SQLStmt

	--select * from ##tempexportdata
	
	SET @ShellStmt = 'bcp "' + ' SELECT * from ##TempExportData" queryout "' + @OutputFileName + '" -c -T -CRAW'
	Exec master..xp_cmdshell @ShellStmt
END TRY

BEGIN CATCH
	SELECT  
    ERROR_NUMBER() AS ErrorNumber  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ,ERROR_PROCEDURE() AS ErrorProcedure  
    ,ERROR_LINE() AS ErrorLine  
    ,ERROR_MESSAGE() AS ErrorMessage; 
END CATCH

	

I am part of a weekly talk show we run at the TriPASS user group, called ‘Shop Talk’. Shop Talk was the brainchild of Kevin Feasel, our key user group lead..we meet on a bi weekly basis and discuss random tech topics related to sql server. Some of these are questions from our audience, and some are just ideas for discussion that one of us come up with. I am constantly amazed and grateful for how much I learn by being part of this show – from my co hosts and from the very intelligent audience we are blessed with. Last week, we discussed Brent Ozar’s blog post on ‘What SQL Server Feature Do You Wish Would Go Away?’. The recording of our discussion (this topic starts around 26:00) is here.
I am not blogging what we discussed in its entirety, the show speaks for itself there. I learned a few features, and use cases of SQL Server features that I never knew existed. Some of these are deprecated – so why is this important to blog about? It is, because if my resume says I’ve worked on this product for 20+ years – I better know what those features are as well. The topic of Brent’s post is a rather common interview question too – I’ve asked as an interviewer it once or twice myself, and I’ve been asked about it as an interviewee several times by several people. It helps to know more ways of answering it. Below are a few terms that I learned/re-learned as I had forgotten they existed, from the show.

Use cases for cursors

I did not consider cursors as among features that have to go away. Cursors are not ideal in a set based language but they absolutely have their use cases. But I heard of two use cases that I did not know of.
1 Leaky Bucket Algorithm
If we have a bucket into which water is poured in randomly. We have to get water in a fixed rate, to ensure that extra water gets out. This is possible by making a hole at the bottom of the bucket. It will ensure that water coming out is in a some fixed rate, and also if bucket will full we will stop pouring in it. So the idea is that the input rate can vary, but the output rate remains constant.

I found this cool blog post by Kevin Feasel again on not just the algorithm but a use case for it and why this use case demands cursors. Quoting from the post itself ‘  The problem is that we have not only a window, but also a floor and ceiling’ – this means using windowing functions is not possible. This is a genuine use case among others which makes it clear that cursors are needed although should be used sparingly.

2 Quirky Update
Remember the days before windowing functions? I do!! We had to do all kinds of workarounds to get running totals..there is no need for us to revisit that any more – but it helps to remember how things were done..the term ‘quirky update’ – using variables and cursors to do what windowing functions now do – came from this article by Robyn Page. Scrolling down to the code, we can see how she does it. I have used this, several times – and I recall how hard it was when you had to write SSRS reports with running totals with logic like this. This is not an argument to keep cursors, but not one to get rid of them either. We have other ways of accomplishing what Quirky Updates used to do, and that’s a good thing to remember, for sure.

Fibers Vs Threads
When Kevin asked me if I knew what fibers were – I drew a blank. I am not aware of any such term with SQL Server. Looks like this goes back all the way to Windows NT/SQL Server 6.5 days – I vaguely do remember ‘lightweight pooling’…’Fiber is multiple pieces of code set on a single thread to execute and controlled by an internal piece of code written in the SQL executable and not by Windows itself.’ This line from an old article made me laugh out loud ‘ Fibers, called lightweight pooling, should be turned on only if CPU is 100 percent and context switching is high.’ Needless to say in today’s world lightweight pooling is no longer necessary.

Numbered Procedures
I had no idea such a thing even existed..but apparently they do…and how it works is as below..you can group procedures by naming them with a <samename>;<incrementing number> as below.

CREATE PROCEDURE proc1;1
AS
BEGIN
SELECT 1
END
GO
CREATE PROCEDURE proc1;2
AS
BEGIN
SELECT 2
END
GO
CREATE PROCEDURE proc1;3
AS
BEGIN
SELECT 3
END
GO

DROP PROCEDURE proc1

The ONLY advantage of such a grouping is , apparently, the last line – you get to drop all of them with one call. I am not sure what was the motivation behind such a feature, but was glad to see it deprecated now.

Those are all the quaint/interesting things I learned during this show. Thanks for reading.

This month’s TSQL Tuesday invite is from James McGillivray – he asks people to write about what they were/are doing to take a break during this crisis ridden time we are in.
Breaks are very important – it is very easy to drive ourselves deeper into work when we are working from home. I personally still struggle with guilt that am not doing enough and tend to work harder than i am in a regular office. Limitations and breaks are really important for me. Below are a few things I do in this regard.
1 Take short walks – I live near a wooded area and it is easy to take a stroll out of home up the lane and back. We get a lot of sunshine even in winter in NC, so it is an added opportunity to get some much needed Vitamin D as well. I take a lot of walks, depending on the weather – a minimum of two to a maximum of 5. I always feel fresh and invigorated after a walk.
2 Coloring books – this is a hobby I enjoy very much. I have a lot of different types of books – mandalas, landscapes, flowers…various. I take one and color it vigorously. It is amazing how much stress relief this can bring.
3 Meditation – I cannot not emphasize how important this is. I recently ran into something called ‘tapping meditation’ and use this – it is a combo of acupressure based tapping and guided visualisation on positive thoughts. I meditate twice a day, for my health and general well being.
4 Watch random television – when I am tired I tend to lean back into rewatching stuff I’ve enjoyed. That includes old classic movies and tv serials – my particular favorites are Downton Abbey and ER. I know lots of little things from rewatching these over and over and still laugh at the jokes. It helps me destress.

These are a few of my little stress breakers. Thanks for reading.