Identifying nullable index columns and nullable foreign keys
While there are no limitations to prevent the use of a nullable column within an index or as a foreign key, there are several situations where nulls may cause problems. One example where a nullable column may cause a problem is in the creation of a unique index or unique constraint. Simply stated, you cannot create a unique index or a unique constraint if there is more than one row with a value of “null” in the given column. The information below is from the SQL Server 2008 R2documentation and can be viewed here: http://msdn.microsoft.com/en-us/library/ms175132.aspx
”Handling NULL Values”
“For indexing purposes, NULL values compare as equal. Therefore, you cannot create a unique index, or UNIQUE constraint, if the key values are NULL in more than one row. Select columns that are defined as NOT NULL when you choose columns for a unique index or unique constraint.”
Additionally, the results show columns used as foreign keys that have been defined as nullable. While it is possible to create foreign keys on nullable columns, it creates a condition that violates the foreign key itself because the null value cannot be equated to any given value during the foreign key lookup. In effect, a null value bypasses the foreign key constraint resulting in data corruption type scenarios.
The following T-SQL script lists all columns that have been defined to allow nulls. The script can also be used to identify columns used as indexed columns and / or foreign keys that have been defined to allow nulls.
select b.name as 'Table Name'
,a.name as 'Column Name'
,isnull(d.name,'N/A') as 'Index Name'
,isnull(f.name,'N/A') as 'Foreign Key Constraint Name'
from sys.columns a
join sys.objects b
on b.object_id = a.object_id
left join sys.index_columns c
on c.column_id = a.column_id
and c.object_id = a.object_id
left join sys.indexes d
on d.index_id = c.index_id
and d.object_id = c.object_id
left join sys.foreign_key_columns e
on e.parent_object_id = a.object_id
and e.parent_column_id = a.column_id
left join sys.foreign_keys f
on f.object_id = e.constraint_object_id
where a.is_nullable = 1
and b.type = 'U'
and objectproperty(b.object_id,'IsMSShipped') = 0
Sample results shown below:
Hopefully you find this script useful.
Please let me know if there are any questions. All feedback is appreciated.
Thanks,
Greg Moss
Ever looked at data returned from a catalog view in order to achieve clarity about a specific question, only to end up being further confused by the catalog data returned?
A good example of this is when looking at the size of your database files in the sys.database_files catalog view. The data returned for the “size” column is shown in 8k pages instead of actual size in KB. This is confusing enough, but with larger databases this can be even more confounding.
One solution to resolving this confusion is with T-SQL “CASE” expressions.
In using T-SQL code to access data within a SQL Server database, we often find opportunities based upon conditional situations where we need the flexibility of replacing column values, calculations, or even conditionally altering the order results are sorted. Most often, these situations can be addressed with the T-SQL “CASE” expression.
There are two types of case statements including the first example, which is the “simple” type. The “simple” case statement evaluates a value (or expression) and compares it in an equality check against a set of values (or expressions).
In its simplest form the T-SQL “CASE” expression can enrich database results while providing descriptive results for non-obvious data values.
In this example, the information is clearer than just a simple “code” being returned:
select name as ‘Object Name’
,(case type
when ‘U’ then ‘User Defined Table’
when ‘V’ then ‘User Defined View’
end) as ‘Object Type’
from sys.objects
where type in (‘U’,'V’)
order by name
This example produces the following results:
Object Name Object Type
Collection User Defined Table
CollectionGroup User Defined Table
HourlyLoad User Defined View
HourlyData User Defined View
Hours User Defined Table
DataValues User Defined View
Property User Defined Table
In the example above the object is “labeled” for clarity based upon its object type.
The second type of case expression is the “searched” case expression. In this type of expression, one or more Boolean expressions are evaluated until a match occurs. For example:
select (convert(varchar(50),getdate(), 108) + ‘ ‘ +
convert(varchar(50),datename(weekday,getdate())) +
convert(varchar(50),(case
when datepart(hh,getdate()) between 0 and 5
then ‘ Early Morning’
when datepart(hh,getdate()) between 6 and 11
then ‘ Morning’
when datepart(hh,getdate()) between 12 and 17
then ‘ Afternoon’
when datepart(hh,getdate()) between 18 and 23
then ‘ Evening’
else ‘Unknown Time’
end))) as ‘When did problem occur?’
This example produces the following results:
When did problem occur?
10:06:52 Monday Morning
In the example above a text string is created with standardized information in order to facilitate clarity in communications between groups.
In some situations, the use of a case expression can be used to address complex conditional computations. For example, in order to provide the size of a database file in the appropriate measurement (in this case we provide size information in Gigabytes, Megabytes, Kilobytes, and Bytes depending on the actual size of the file) we create a nested case expression as follows:
–***** Define Variables *****
declare @GB float
declare @MB float
declare @KB float
declare @B float
–***** Assign Variable Values *****
set @GB = (cast(8192 as float) / cast(1073741824 as float))
set @MB = (cast(8192 as float) / cast(1048576 as float))
set @KB = (cast(8192 as float) / cast(1024 as float))
set @B = (cast(8192 as float))
–***** Get Database File Information ******
select name as ‘File Name’
,file_id as ‘File ID’
,(case
when (cast(size as float) * @GB) > (cast(1 as float))
then (convert(varchar(16),(cast(size as float) * @GB)) + ‘ GB’)
else (case
when (cast(size as float) * @MB) > (cast(1 as float))
then (convert(varchar(16),(cast(size as float) * @MB)) + ‘ MB’)
else (case
when (cast(size as float) * @KB) > (cast(1 as float))
then (convert(varchar(16),(cast(size as float) * @KB)) + ‘ KB’)
else ((convert(varchar(16),(cast(size as float) * @B))) + ‘ Bytes’)
end)
end)
end) as ‘File Size’
from sys.database_files
order by type, file_id
This example provides the following results and was created to demonstrate complex CASE expression nesting:
File Name File ID File Size
MyDatabase_Data 1 11.7197 GB
MyDatabase_Log 2 1.95313 GB
NOTE: This example can be reduced to a simple case statement but was purposefully coded to show nesting of case expressions. This example accurately calculates useable file space and does not necessarily match the SQL Server Management Studio file size displayed!
The example above calculates size in differing measurements based upon measurement boundries.
In the final example, we use a T-SQL “CASE” expression to conditionally order a result set. In this particular scenario, we order all results other than system table data by object type and object name. All results that are system table data are sorted by type and object_id:
select name as ‘Object Name’
,type as ‘Object Type’
,object_id as ‘Object ID’
from sys.objects a
order by type
,(case type
when ‘S’ then ‘Object ID’
else a.name
end) asc
This example provides the following results:
Object Name Object Type Object ID
CK_master_server_MustBe_1 C 309576141
sysmail_OutMailSensitivityMustBeValid C 155147598
default_current_date D 2121058592
DF__sysutilit__volum__049AA3C2 D 77243330
FK__backupfil__backu__14E61A24 F 350624292
FK_sysutility_mi_smo_properties F 1293247662
agent_datetime FN 1973582069
ConvertToInt FN 891150220
syspolicy_fn_get_type_name FN 1504724413
sp_add_alert P 1906105831
sysmail_verify_profile_sp P 939150391
PK__backupfi__57D1800A17C286CF PK 414624520
SYSMAIL_SERVERTYPE_TypeMustBeUnique PK 1822629536
sysrscols S 3
sysallocunits S 7
sysfiles1 S 8
syspriorities S 17
sysowners S 27
sysbinobjs S 58
sysaudacts S 59
sysobjvalues S 60
sysclsobjs S 64
sysrowsetrefs S 65
sysremsvcbinds S 67
sysxmitqueue S 68
sysrts S 69
sysconvgroup S 71
sysdesend S 72
sysdercv S 73
syssingleobjrefs S 74
sysmultiobjrefs S 75
sysguidrefs S 78
syscompfragments S 82
syn_sysutility_ucp_volumes SN 189243729
fn_sysutility_ucp_get_policy_violations TF 2077250455
backupfile U 366624349
backupfilegroup U 286624064
backupmediafamily U 110623437
backupmediaset U 46623209
backupset U 206623779
sysproxylogin U 69575286
sysutility_ucp_volumes_stub U 2000726180
NOTE: Results truncated for clarity!
In the example above a “CASE” expression is used to conditionally order data within the result set that would not be possible otherwise.
In conclusion, we can see that the “CASE” expression can be used in efforts to clarify results, conditionally apply calculations and conditionally order result sets. The ability to use a “CASE” expression effectively can provide additional functionality and flexibility in T-SQL coding scenarios unobtainable otherwise.
Note: All examples created using SQL Server 2008 R2.
Note: I am also including the original word document that perserves formating for reading ease!
As is always the case with a limited amount of space, I often find myself trying to manage space on the physical storage I have available. This process is like trying to hit a moving target as I will frequently create and drop databases for testing purposes or load / attach databases sent to me by customers.
Usually if a database is not in use, I archive it or remove it completely as I only need space for the active databases I am working with. In order to know how much space I am currently consuming I run the following script to enumerate all databases currently in use and the files associated with each of these databases.
–***** Define Variables *****
declare @GB float
declare @MB float
declare @KB float
declare @B float
–***** Assign Variable Values *****
set @GB = (cast(8192 as float) / cast(1073741824 as float))
set @MB = (cast(8192 as float) / cast(1048576 as float))
set @KB = (cast(8192 as float) / cast(1024 as float))
set @B = (cast(8192 as float))
select db_name(database_id) as ‘Database Name’,
database_id as ‘Database ID’,
(case type
when 0 then ‘Data File’
when 1 then ‘Log File’
when 2 then ‘Filestream File’
when 3 then ‘Other – N/A’
when 4 then ‘Full Text Catalog File’
else ”
end) as ‘FIle Type’,
name as ‘Logical File Name’,
physical_name as ‘Physical File Name and Location’,
((convert(float,size) * CONVERT(float,8)) / CONVERT(float,1024)) as ‘Declared Size in MB’,
(case when (cast(size as float) * @GB) > (cast(1 as float))
then (convert(varchar(16),(cast(size as float) * @GB)) + ‘ GB’)
else (case when (cast(size as float) * @MB) > (cast(1 as float))
then (convert(varchar(16),(cast(size as float) * @MB)) + ‘ MB’)
else (case when (cast(size as float) * @KB) > (cast(1 as float))
then (convert(varchar(16),(cast(size as float) * @KB)) + ‘ KB’)
else ((convert(varchar(16),(cast(size as float) * @B))) + ‘ Bytes’)
end)
end)
end) as ‘Actual Size’,
(case when is_percent_growth = 0
then (case when (cast(growth as float) * @GB) > (cast(1 as float))
then (convert(varchar(16),(cast(growth as float) * @GB)) + ‘ GB’)
else (case when (cast(growth as float) * @MB) > (cast(1 as float))
then (convert(varchar(16),(cast(growth as float) * @MB)) + ‘ MB’)
else (case when (cast(growth as float) * @KB) > (cast(1 as float))
then (convert(varchar(16),(cast(growth as float) * @KB)) + ‘ KB’)
else ((convert(varchar(16),(cast(growth as float) * @B))) + ‘ Bytes’)
end)
end)
end)
when is_percent_growth = 1
then (case growth
when 0 then ‘N/A’
else (convert(varchar(16),(cast(growth as float))) + ‘ %’)
end)
else ”
end) as ‘Growth Method’
from master.sys.master_files
order by database_id, type, size desc
Hopefully you find this script useful.
Please let me know if there are any questions. All feedback is appreciated.
Thanks,
Greg Moss
