A blog about SQL Server, Books, Movies and life in general
This is day fifteen of the SQL Advent 2012 series of blog posts. Today we are going to look at the benefit of indexes So how does an index work? How does an index work, how does it help SQL Server finding stuff faster? Here is an simple non technology explanation. If I told you to grab a cookbook and give me all the recipes in that book for cod, what would you do? There are two things yuo can do,…
We have several domains at my job, we have a dev domain, a prod domain and then several other domains. Recently we had some domain changes where we would connect to a different domain when logging in from the laptops. When opening up SSMS and connecting to the SQL Server instances, we now need to use a fully qualified domain name (FQDN) and also specify the port number and the instance name. This…
Very similar to my After 20+ years in IT .. I finally discovered this... post, I discovered yet another command I should have know about Originally I was not going to write this post, but after I found out that several other people didn't know this, I figured what the heck, why not, maybe someone will think this is cool as well Open up a command prompt or powershell command window, , navigate to a…
PostgreSQL 13 Beta 1 was released yesterday, you can read the release notes here https://www.postgresql.org/about/news/2040/ One thing that caught my eye was this statement in the release notes PostgreSQL 13 brings more convenience to writing queries with features like FETCH FIRST WITH TIES, which returns any additional rows that match the last row. This is I guess exactly like TOP WITH TIES in…
I was looking at some code I wrote the other day and noticed the WAITFOR command.. This got me thinking. How many times have I used WAITFOR in code, probably as much as I have used NTILE :-) I looked at the documentation for WAITFOR and notice there is TIME in addition to DELAY. Oh that is handy, I always rolled my own ghetto-style version by calculating how long it would be until a specific time…
The other day I was thinking about the blog post Faster temp table and table variable by using memory optimization I read a while back. Since you can't believe anything on the internets (no disrespect to whoever wrote that post) , I decided to take this for a test In this post I will be creating 2 databases, one is a plain vanilla database and the other, a database that also has a file group that…
As we finish the tumultuous 2010s and are ready for the roaring 2020s, I decided to take a quick look at the ten most viewed posts from the past decade. Two of these posts were made posted before 2010 Without any fanfare, here is the list 10. Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection This is my recap of the chalkboard session with…
This morning I was greeted by the following message from a job email The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72059165481762816. Dropping and re-creating the index may resolve this; otherwise, use another clustering key. [SQLSTATE 42000] (Error 666) Almost Halloween? check! Error 666? check! Ever seen this error before? no! The job…
This question came up the other day from a co-worker, he said he couldn't change a query but was there a way of making the same query produce a better plan by doing something else perhaps (magic?) He said his query had a WHERE clause that looked like the following WHERE RIGHT(SomeColumn,3) = '333' I then asked if he could change the table, his answer was that he couldn't mess around with the…
This came up the other day, someone wanted to know the percentage of NULL values in a column Then I said "I bet you I can run that query without using a NULL in the WHERE clause, as a matter of fact, I can run that query without a WHERE clause at all!!" The person then wanted to know more, so you know what that means.. it becomes a blog post :-) BTW, the PostgreSQL version of this blog post can be…
Are you still running profiler or have you transferred to using Extended Events? I use Extended Events almost exclusively now because it's so much easier compared to using profiler or trace from T-SQL. Not to mentioned you can capture more things The other day someone checked in some code and every now and then the build would fail with the error Msg 15151, Level 16, State 19, Line 51 Cannot drop…
Today we are going to look at how to improve your tech skills. This really is a continuation of the Stay relevant and marketable post from a couple of weeks ago. Here are some things that you can do to improve your tech skills Attend usergroups Attend your local usergroup meetings, there is always some expert that comes to do presentations. Answer questions I still think answering questions is one…
This is just a quick and fun post. I was troubleshooting a deadlock the other day and it got me thinking.... I know the number 1205 by heart and know it is associated to a deadlock. What other numbers are there that you can associate to an event or object or limitation. For example 32767 will be known by a lot of people as the database id of the ResourceDb, master is 1, msdb is 4 etc etc. So below…
It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info. Whenever I interview people for a DB position, I always ask what the latest version is that they have used. I will also ask if they have used the version that is in beta now. More often than not I will…
It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info. Today we are going to look at servers where everything is installed and enabled. Before we start this post let's look back in time a little. Before SQL Server 2005 came out when you installed SQL Server…
The other day I was reading something on Hackernews and someone posted a link to a Sexy Primes wikipedia article. I looked at that and then decided to do this in SQL Server because.. why not? From that wikipedia link: https://en.wikipedia.org/wiki/Sexy_prime In mathematics, sexy primes are prime numbers that differ from each other by six. For example, the numbers 5 and 11 are both sexy primes,…
A coworker came to me asking me for some help. He had some issues trying to convert some data from a staging table to numeric. I asked him to show me the data in SSMS and at first glance it looked good to me. Then I asked where the data came from, he said it came from Excel. Aha... I have plenty of war stories with Excel so I said, it's probably some non printable character that is in the column..…
In the previous post Scripting out procs and user defined functions with PowerShell , we scripted out some procs and functions from the Adventureworks database so that we can run some static code analysis. Today we will install SonarQube, SonarQube Scanner and the sonar-tsql-plugin. First thing we need is to grab Java if you don't have it installed. I know I know... I uninstalled it as well.. sigh…
I decided to take SonarCube for a spin to run some static code analysis. In order to do that I needed some code that I can display here without breaking laws. I a real world, you would just point SonarCube to your code repository, but in order to show you how to run it so that you can follow along, I decided to go this route I decided to use the AdventureWorks database to script out some procs and…
Originally I was not going to write this post, but after I found out that several other people didn't know this I figured what the heck, why not, maybe this will help someone else as well Last week I was on a remote session with 2 clients, each run the Advent program . The team I am part of provides a script to run the advent (APX or Axys) executable. This will then generate the portfolios,…
Someone had an issue where a batched delete script was not deleting anything. I looked over some code in our repository and noticed two patterns the way queries are written to handle batch deletes One is a while loop that runs while @@rowcount is greater than 0 WHILE @@rowcount > 0 BEGIN DELETE TOP ( 5000 ) FROM SomeTable END The other way is to run a while loop which is always true and then check…
It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info. Today we are going to look at proactive notifications In the SQL Server Maintenance post from yesterday I touched upon proactive notifications a little, today I want to dive a little deeper into this…
It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info. Today we are going to look at cursors Why do we hate those poor cursors? Let's first see why people tend to use cursors. Let's say you come from a procedural language and this is the first time you are…
It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info. In this post we are going to look at SQL Server maintenance Just like with a car or a house, you need to do maintenance on databases as well. SQL Server has gotten better over the years, there are less…
Image by Jahobr - Own work , CC0 , Link Since today is Fibonacci day I decided to to a short post about how to do generate a Fibonacci sequence in T-SQL. But first let's take a look at what a Fibonacci sequence actually is. In mathematics, the Fibonacci numbers are the numbers in the following integer sequence, called the Fibonacci sequence, and characterized by the fact that every number after…