Primary Key Max Length

I’m planning on fishing my first Muskie tournament this year. The Rhinelander Hodag Musky Challenge is held each year up in September on various lakes around Rhinelander. Based on results from the last several years catching two legal (40+ inch) muskies will put you into the top 3, and cash prizes are awarded to the top 10 finishers. This is a really a great opportunity for muskie fishermen because as long as your doing what you can correctly, anyone can get lucky twice in two days. On the other hand, it shows how challenging musky fishing can be – about 3 of the 80 or so teams will actually manage to land two of the fish.

Winners of the 2014 Rhinelander Toyota Hodag Musky Challenge (from the left) third place, Matt Rollins and Brad Rollins; first place, Scott Denzer and Kevin Douglas; Rhinelander Area Chamber of Commerce Executive Director Dana DeMet; second place, Jason Mollen and Todd Havel.

Winners of the 2014 Rhinelander Toyota Hodag Musky Challenge (from the left) third place, Matt Rollins and Brad Rollins; first place, Scott Denzer and Kevin Douglas; Rhinelander Area Chamber of Commerce Executive Director Dana DeMet; second place, Jason Mollen and Todd Havel.

One interesting thing about fishing tournaments is that many require you to release the fish after having it recorded by a judge. In fact, if the fish accidentally dies before you can successfully let it go you are often very harshly penalized. To help facilitate this requirement, a livewell with a minimum size is required. The Rhinelander Hodag Musky Challenge posts this in their list of rules:

ALL BOATS must have a live well or aerated
container measuring at least 40”. Wisconsin DNR
recommendation is 45”.

The reason for this is pretty simple – how can you expect to release a 40+ inch fish crammed into a 30 inch livewell? It’s probably not going to work.

Size is important, especially in SQL Server. One place this is particularly true is the size of the primary key. This page lists Microsoft’s size limit on various objects, and we can see the size limit on the primary key is 900 bytes. A primary key is one or more columns that must be unique within a table. Many times this will be a single numeric column such as an identity integer field. However, sometimes it makes sense to use other types of fields or a combination of several fields. The syntax for creating a table with a primary key is:

CREATE TABLE [dbo].[TournamentCatches]
    (
      [TC_ID] [INT] IDENTITY(1, 1) NOT NULL ,
      [Team_ID] [INT] NOT NULL ,
      [Lake_ID] [INT] NOT NULL ,
      [FishSpecies_ID] [INT] NOT NULL ,
      [FishLength] [NUMERIC](5, 2) NOT NULL ,
      [FishScore] [INT] NOT NULL ,
      [FishSurvived] [BIT] NOT NULL ,
      CONSTRAINT [PK_TournamentCatches] PRIMARY KEY ( [TC_ID] ASC )
    )
ON  [PRIMARY]

So what happens if we try to create a primary key on fields totaling more than 900 bytes? Let’s add a description of the catch to the table and try to include it in the primary key (this would usually not belong in a primary key, but for the sake of the example):

CREATE TABLE [dbo].[TournamentCatches]
    (
      [TC_ID] [INT] IDENTITY(1, 1) NOT NULL ,
      [Team_ID] [INT] NOT NULL ,
      [Lake_ID] [INT] NOT NULL ,
      [FishSpecies_ID] [INT] NOT NULL ,
      [FishLength] [NUMERIC](5, 2) NOT NULL ,
      [FishScore] [INT] NOT NULL ,
      [FishSurvived] [BIT] NOT NULL ,
      CatchDescription CHAR(900)
        CONSTRAINT [PK_TournamentCatches]
        PRIMARY KEY ( [TC_ID] ASC, CatchDescription ASC )
    )
ON  [PRIMARY]

20141230 PK Too Long

You can see SQL Server will not let us create this primary key because it’s too big. What if the CatchDescription was VARCHAR(1000) instead of CHAR(900)?

DROP TABLE dbo.TournamentCatches;

