# Thinking outside the box

Patron Saint of Lost Yaks

## June 2010 Blog Posts

##### Relational division

I came across an interesting post on Microsoft SQL Server forum this afternoon. It was a question about Relational algebra and the poster wanted to have an efficient query to solve his problem. The problem could be solved with relational division, but there is no such operator in SQL Server. Maybe there will be some day. For a fully working solution, see http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx But for now there is no such operator, so we as developers have to find our own ways. First prepare and populate some sample data -- Prepare sample data DECLARE @Sample TABLE         (             ParentID INT NOT NULL,             Keyword VARCHAR(25) NOT NULL,             UNIQUE (ParentID, Keyword)         ) -- Populate sample data INSERT ...

posted @ Wednesday, June 30, 2010 9:41 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Another bin-packaging algorithm using recursion and XML

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.51),         (45.63),         (66.35),         (92.66),         (65.46),         (54.01),         (32.23),         (27.16),         (78.92),         (14.58)   Next, we need to create a variable to hold the user's wanted total sum. -- Prepare user supplied parameter DECLARE @WantedSUM SMALLMONEY = 111.09 And we also need to create a temporary staging table to hold the valid combinations   -- Prepare temporary staging table DECLARE @Temp TABLE         (             CombID INT...

posted @ Wednesday, June 30, 2010 1:44 PM | Feedback (1) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

##### Bug in SQL Server Management Studio

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. Please vote here https://connect.microsoft.com/SQLServer/feedback/details/570758/cannot-use-delete-key-in-ssms-and-object-explorer to fix this little, but annoying, issue. //Peso

posted @ Friday, June 25, 2010 10:13 AM | Feedback (0) | Filed Under [ SQL Server 2008 Administration ]

##### Ten days left

As some of you know, I was awarded Microsoft Most Valuable Professional (MVP) for SQL Server in July last year. Now it's time to see if I get my MVP renewed, or if I lose the award. I honestly believe it's a good thing MVP status only lasts for one year at a time. Knowledge is fresh. Things that worked in the past may not work any longer due to evolvement, and there are smarter ways to do things now, than before. Being an MVP is a responsibility. It does mean you have more than average knowledge of SQL Server and how...

posted @ Sunday, June 20, 2010 9:21 AM | Feedback (0) | Filed Under [ Miscellaneous ]

##### SQL Server 2008 Compression

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. However, the server has 60GB of memory (of which 48 is dedicated to SQL Server service), so all data didn't fit in memory and the SAN is not the fastest one around. So I decided to give compression a go, since we use Enterprise Edition anyway. This is the code I use to compress all tables with PAGE compression. DECLARE @SQL VARCHAR(MAX)   DECLARE curTables CURSOR FOR        ...

posted @ Thursday, June 17, 2010 2:16 PM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Administration ]

##### Simple script to get logins and default database names

SELECT     u.name,            l.dbname FROM       sys.sysusers AS u INNER JOIN sys.syslogins AS l ON l.sid = u.sid

posted @ Monday, June 14, 2010 1:27 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

##### Simple script to get referenced table and their column names

-- 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.Name AS ChildColumnName FROM        sys.foreign_key_columns AS fkc INNER JOIN  sys.columns AS cc ON cc.column_id = fkc.parent_column_id                 AND cc.object_id = fkc.parent_object_id INNER JOIN  sys.columns AS cp ON cp.column_id = fkc.referenced_column_id                 AND cp.object_id = fkc.referenced_object_id WHERE       referenced_object_id = OBJECT_ID(@WantedTable)   -- Wanted table is "child table" SELECT      OBJECT_SCHEMA_NAME(referenced_object_id) AS ParentSchemaName,             OBJECT_NAME(referenced_object_id) AS ParentTableName,             cc.Name AS ParentColumnName,             PARSENAME(@WantedTable, 2) AS ChildSchemaName,             PARSENAME(@WantedTable, 1) AS ChildTableName,             cp.Name AS ChildColumnName FROM        sys.foreign_key_columns AS fkc INNER JOIN  sys.columns AS cp ON cp.column_id =...

posted @ Tuesday, June 08, 2010 4:02 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]