Tech Blog | SQL From The Ground | Part 2

One of the most important things I read this week was about the data types in SQL. It is much like mysql and Oracle SQL but I studied a bit further into it than I had previously. Data types are broken into categories: exact numeric, such as Int and Numeric, character string, or char and varchar, Unicode characters, which are Nchar and NVchar, approximate numbers float, and real binary strings like binary and varbinary. As well date and time has things like data, time, datetime2, smalldatetime etc.

What I found to be interesting about the different data types inside each category is the size difference. For instance smalldatetime is 4 bits, while datetime2 is 8 bits. It seems insignificant at first glance, but when faced with a multi-thousand row table, in a database with multiple tables, the impact starts to be astounding. The trade-off generally speaking is accuracy of the number. Changing directions to numbers, using a float is smaller generally than a numeric. The numeric though has the capacity to hold larger, or smaller numbers. When designing software, or large heavily used databases, you can halve the impact of a lot of the data by using the right datatype.

Moving on to some information from my DBA readings. I read a lot about consistency checks. Consistency checks should be done often to check for errors, faults, and corruption in the database. There are multiple checks that can be run and in different combinations. Below is an example of these multiple checks:

 

  • DBCC CheckDB (highest level, runs CheckAlloc, and CheckTable)
  • DBCC Check Alloc
  • DBCC Checkcatalog
  • DBCC Checktable
  • DBCC Checkfilegroup (Runs alloc and table for a specific file group)
  • DBCC Checkident
  • DBCC Checkconstaints

 

After running consistency checks, and getting some results you have a few options. Generally speaking if there is <10% fragmentation you don’t really want to mess with it. If there is between 10% and 30% you want to alter and reorganize. If there is more than 30% you want to rebuild indexes. All of the information on consistency checks, and the proceeding actions taken are gathered in a maintenance plan, this should be pre-designed before even turning the database on. Fortunately, there are very well accepted pre-made scripts out there. I’m going to look further into them and test some of these scripts as time goes on.

For security of the database at installation time, there should be a different user for each service that SQL uses. You have to open TCP 1433 and 1434 in the firewall, but the firewall SHOULD NOT BE TAKEN DOWN. In my short career, I’ve seen a lot of sysadmins disable the windows firewall to allow applications through, but never take the time to make exclusions for things to work. Physical and network access should be restricted as to avoid masquerading users, and other infiltration.

The last thing I want to mention about security is that permissions to the databases and tables should be as tight as possible. Unfortunately most applications need access to the dbo. As a software designer, and a dba, it would be great to be able to connect to the database in the most secure way possible. Leaving as little open to would be evil-doers as possible.