CREATE TABLE [dbo].[TournamentCatches]
    (
      [TC_ID] [INT] IDENTITY(1, 1) NOT NULL ,
      [Team_ID] [INT] NOT NULL ,
      [Lake_ID] [INT] NOT NULL ,
      [FishSpecies_ID] [INT] NOT NULL ,
      [FishLength] [NUMERIC](5, 2) NOT NULL ,
      [FishScore] [INT] NOT NULL ,
      [FishSurvived] [BIT] NOT NULL ,
      CatchDescription VARCHAR(1000)
        CONSTRAINT [PK_TournamentCatches]
        PRIMARY KEY ( [TC_ID] ASC, CatchDescription ASC )
    )
ON  [PRIMARY]

20141230 PK Variable Length

This time the primary key was successfully created. However, this has created a very dangerous situation. If someone tries to insert a value larger than 899 bytes into the CatchDescription or update an existing value in that field to a value larger than 899 bytes, SQL is going to stop them.

INSERT  INTO dbo.TournamentCatches
        ( Team_ID ,
          Lake_ID ,
          FishSpecies_ID ,
          FishLength ,
          FishScore ,
          FishSurvived ,
          CatchDescription
        )
VALUES  ( 12 ,
          3 ,
          1 ,
          43.5 ,
          56 ,
          1 ,
          REPLICATE('Fishing Rocks! ', 61) -- This will replicate these 15 characters 61 times for a total length of 915 bytes
        )

20141230 PK Variable Length Failed

This is a great example of the notion that just because SQL Server lets you do something doesn’t mean that you should.

Advertisements

Finding SSIS package passwords in SQL Agent jobs

It can be a daunting experience to try fishing a new lake. We can see what the lake looks like from above the water, but below there are innumerable structures and features to consider. Last summer I fished lake Winnebago for the first time with my pop and brother. Lake Winnebago is the 21st largest natural lake in the United States, covering about 215 square miles. As we motored out from the boat launch, the only thought was “where to start?” The vast lake was stretched out before us, but since we could only see the top of the water we had to use other clues to figure out where to start fishing.
It’s tough heading into the unknown. Earlier this year I started a new job that used SQL Server Integration Services (SSIS) jobs saved in the file system as steps in various SQL Agent jobs. This job was at a small shop where documentation was not well kept. The SSIS packages were password protected, but nobody knew the password! Fortunately I was able to find the passwords stored in the SQL Agent job steps using this SQL command:

SELECT  sj.name ,
        sjs.command
FROM    msdb.dbo.sysjobs sj
        JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE   sjs.subsystem = N'SSIS'

After running, I was able to find the /DECRYPT password part of the command, which I used to decrypt and view/edit the packages using BIDS.  In the image below, you can see the password is “ssis”.

20141229 SSIS Package Passwords

Winnebago turned out to be a fun lake to fish.  We were catching fish all day, and ended up with a good amount to take home, after throwing back all the nasty sheepshead.

Sheepshead

How to capture database changes with a DDL Trigger

Casting and retrieving big, heavy muskie lures for a full day can actually be quite a bit of work. Lures weighing up to a pound can be tough to cast, and lures with heavy action take a lot of effort to pull through the water. Using such large, heavy gear also requires standing instead of sitting, and (not to sound like a wimp but…) a full day of standing adds to the fatigue.
Not all fishing is such hard work. There is something both satisfying and relaxing about sitting back with your feet up, enjoying a cold beer, and holding a rod waiting to feel the sharp tap of a fish bite. Walleye fishing often provides this type of relaxing leisure time. Walleye are a popular fish to catch due to their taste, though they are also quite challenging. They have a reputation for being a finicky fish and will refuse many different presentations before choosing one to bite. Their bite is also quite subtle, and if you aren’t paying attention you can often miss it. By the time you realize you have a fish on your line, the walleye will have felt the hook and spit out the bait. Another complication is that small panfish will often find and steal your bait before the walleye get a chance. Many a time I have tried to set the hook on what I thought was a good sized walleye, only to pull up a small bluegill, or even worse, a hook with only a small scrap of worm because the bluegills have pulled it apart and eaten the rest.
How nice it would be to have some way to watch and monitor the bait, so it wouldn’t be stolen by smaller fish and we wouldn’t miss the walleye when they do show up. Many database administrators have the same feelings about their databases. We want to be able to see exactly what is changing, when, and who is making those changes. While we try to lock down our environments so most of, if not all, the changes have to go through us, there are often business requirements that force us to give the ability to make minor changes others. This is especially true in lower environments, where developers can often be tasked with creating tables or stored procedures that will be migrated up to production.
Fortunately, SQL Server gives us the ability to capture all DDL changes through the use of a DDL Trigger. DDL stands for Data Definition Language, and it refers to statements that work against objects rather than the data within them. Some examples are DROP TABLE, CREATE STORED PROCEDURE, and ALTER VIEW. You can capture these statements, including who ran them, from where, and at what time through the use of the DDL Trigger. Here’s Microsoft’s explanation on DDL triggers.  The first thing to do is to create a table to hold all the captured events:

