To check concurrency violations, it’s common to have a Timestamp column. This column represents some kind of version of that row, and has nothing to do with the actual time. In fact, the information is stored as binary(8) when non-nullable, and varbinary(8) when nullable. Anyway, the value in this column is being used to check [...]
Archive for the ‘SQL’ Category
Sql DataTypes: Float vs Decimal
I’ve never been really confident with my choices of data types in MS Sql, especially when it comes to float and decimal. So today, I’ve done some research… Here are some important findings/remarks: * The statement: “You should avoid Float whenever possible” is just WRONG. There are definitely scenarios where calculations with floats show rounding [...]
Restore Sql 2008 backup to Sql 2005
hmm. I recently did a clean install of Windows 7, and that was a nice opportunity to get of rid of all redundant software. So I only installed Sql 2008 because it must be backwards compatible. I thought: I can open a Sql 2005 database in Sql 2008 Management Studio, create a backup (unedited), [...]
Generate INSERT INTO script for existing data in Sql Server 2008
It always amazes me how little features are built into Sql Server Management Studio. One of these (trivial) things is to generate a script which inserts existing data. Fortunately, it has become a feature in Sql Server 2008. short story: right click the database > tasks > generate scripts > enable “script data” option full [...]
Sql Server 2008: Can’t save changes that require Recreation of Database
There's a silly new default in SQL Server's Management Tools: When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the management tools will not allow you to save the changes. The fix is: Go to Tools | Options [...]
Open Sql Server Express instance while working in Visual Studio
In my Visual Studio project, I've added a local mdf database ("Service based database") to support Sql Server Express. By default, these databases are configured to run as user-instances. As a side-effect, it's not possible to work with your project as you would normally do, and at the same time open the database in Management [...]
Installation of Sql Server Express 2008
Ok, the installation of Sql Server (Express) 2008 is not of the type "next-next-finish". I first installed the Sql Server Express 2008 instance, and then I wanted to have the Management Studio. (not a very exotic scenario is it?!) I ran the setup, and I selected the option to add new features to an existing [...]
50 meest cpu-vretende queries in SQL
Ik heb hier een query die een lijst toont met de meest cpu intensieve queries die in sql zijn uitgevoerd. Handig als je performance verbeteringen wilt doorvoeren. Vraag me overigens niet om de details, gezien ik geen sql guru ben [cc]select top 50 (a.total_worker_time/a.execution_count) as [avg_cpu_time], convert( varchar, last_execution_time) as ‘Last_execution_time’, Total_Physical_Reads, substring(b.text, a.statement_start_offset/2, (case [...]