For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden.
Read more →
This bug has haunted me for a while, until today when I decided to not accept it anymore. So I filed a bug over at connect.microsoft.com, https://connect.microsoft.com/SQLServer/feedback/details/636074/some-datatypes-doesnt-honor-localization, and if you feel the way I do, please vote for this bug to be fixed.
Read more →
Or ask yourself, "How much of the database has changed since last backup?". Here is a simple script that will tell you how much (in percent) have changed in the database since last backup.
Read more →
When I try to rename a node name such as a table or column in Management Studio, I cannot use DELETE key to remove previous characters. However, I can use BACKSPACE key.
Read more →
Hi! Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity.
Read more →
SELECTu.name, l.dbname FROMsys.sysusers AS u INNERJOIN sys.syslogins AS l ON l.sid = u.sid
Read more →
-- Setup user supplied parameters DECLARE @WantedTable SYSNAME SET@WantedTable = 'Sales.factSalesDetail' -- Wanted table is "parent table" SELECT PARSENAME(@WantedTable, 2) AS ParentSchemaName, PARSENAME(@WantedTable, 1) AS ParentTableName, cp.Name AS ParentColumnName, OBJECT_SCHEMA_NAME(parent_object_id) AS ChildSchemaName, OBJECT_NAME(parent_object_id) AS ChildTableName, cc.
Read more →
SELECT name AS Setting, CASE WHEN @@OPTIONS & number = number THEN 'ON' ELSE 'OFF' END AS Value FROMmaster..spt_values WHEREtype = 'SOP' AND number > 0 Or this SELECT* FROMsys.
Read more →
Up until now, I have used convuluted approaches to get the current user client IP-address. This weekend I browsed Books Online for SQL Server 2008 R2 (November CTP) and found this new cool function!
Read more →
Filtered indexes is one of my new favorite things with SQL Server 2008. I am currently working on designing a new datawarehouse. There are two restrictions doing this
It has to be fed from the old legacy system with both historical data and new data It has to be fed from the new business system with new data When we incorporate the new business system, we are going to do that for one market only.
Read more →
I created a small table with about 150,000 records in order to do some aggregations. There are some prerequisites to comply for, but that is easily done with replace function. With Azure, it took almost 4 times as long time as SQL Server Express on my old development machine to complete the query.
Read more →
The few last days, our hosting company have updated their VMware environment a number of times and thus have forced equal number of restarts for our database servers. The problem with this is that one of the databases, Yoda, needed 1.
Read more →
Yesterday, I decided to install Microsoft SQL Server 2008 R2 (November CTP) on my local machine. I already had SQL Server Express 2008 installed so the first step was to uninstall Express, since there seemed to be no upgrade path.
Read more →
This is a piece of code I often use to see if the database server is running in a virtual environment. That is not always obvious or known by the developers.
Read more →
Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was declare@s varchar(100) set@s = 'xxxx yyyyy zzzzzz' SELECTREPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '') I used CHAR(2) because that is not commonly used in normal texts.
Read more →
Yesterday I hade the unfortenate task to change the database collation for a particular database. Not only the default database collation should be changed, but also all columns! After some investigating about how to do this, I noticed that check constraints and foreign key constraints must be removed before changing a collation.
Read more →
I installed SQL Server Enterprise 2008 R2 on my laptop as a new instance. I also have SQL Server Developer 2008 as default instance. For the default instance, I have a database named Test on a separate partition of my laptop harddrive.
Read more →
This is a simple query for creating a script for your foreign keys in your database. It may need tweaking for composite keys. If that's the case, see here how to concatenate http://www.
Read more →
Some time ago, I displayed how to work with XML data when searching for data stored in a XML column. Here Some XML search approaches and here Updated XML search (test case with variables).
Read more →
With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement. The drawback is that there is no way to filter the returned resultset directly.
Read more →