CREATE TABLE Sandbox.dbo.DDLEvents
    (
      ID INT IDENTITY(1, 1) ,
      EventDate DATETIME2 ,
      EventType NVARCHAR(100) ,
      EventDDL NVARCHAR(MAX) ,
      EventXML XML ,
      DatabaseName NVARCHAR(MAX) ,
      SchemaName NVARCHAR(255) ,
      ObjectName NVARCHAR(255) ,
      HostName NVARCHAR(255) ,
      IPAddress VARCHAR(20) ,
      ProgramName NVARCHAR(1000) ,
      LoginName NVARCHAR(255)
    )
 GO

Next we need to create the actual trigger.

USE [master]
 GO
CREATE TRIGGER [DDLTrigger_CaptureEvents] ON ALL SERVER
    WITH EXECUTE AS 'sa'
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_INDEX,
        ALTER_INDEX, DROP_INDEX, CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
        CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_ROLE,
        ALTER_ROLE, DROP_ROLE, CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN,
        CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER, CREATE_USER, ALTER_USER,
        DROP_USER, CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @EventData XML = EVENTDATA();
 
        DECLARE @ip VARCHAR(32) = ( SELECT  client_net_address
                                    FROM    sys.dm_exec_connections
                                    WHERE   session_id = @@SPID
                                  );
        INSERT  Sandbox.dbo.DDLEvents
                ( EventDate ,
                  EventType ,
                  EventDDL ,
                  EventXML ,
                  DatabaseName ,
                  SchemaName ,
                  ObjectName ,
                  HostName ,
                  IPAddress ,
                  ProgramName ,
                  LoginName
                )
                SELECT  GETDATE() ,
                        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)') ,
                        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)') ,
                        @EventData ,
                        @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(MAX)') ,
                        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)') ,
                        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(255)') ,
                        HOST_NAME() ,
                        @ip ,
                        PROGRAM_NAME() ,
                        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(255)');
    END

A couple important notes to make on this create statement:

  • This trigger must be created in the master database
  • I added the “WITH EXECUTE AS ‘sa'”. Without this clause, each user who executes a statement that fires the trigger will need permission on everything the trigger uses. Since this is going into a holding table, giving everyone permission for this table would be a huge hassle. It is VERY IMPORTANT to understand if the user/process has insufficient permissions, the entire transaction will be rolled back. This can also be difficult to find as rollbacks due to insufficient permissions within triggers often do not pass out helpful error messages.
  • On the “FOR CREATE_PROCEDURE, ALTER_PROCEDURE,…” you can use this to specify which events should fire the trigger. Maybe you’re only interested in finding out who is dropping tables so you’d only need to add that to the trigger.
  • The IP address of the user/process that is executing the statement had to be retrieved from a separate DMV.

One last step to make after creating the trigger is to enable it:

ENABLE TRIGGER [DDLTrigger_CaptureEvents] ON ALL SERVER

Now after running a few DDL statements I see them logged in my table:

20141229 DDLEvents Captured
So there is one way to tell who’s doing what DDL in your database. Next time you need to confront one of your “bluegill” teammates, you’ll have proof that he has been messing around with your bait/database.

Connection Problems from Remote Computer

