This time I will show you an algorithm to do the dreaded bin-packaging using recursion and XML. First, create some sample data like this -- Prepare sample data DECLARE@Sample TABLE ( RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Expense SMALLMONEY NOT NULL ) -- Populate sample data INSERT@Sample ( Expense ) VALUES (12.
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 →
Here is a generic algorithm to get the Excel column name according to it's position. By changing the @Base parameter, you can do this for any sequence according to same style as Excel.
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 →
In my past competition for Phil Factor 'Subscription List' SQL Problem, I presented a technique of Ordered CTE Update which is explained more in detail by Mladen Prajdic. In the comments to the in-depth analysis follow-up article by MVP Kathi Kellenberger, there is now some sort of debate whether or not if it's responsible to present a technique like that.
Read more →
Today I answered a question where OP wanted to convert a 64-bit filetime value to a system time. After a little reading about FILETIME, I learnt that the number is a value for 100 ns passed since January 1st 1601.
Read more →
With SQL Server 2008, we can easily use DECLARE@bin VARBINARY(MAX) SET @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8 SELECTCONVERT(VARCHAR(MAX), @bin, 2) But how can we do this in SQL Server 2005?
Read more →
This function is just the opposite of this one, http://weblogs.sqlteam.com/peterl/archive/2009/05/27/Extended-ISO-week-function.aspx. The function in the link returns the ISO week number from a given date, and the function below returns the monday's date from an ISO week.
Read more →
Joe Celko has posted a new Stumper - The Class Scheduling Problem here http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/ Here is one suggestion to solve the problem. It's linear in time so it should be very fast.
Read more →
This example is based on AdventureWorks database. More details about the task is found here http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx. The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days.
Read more →
This is one way to create permutations of a word. It is certainly not the fastest way (there are hardwired and specialized approaches, and there are other algorithms such as Fike) but this is very simple.
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 →
This is currently for ISO weeks only, but if you have the week number, the weekday and year and you want that date in return, you can use this function below.
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 →