I recently did a webinar on some of the features that Windows 8 Store App developers can easily add to their apps to enhance the user experience. You can view the webinar here. I specifically covered Semantic Zoom, Live Tiles, Snap, and Search integration with the Windows 8 charms bar.
Tuesday, December 18, 2012
Friday, November 16, 2012
SQL Server Database Design Gotchas
This is a long over-due post about some SQL Server database design gotchas that I have run across as I’ve worked with SQL Server over the years. A couple of these are already “best practices” and the others are simply practices I’ve learned that improve the database design.
1. Don’t use UniqueIdentifier (GUID) as the clustered key of a table.
This results in massive table fragmentation, as the order of the data in the table will be based on this column. Because GUIDs are by nature random and not sequential, this means that inserted data will always be randomly inserted all over the place. It is best to choose a sequential column or set of columns as the clustered key of a table.
2. Index your foreign keys.
SQL Server does not index foreign keys and thus you must do this manually. This is a good practice because you will be joining tables on the primary/foreign key relationships and therefore having an index on the foreign keys will allow SQL Server to use the index on those joins. If you follow point #3 on your database design, you can use the following SQL to generate your foreign keys (SQL 2008+):
3. Don’t create nullable BIT columns.
This is a logical error anyway. A bit (or boolean) by definition is either true or false. If there is a third option, use another data type like tinyint. And create a default constraint on the bit column to save yourself on inserts.
4. Use a single column for your primary key (avoid composite primary keys).
The benefits to this one are multiple. One column is the identifier of the row, any foreign keys back to the table are also one column, the join in simpler, the index is smaller, etc. With composite keys, you can quickly end up in a situation where the great-grandchild table ends up having 4 columns as its primary key, and who likes to type that much code for joins?
5. Use unique constraints to specify the business keys (corollary to the previous point).
While the primary key is a single column, use a unique constraint to specify the business key instead of defining the primary key as the business key.
6. Be consistent in your naming.
Self-explanatory – it is difficult to maintain a database that is inconsistent. Even when making changes to an existing database, stick with the convention already defined instead of doing your own, even if you disagree with its convention.
7. Avoid making the primary key column of a lookup table an identity column if the table is one where the primary key value will have meaning.
For system lookup tables (typically those you would generate as enums in code), I’d recommend not applying an identity specification on the key column (thus requiring an explicit set of the value on insert) and I’d change the column name to end in “Cd” instead of “Id” to denote that it is an explicit value that can be coded against.
That’s it for now. Enjoy!
1. Don’t use UniqueIdentifier (GUID) as the clustered key of a table.
This results in massive table fragmentation, as the order of the data in the table will be based on this column. Because GUIDs are by nature random and not sequential, this means that inserted data will always be randomly inserted all over the place. It is best to choose a sequential column or set of columns as the clustered key of a table.
2. Index your foreign keys.
SQL Server does not index foreign keys and thus you must do this manually. This is a good practice because you will be joining tables on the primary/foreign key relationships and therefore having an index on the foreign keys will allow SQL Server to use the index on those joins. If you follow point #3 on your database design, you can use the following SQL to generate your foreign keys (SQL 2008+):
SELECT 'CREATE NONCLUSTERED INDEX IDXFK_'+SCHEMA_NAME([d].schema_id)+'_'+[d].[name] +'_'+ OBJECT_NAME(a.referenced_object_id) + '_' + [c].[name] + ' ON '+ SCHEMA_NAME([d].schema_id)+'.'+[d].[name] + '('+[c].[name]+');'
FROM sys.foreign_keys a
INNER JOIN sys.foreign_key_columns b ON a.parent_object_id = b.parent_object_id AND a.referenced_object_id = b.referenced_object_id AND a.object_id = b.constraint_object_id
INNER JOIN sys.columns c ON a.parent_object_id = c.object_id AND b.parent_column_id = c.column_id
INNER JOIN sys.tables d ON c.object_id = d.object_id
ORDER BY [d].[name], [c].[name]
3. Don’t create nullable BIT columns.
This is a logical error anyway. A bit (or boolean) by definition is either true or false. If there is a third option, use another data type like tinyint. And create a default constraint on the bit column to save yourself on inserts.
4. Use a single column for your primary key (avoid composite primary keys).
The benefits to this one are multiple. One column is the identifier of the row, any foreign keys back to the table are also one column, the join in simpler, the index is smaller, etc. With composite keys, you can quickly end up in a situation where the great-grandchild table ends up having 4 columns as its primary key, and who likes to type that much code for joins?
5. Use unique constraints to specify the business keys (corollary to the previous point).
While the primary key is a single column, use a unique constraint to specify the business key instead of defining the primary key as the business key.
6. Be consistent in your naming.
Self-explanatory – it is difficult to maintain a database that is inconsistent. Even when making changes to an existing database, stick with the convention already defined instead of doing your own, even if you disagree with its convention.
7. Avoid making the primary key column of a lookup table an identity column if the table is one where the primary key value will have meaning.
For system lookup tables (typically those you would generate as enums in code), I’d recommend not applying an identity specification on the key column (thus requiring an explicit set of the value on insert) and I’d change the column name to end in “Cd” instead of “Id” to denote that it is an explicit value that can be coded against.
That’s it for now. Enjoy!
Tuesday, August 21, 2012
Installing Windows 8 Enterprise and Activation Error
After installing Windows 8 Enterprise edition, I received the following error:
Windows can’t activate right now. Please try again later.
Trying later results in the same message. The problem turns out to be that the system need a product key. To do this, simply run an elevated command prompt and type:
slmgr.vbs –ipk “ENTER PRODUCT KEY”
Once this is done, you will be able to activate Windows (and on mine, it was already activated when I went to the activation center).
Windows can’t activate right now. Please try again later.
Trying later results in the same message. The problem turns out to be that the system need a product key. To do this, simply run an elevated command prompt and type:
slmgr.vbs –ipk “ENTER PRODUCT KEY”
Once this is done, you will be able to activate Windows (and on mine, it was already activated when I went to the activation center).
Tuesday, August 7, 2012
Delimited List of Columns
I frequently need to get a list of columns of a database table in a delimited format, and have found myself rediscovering the following query:
For a table with three columns, the return from the above would be:
[Column1],[Column2],[Column3],
Enjoy!
select '['+ [name] + '],'
from sys.columns
where object_name(object_id) = 'table-name'
order by column_id
for xml path('')
For a table with three columns, the return from the above would be:
[Column1],[Column2],[Column3],
Enjoy!
Thursday, July 5, 2012
Creating Scripts to Automate a Local Rebuild
As a project grows larger, it tends to take a longer time to pull down the latest from source code, recompile all the code, and if required, redeploy the database to your local machine so you have the latest of everything. A few weeks ago a coworker remarked that he would like to run a script that would clean, get latest, rebuild everything, and reset his database to the latest from source control, while he goes and gets coffee (our project/local ‘reset’ is a multi-step process that takes about 15 minutes to run). It was a great idea, and so I went off and created something that is now in use by the team, and something you may find useful in yours. Let’s walk through it.
Using an MSBuild build file, I created targets for each action: Clean, GetLatest, Compile, BuildDatabase, and DeployDatabase. At the top of the file there are properties (PropertyGroups and ItemGroups) defined that provide some “configuration” information for what will be run. Notice that the DeployDatabase target explicitly depends on the BuildDatabase target; the other targets do not have dependencies because I want them to be able to run separately.
The items you will need to configure are the DatabaseName, GetPath, SolutionProjectsToBuild, and DatabaseProjectsToBuild values. Once this is done, you should place this file near or at the root of your project (the location of the file will be the MSBuildProjectDirectory value). You can then execute MSBuild, calling the targets, or chaining them together. You should run this from a Visual Studio Command Prompt, as it uses TF.exe. This command is part of the TFS Power Tools, so ensure that you have it installed.
For our project, I created a .bat file that contains the following (note my build file is called master.build):
call “C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\vcvarsall.bat” x86
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:GetLatest
I have defined additional targets in my build file to run multiple steps; here is an example of one:
If I want to execute two steps at the same time, in my bat file, I do the following (deploy database pops up in a second command window):
And since the team wanted options, I did the following in my bat file:
Using an MSBuild build file, I created targets for each action: Clean, GetLatest, Compile, BuildDatabase, and DeployDatabase. At the top of the file there are properties (PropertyGroups and ItemGroups) defined that provide some “configuration” information for what will be run. Notice that the DeployDatabase target explicitly depends on the BuildDatabase target; the other targets do not have dependencies because I want them to be able to run separately.
Debug Any CPU false true YourDBName "if exists(select top 1 1 from sys.databases where [name] = '$(DatabaseName)') BEGIN ALTER DATABASE $(DatabaseName) SET SINGLE_USER WITH ROLLBACK IMMEDIATE;drop database $(DatabaseName);END" .\sqlexpress sqlcmd -E -S $(DatabaseServer) -Q $(DropDatabaseSQL) RunCodeAnalysis=$(RunCodeAnalysis);SkipInvalidConfigurations=true;RestorePackages=false;
The items you will need to configure are the DatabaseName, GetPath, SolutionProjectsToBuild, and DatabaseProjectsToBuild values. Once this is done, you should place this file near or at the root of your project (the location of the file will be the MSBuildProjectDirectory value). You can then execute MSBuild, calling the targets, or chaining them together. You should run this from a Visual Studio Command Prompt, as it uses TF.exe. This command is part of the TFS Power Tools, so ensure that you have it installed.
For our project, I created a .bat file that contains the following (note my build file is called master.build):
call “C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\vcvarsall.bat” x86
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:GetLatest
I have defined additional targets in my build file to run multiple steps; here is an example of one:
If I want to execute two steps at the same time, in my bat file, I do the following (deploy database pops up in a second command window):
cmd /c start cmd /k “C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:DeployDatabase /m:2″ C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:Compile /m:2
And since the team wanted options, I did the following in my bat file:
call "C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\vcvarsall.bat" x86 cls echo Please select one of the following options: echo 1. Get Latest, Compile, and Deploy DB (default in 10 seconds) echo 2. Get Latest and Compile only echo 3. Compile only echo 4. Deploy Database Only echo - choice /C:1234 /N /D:1 /T:10 /M:"Your selection >> " if %ERRORLEVEL% == 1 GOTO FULL if %ERRORLEVEL% == 2 GOTO GETCOMPILE if %ERRORLEVEL% == 3 GOTO COMPILEONLY if %ERRORLEVEL% == 4 GOTO DATABASEONLY GOTO END :FULL C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:CleanGetLatest cmd /c start cmd /k "C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:DeployDatabase /m:2" C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:Compile /m:2 GOTO END :GETCOMPILE C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:CleanGetLatest cmd /c start cmd /k "C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:BuildDatabase /m:2" C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:Compile /m:2 GOTO END :COMPILEONLY C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:Clean /m:2 cmd /c start cmd /k "C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:BuildDatabase /m:2" C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:Compile /m:2 GOTO END :DATABASEONLY C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:DeployDatabase /m:2 GOTO END :END echo on Pause
Wednesday, June 6, 2012
Runas /netonly
I discovered the runas /netonly gem today and it is wonderful! Yes, wonderful!
The runas command allows you to run a program on a machine as a different user than what you are logged in as. This is great when you are doing same domain activities, but what about crossing domains? This is the issue the /netonly switch solves. If you include this switch, it will run the program as your logged in user, but any network calls will be sent as if they came from the user you specified! Thus, if I execute something like:
runas /netonly /user:AnotherDomain\AnotherUser devenv
This runs Visual Studio as my user, but any network calls (TFS, Database, etc.) will use AnotherDomain\AnotherUser. A SQL server with Windows Authentication only can now be connected to via this command on your machine not on the same domain as the SQL server. You can run code locally in Visual Studio and debug, connecting via Windows Authentication to a server on a different domain.
Yes, this is wonderful!
The runas command allows you to run a program on a machine as a different user than what you are logged in as. This is great when you are doing same domain activities, but what about crossing domains? This is the issue the /netonly switch solves. If you include this switch, it will run the program as your logged in user, but any network calls will be sent as if they came from the user you specified! Thus, if I execute something like:
runas /netonly /user:AnotherDomain\AnotherUser devenv
This runs Visual Studio as my user, but any network calls (TFS, Database, etc.) will use AnotherDomain\AnotherUser. A SQL server with Windows Authentication only can now be connected to via this command on your machine not on the same domain as the SQL server. You can run code locally in Visual Studio and debug, connecting via Windows Authentication to a server on a different domain.
Yes, this is wonderful!
Tuesday, May 8, 2012
Config Transforms for Elements
I would have thought this was obvious, but it took me a bit to figure it out. The config file transforms that are available for web.config files and for all other files can be used to replace sections of a config file, based on a project configuration. Most of the examples show changing attributes. I wanted to change the entire element, in this instance, connectionStrings. To do this, you simply put the Replace value in the Transform attribute on the element:
Subscribe to:
Posts (Atom)