A basic but crucial part of a successful catch involves putting your bait near active fish. It sounds really simple, but often we as fisherman spend way too much time zipping around the lake trying to find fish that are in a mood to feed. When muskie fishing, both great excitement and disappointed frustration are felt when a large fish lazily follows your bait to the boat but turns around and swims away instead of trying to eat. If you want to make that connection with a large fish, it needs to be ready.
The same is true of SQL Server. SQL Server is able to use different network protocols to “listen” for incoming connection requests. The three main protocol types are Shared Memory, TCP/IP, and Named Pipes. The most widely used type is TCP/IP because it provides support for computers with diverse hardware architectures and various operating systems. Here are definitions of each type, parafrased from Microsoft Technet:
– Shared Memory: The simplest to use, shared memory has no configurable settings. This can only be used when connecting to a SQL Server instance on the local computer. This will often be used for troubleshooting when there are problems with other protocols.
– TCP/IP: This is the most common protocol used over the Internet. TCP/IP includes standards for routing network traffic and offers advanced security features. Configuration can be complicated, but most networked computers are already correctly configured.
– Named Pipes: This protocol was developed for local area networks (LAN). A part of memory is used to pass information from one process to another. This is comparable performantly to TCP/IP in a fast LAN, but can function poorly in environments with a slow network. Generally, TCP/IP is preferred in a slow LAN, WAN (wide area network), or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.
I recently ran into an issue where I was unable to connect to a remote instance of SQL Server that I had setup on a sandbox machine within our local network.
20141223 No ConnectionI could connect from SQL Server Management Studio (SSMS) from the local sandbox machine, so I knew I needed to check a few settings. My first step was to check that the instance was correctly configured to allow remote connections. I went into the properties of the instance and found that it was set up to allow remote connections:
20141223 ConnectionsNext I checked the SQL Server Configuration Manager to see what network protocols were enabled. I found that that while Shared Memory was enabled, both Named Pipes and TCP/IP were set to disabled.

20141223 SSCM

I know that in our network we use TCP/IP for our connections to SQL Server, so I enabled this option, but I was still unable to successfully connect from the remote computer.  My next step was to check the SQL Server Browser.  This can be found in the SQL Server Configuration manager under SQL Server Services.

20141223 SQL Browser

This was also stopped, so I started it, and was then able to connect via SSMS from the remote computer.

Other items to consider checking include Windows Firewall and making sure the SQL Server service is running.
So next time you can’t connect to a remote instance of SQL Server, check these two settings. And next time a fish looks at your bait indifferently before turning around and swimming away, practice persistance – it’s got to eat sooner or later!

Evaluating the use of Sparse Columns

A few summers ago I was fishing a very clear lake up in Oneida county, WI for muskie. Oneida county and the county just to the north of it, Vilas county, offer some of the best muskie fishing in the Midwest. It was a hot, calm day with not a cloud in the sky. These types of conditions are usually not very good for muskie – they are ambush predators and prefer low light conditions or stained water so they can close in on their prey without being noticed until the last second. I was fishing a deep weedline with a deep-running crankbait. As I was reeling in I felt the dull tug of weed getting caught in the hooks of the lure. I gave a sharp tug with the rod to try and free the lure, but the weeds would not let go. As my lure limped back to the boat, I was surprised to see a good sized muskie following the lure even though it was surrounded by the weeds it was dragging. One fun thing about muskie is their willingness to attack lures right next to the boat, and for this reason the seasoned muskie angler will swim the bait in a circle a few times at the end of each retrieve (commonly called a figure-eight). Although I was sure the fish wouldn’t hit the fouled lure, I still went into my standard figure-eight. I was surprised and completely unprepared as the muskie shot forward and grabbed the lure, still full of weeds, about a quarter way through my first circle. I had a split second decision on which way to try the hookset, and I made the wrong one. I tried setting the hood back against the direction the fish was moving, but I ended up striking the side of the boat with my rod, and the fish dropped the lure and swam off. I had made a wrong decision, and had to live with losing that fish. Fortunately when deciding whether or not to use Sparse columns in SQL Server, we don’t have a split second decision – we can experiment to see whether they are a good fit for our particular tables.
Previously I had posted on the amount of space used by fixed length fields, especially when storing the NULL value. One way to get around this “limitation” of these data types is to use Sparse columns. Microsoft’s Developer Network defines Sparse columns as ordinary columns that have an optimized storage for NULL values. Sparse columns reduce the space needed for NULL values, but the tradeoff is the space used and cost to retrieve the non-NULL values. Microsoft suggests using sparse columns when the space saved is at least 20 to 40 percent. There are also a couple of important restrictions on Sparse columns:

  • Obviously, the column has to be nullable (no identity or primary key)
  • These data types are not allowed to use sparse columns: text, ntext, image, timestamp, user-defined, geometry, or geography
  • The columns can’t have a default value or be a computed column
  • Sparse columns are incompatible with compression. Sparse columns can’t be added to compressed tables and tables with existing sparse columns can’t be compressed

