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 →
Let me start by writing I am a supreme VB6 programmer, but I have very little experience with VB.Net, so I think I still need some more time learning SmartAssembly. SmartAssembly make obfuscating and merging dll files a piece of cake!
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 →
Yesterday I got the brilliant idea to format my laptop and reinstall Windows 7 from scratch. I've had a few problem last month, mostly due to upgrading the preinstalled Vista to Windows 7.
Read more →
PASS Scania is a new PASS chapter in Sweden which will promote the interest, networking and knowledge for professional SQL Server-users such as developers, DBA's and BI-specialist in Skåne. The Chapter also has a business alliance with an existing usergroup SQLUG.
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 →
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 →
Today, it seems the transfer for the MSDN subscription I won for the Adam Machanic Grouped String Concatenation finally is done. It took almost 4 months with a lot of phone calls with Microsoft (I have spent more than 7 hours in this case).
Read more →
I have previously posted some algorithm how to find the Nth weekday for a fixed period such as month, quarter or year. See http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx This algorithm in the function allows you to get a weekday for any arbitrary period, both forward and backwards.
Read more →
Based on Itzik's suggestion for sequence generators, I rewrote them and adapted them for my needs. Itzik showed how to work around a big issue for some cases where the query optimizer actually tried to produce all possible combinations before returning the wanted records.
Read more →