That last point is pretty important because you can often get significant space savings with compression. The main downside of compression is the extra CPU usage required when working with these tables, but that’s another discussion altogether.
When SQL Server is changing a column from sparse to nonsparse or vice versa, that table is unavailable (locked). This is because SQL Server follows this procedure to make the change:

  1. Adds a new column to the table with the new storage size and format.
  2. Copies the data from the old row to the new
  3. Removes the old column
  4. Rebuilds the table or clustered index to reclaim the space used by the old column.

It’s important to remember there is a row size limit in SQL Server that is 8060 bytes for tables without Sparse columns and 8018 bytes for tables with Sparse columns. If changing a column from nonsparse to Sparse violates these limits, the change will fail.
From a storage perspective, Sparse columns work by not storing any data for columns that have been marked sparse and are NULL. However, in order to store values in Sparse columns that aren’t NULL, SQL server has to not only store the value, but also what column is holding that value. This causes the size required for each non-NULL value to increase by 4 to 8 bytes (for example, non-NULL integers increase from 4 to 8 bytes).

Now that I’ve hopefully provided a little background on what Sparse columns are and how they work, let’s look at an example. I have created a table with 1 million records in an integer field.


CREATE TABLE dbo.SparseTest
    (
      ID INT IDENTITY(1, 1) ,
      IntegerField INT ,
      CONSTRAINT PK_SparseTest_ID PRIMARY KEY CLUSTERED ( ID ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]
DECLARE @int INT = 1
WHILE @int <= 1000000
    BEGIN
        INSERT  INTO dbo.SparseTest
                ( IntegerField )
        VALUES  ( CHECKSUM(NEWID()) )
        SELECT  @int = @int + 1
    END

This table is full of values – none are NULL:

20141223 No Sparse No Null

The initial size of this table is about 16 MB:

20141223 No Sparse Size

Next I update the table definition to make IntegerField a Sparse column:

ALTER TABLE dbo.SparseTest ALTER COLUMN IntegerField INT SPARSE
ALTER TABLE dbo.SparseTest REBUILD

I found I did have to rebuild the table before I was able to see the change in used space.  Now our table with the Sparse column with no NULL values has ballooned to 28 MB!

20141223 Sparse Size

I change 25% of the values in the Sparse column to NULL with this statement:

UPDATE  dbo.SparseTest
SET     IntegerField = NULL
WHERE   ID % 4 = 0

20141223 Sparse Size 25 Percent NULL

The size has shrunk to approximately 4 MB, to 24 MB.  Next I change another 25% of the values to NULL.  Now half of the values in the table are NULL.

UPDATE  dbo.SparseTest
SET     IntegerField = NULL
WHERE   ID % 4 = 1

The size has shrunk another 4 MB; we are now down to about 20 MB for the entire table.

20141223 Sparse Size 50 Percent NULL

I update another 25% to NULL so now 75% of the values in the Sparse column are NULL.

UPDATE  dbo.SparseTest
SET     IntegerField = NULL
WHERE   ID % 4 = 2

20141223 Sparse Size 75 Percent NULL

We now see the table size is down to 16 MB, the same size as when the column was nonSparse.  One last update puts the table down to 12.5 MB.

UPDATE  dbo.SparseTest
SET     IntegerField = NULL

20141223 Sparse Size 100 Percent NULL

So this demonstrates the size differences of the entire table based on the amount of NULLs in a Sparse column.  The real lesson here is to experiment with Sparse columns on your tables if you have many NULL values, but understand that you need to check to make sure making columns Sparse does indeed reduce the amount of space used.  And don’t forget, this isn’t a one time decision.  The percentage of total values in a table that are NULL can change often; don’t be afraid to track the NULL percentage over time to make sure you are keeping your table optimized.

Fixed Length Fields and the NULL Value

Before each muskie fishing trip I go down into my basement and choose which lures I want to bring with.  You see, muksy lures are BIG, and I have many more lures than fit inside my tackle box.  I need to plan which lures will probably be most effective depending on where I will be fishing and under what conditions (weather, season, water clarity, etc.).  I may choose to use every slot in my tackle box, or to just grab a few proven lures and leave the rest of the slots empty.  However, whether I choose to completely fill my tackle box, or just bring a few lures that I really trust, my tackle box is still going to take up the same amount of space in my boat.  Fixed length fields in SQL Server work the same way.  No matter what value (or lack of value) is in them, they take up the same amount of space. Mostly Empty Tackly Box

This topic is a fairly simple one but it resonates with me because it was one of the first situations I ran into as an early DBA where I had to disagree with one of the “more seasoned” DBAs.  I put more seasoned in quotes because the guy (I’ll call him Larry) actually had only been a DBA for about ten months more than me.  In discussing table design, Larry made the point that NULL values don’t take up much space, usually a single bit that indicates the field is NULL.  I tried to point out that this was only true for variable length fields – fixed length fields always take the same amount of space regardless of what value they hold.  Larry disagreed and shot me down abruptly.  I tried to argue my point one more time, then I let it go.  Afterward I set up an experiment to prove to myself that I was correct.  To set this up, I created a table with both fixed length and variable length fields:

CREATE TABLE [dbo].[NullsUsedSpace]
    (
      [ID] [INT] NOT NULL IDENTITY(1, 1) ,
      [IntegerField] [INT] NULL ,
      [VarcharField] [VARCHAR](36) NULL ,
      [CharField] [CHAR](36) NULL
        CONSTRAINT [PK_NullsUsedSpace]
        PRIMARY KEY CLUSTERED ( [ID] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

From Microsoft here and here, we know that Integer and Char are fixed length fields while Varchar is a variable length field.  Next I fill this table with a set of various values for each data type:

SELECT TOP 100000
        IDENTITY ( INT,1,1 ) IntegerField ,
        NEWID() VARCHARField ,
        NEWID() CHARField
INTO    #temp
FROM    MASTER.dbo.syscolumns sc1 ,
        MASTER.dbo.syscolumns sc2

INSERT  INTO dbo.NullsUsedSpace
        ( IntegerField ,
          VARCHARField ,
          CHARField
        )
        SELECT  IntegerField ,
                VARCHARField ,
                CHARField
        FROM    #temp;

DROP TABLE #temp;

Here is a screenshot of the data returned:

SELECT  *
FROM    dbo.NullsUsedSpace;

20141222 Nullable Table

I check out the size of the table and find it at almost 9 MB.

20141222 Initial Space

Next we update the table and set all CharField and IntegerField to NULL.

UPDATE  dbo.NullsUsedSpace
SET     CHARField = NULL ,
        IntegerField = NULL;

Depending on the size of the table, you may need to rebuild the index to see size changes:

ALTER INDEX PK_NullsUsedSpace ON dbo.NullsUsedSPace
REBUILD;

If fixed length fields didn’t need the same amount of space for NULLs as non-NULL values, I’d expect the space used to go down significantly.  Instead, we see it hasn’t changed:

20141222 Nullable Fixed

However, if we set the variable length fields to NULL, we do see the space used go down to just over 5 MB:

UPDATE  dbo.NullsUsedSpace
SET     VARCHARField = NULL;

ALTER INDEX PK_NullsUsedSpace ON dbo.NullsUsedSPace
REBUILD;

20141222 Nullable Variable

For one last double check, let’s add the integer and char values back into the table to verify the size is still the same.

UPDATE  dbo.NullsUsedSpace
SET     IntegerField = RAND() * 100000 ,
        CHARField = 'Fishing Rocks!';

ALTER INDEX PK_NullsUsedSpace ON dbo.NullsUsedSPace
REBUILD;

20141222 Nullable Variable 2

So there you have it!  Again, this was a pretty simple concept.  This demonstration proved that NULLs take just as much space in a fixed length field as any other value does, just like an empty tackle box takes up just as much space in a boat as a full one.

Start of the blog!

One of the best things in life is a good challenge. Being challenged to accomplish a goal brings out the best in all of us. We have to practice, learn, train, think, and grow – in short, we have to become a better version of ourselves. The challenge of winning a championship propels athletes to new heights; the challenge of earning a degree pushes students to gain more knowledge; and the challenge of completing a video game drives players to play for hours on end.
Being a DBA is one of these good challenges. There are so many great things you can accomplish in SQL Server (and other RDBMS). You can pick one person’s data out of millions of records in miliseconds. You can create unique and elegant ways of finding exactly the permutations of data needed. You can make data both secure and available at the same time. Being a DBA is a fun challenge!
One other activity that I find to be a fun challenge is fishing. There are many similarities between fishing and working with databases. You have to know how to use your tools. For fishing that means your rod, reel, lures, net, and any other tools. You need to practice using each piece of equipment. You need to play with the drag to get it just right – too loose and the fish throws the lure, too tight and the fish snaps your line. You need to experiment moving the lure around in the water. How will it swim if you reel it in steadily? How does it move if you give it a short, sharp jerk? What does it do if you let it stop?
The same is true in SQL Server. Will your database be available while you rebuild indexes? How about when you’re updating statistics? Does that stored procedure really need with WITH RECOMPILE option, causing it to recompile every time it’s run? There are often many different ways you can do the same task – which one is going to work best for your situation? The best way to learn SQL Server is also the best way to learn to fish – just go out and do it!
Another important concept is to know your environment. In fishing there is an expression “match the hatch”. This means when you’re going to a lake, pick out a bait that resembles what’s already there. You need to know what’s in the lake before you can know what to fish with. Additionally, there are several other factors that are all important in determining how to fish – cloud cover, barometric pressure, water clarity, wind, moon phase, and probably 200 more environmental variables. You need to constantly study the way these factors interact with each other and influence the fish if you want to consistently put the big ones in the boat.
The same is true of SQL Server in a very significant way. You need to know your databases. Is CPU running high? Is 300 a good number for Page Life Expectancy? Is setting Cost Threshold of Parallelism at 5 appropriate? Why was this query running in less than a second yesterday, and now it’s taking over a minute? Answers to these questions can almost always be found eventually, but if you study your system and the way everything in it interacts, you’ll know what to check and how to calmly find the source of your problems – and the best solution to them.
The purpose of this blog will be to pass on my knowledge and findings in working with relational database management systems (primarily SQL Server with a bit of Oracle). I’m still very new to the world of databases, and I’m sure many of my posts will be considered quite basic. It is my hope however that some of you will be able to benefit from the perspective I offer.
The comparison between fishing and database administration that’s most fun to consider is meeting the challenge. The hours spent preparing and learning to fish makes the excitment of pulling that lunker out of the water that much greater. In the same way, puzzling over a problem with the database before finding and implementing a solution gives such a great feeling of satisfaction that you’ll almost want to get a picture of yourself with the solution before letting it go, and moving on to the next spot.

 IntroPic


A bit about me:

I grew up in a suburb of Milwaukee in Southeastern Wisconsin. I come from a family with eight kids, so I’m used to the chaos that the IT world throws at us! I graduated from Franciscan University in Steubenville, OH in 2008 with a degree in math and economics. Since then I’ve worked in the Milwaukee area for a bank, a software company, and I’m currently working for an online retailer. I’ve been happily married since 2009, and my wife and I are the proud parents of three young children. We belong to Saint Frances Cabrini parish and try our best be good Catholics, growing closer to God as we live. My relationship with God and my family are the two most important parts of my life, but in my little leftover free time I enjoy fishing, microbrews, cheering on the 49ers and Bucks, and I’m trying to get into bow hunting.