-- Prepare sample data
DECLARE       @Keywords TABLE
       (
              FaqID INT,
              Keyword VARCHAR(200)
       )

INSERT @Keywords
SELECT 1, 'help' UNION ALL
SELECT 1, 'resolve' UNION ALL
SELECT 1, 'issue' UNION ALL
SELECT 2, 'Red Herring'

DECLARE       @Faq TABLE
       (
              FaqID INT,
              Question VARCHAR(MAX),
              Answer VARCHAR(MAX)
       )

INSERT @Faq
SELECT 1, 'This is stupid question', 'This is a stupid answer'

DECLARE @Question AS VARCHAR(200)

-- Prepare user supplied parameter
SET @Question = 'How can we help resolve your issue'

-- Show the expected result
SELECT        f.Question,
              f.Answer
FROM          @Faq AS f
INNER JOIN    (
                     SELECT        FaqID         
                     FROM          @Keywords
                     GROUP BY      FaqID
                     HAVING        SUM(CASE WHEN @Question LIKE '%' + Keyword + '%' THEN 1 ELSE 0 END) = COUNT(*)
              ) AS d ON d.FaqID = f.FaqID

-- Prepare user supplied parameter
SET @Question = 'How can we help resolve your problem'

-- Show the expected result
SELECT        f.Question,
              f.Answer
FROM          @Faq AS f
INNER JOIN    (
                     SELECT        FaqID         
                     FROM          @Keywords
                     GROUP BY      FaqID
                     HAVING        SUM(CASE WHEN @Question LIKE '%' + Keyword + '%' THEN 1 ELSE 0 END) = COUNT(*)
              ) AS d ON d.FaqID = f.FaqID

 

Source Click Here.

A Simple Single-Child Model

Consider the following logical data model:

• There are multiple Companies
• Each Company has many Projects
• Each Project has many Tasks
• Each Task has a Status, selected from a global list of pre-defined Statuses.

  Status   Companies
      \       |
       \      |
        \  Projects
         \    |
          \   |
           Tasks

Let us say that we decide that the primary key of Companies, Projects, Tasks, and Status are all Identity (auto-number) columns, since we would like to auto-generate primary keys for those tables.

So, we have something like this:

Table       Primary Key     Foreign Key References
---------   -----------     ----------------------
Status      StatusID        n/a
Companies   CompanyID       n/a
Projects    ProjectID       Companies(CompanyID)
Task        TaskID          Projects(ProjectID), Status(StatusID)

At this point, things work fine -- we can enforce full referential integrity with our identity primary keys. We can't necessarily eliminate duplicate data, but suppose that is not an issue and/or we have unique constraints in place.  Choosing to follow the "every primary key must be an identity rule" did not have any adverse affects and we have a nice, clean data model.

Parents with Multiple Children

Now, allow me to add one little wrinkle.  Suppose that the available Statuses for each Task is not defined globally, but at the Company level.  That is, each Company has its own list of Statuses to which a Task can be assigned.

     Companies
      /     \
     /       \
  Status   Projects
     \       /
      \     /
       Tasks

This means that the Status table now needs a Foreign Key reference to the Companies table (to indicate which Company each Status belongs to), giving us:

Table       Primary Key     Foreign Key References
---------   -----------     ----------------------
Companies   CompanyID       n/a
Status      StatusID        Companies(CompanyID)
Projects    ProjectID       Companies(CompanyID)
Task        TaskID          Projects(ProjectID), Status(StatusID)

Are there any other changes we need to make to this data model?  Or is simply adding a CompanyID column to the Status table enough to facilitate this change?  Remember, our goal as always is full referential integrity using primary and foreign key constraints wherever possible.

Well, there is an issue:

Nothing in this data model stops us from assigning a Status to a Task that is not defined for that Task's parent company.   We have no way of enforcing this right now with our current constraints.  Our physical data model is flawed.  (This is also described a bit here.)

Identities Are Part of the Solution

This is very easily fixed, but it can only be done by violating the "all tables just need an identity primary key" rule.

First, remember this: just because an identity column is unique does not mean that that column cannot be part of a primary key.  So, even though maybe you decide that you really like integer primary keys for your Status table (when probably a simple CHAR(2) code is all you need), if Status codes are child entities of Companies then we can instead create a composite primary key like this:

Table          Primary Key             Foreign Key References
---------      -----------             ----------------------
Status         CompanyID,StatusID      Companies(CompanyID)

So now, with the two column primary key and one of those columns referencing another table, Statuses are truly a child entity of Companies.  Even though it seems that having a two-column primary key is not necessary since the StatusID column in itself is unique, you will see that to physically enforce the very simple logical model we are working with, it's required!

Now, just as Statuses are child entities of Companies, so are Projects.  And as with the StatusID column, we have a nice unique identity ProjectID column that is unique enough that it could be our primary key -- but it shouldn't be.  Once again, being a child entity of a Company, and with the goal of wishing to enforce integrity throughout our entire model, we find that the true primary key of our Project table needs to be a composite of CompanyID/ProjectID:

Table       Primary Key             Foreign Key References
---------   -----------             ----------------------
Projects    CompanyID,ProjectID    Companies(CompanyID)

None of this seems necessary yet -- why are we messing with our perfectly fine keys? -- but soon it will all make sense.

Looking at Tasks, we know we have a foreign key reference to Projects, and we know the primary key of Projects is now (CompanyID, ProjectID).  Also, remember that we changed our Status table as well to have a composite primary key including the CompanyID column.  Thus, we now know that we need to add a CompanyID column to our Tasks table to enforce these FK constraints:

Table       Primary Key     Foreign Key References
---------   -----------     ----------------------
Task        TaskID          Projects(CompanyID,ProjectID), Status(CompanyID,StatusID)

OK, so we are making our primary keys bigger when it doesn't seem necessary, and now our foreign key constraints are getting more complicated as well since they now involve multiple columns.

Why did we bother doing all this?

Because now we have full, complete referential integrity, that's why!

Examining the Results

With these changes in place, only Status codes belonging to a Task's Company can be assigned to that Task.  By propagating the CompanyID column down to our child tables, we have the column necessary to enforce full referential integrity as dictated by our logical model.

So, in the end, it looks like this:

Table       Primary Key            Foreign Key References
---------   -----------            ----------------------
Companies   CompanyID              n/a
Status      CompanyID,StatusID     Companies(CompanyID)
Projects    CompanyID,ProjectID    Companies(CompanyID)
Task        TaskID                 Projects(CompanyID,ProjectID), Status(CompanyID,StatusID)

Just this little tweak in our logical model -- that each Company has their own list of Statuses -- required all of these changes in the physical database schema!  What gives?

Well, remember: if the logical model changes, the physical data model should change. If it doesn't, then you are probably not modeling it correctly or accurately to maintain strict data integrity in the first place.

"All-identity-all-the-time" data modelers often claim that only using identities makes things easier and "more flexible". As we can see from this example, it can work in some cases (back when Statuses were global), but not in every case.  Sometimes, if your goal is to enforce strict data integrity, you simply cannot create your physical schema that way.  If you want to approximately model your data, then all identities can be nice. If you want to accurately model your data, then it simply is not always possible with only single-column identity keys as clearly demonstrated here.

"Identifying" When Using Only Identities Will Not Work

The Companies table originally had just one child: Projects.  But once we determined that Status codes are  also a child entity of Companies, we now had multiple children for the Companies table: Projects and Statuses.

Now, that in of itself is fine; at this point, there is no reason to change anything.  We can have those entities both references the Companies table via CompanyID, and both of those tables can have single column identity primary keys.

However, also in our data model, we see that at some point a child entity of Projects (Tasks) requires that we relate Projects to Statuses.  Now, suddenly the fact that Parents and Statuses have a common parent -- CompanyID -- is very important.   This is because we cannot allow a Project from one company to be assigned to a Status from another.  Once we identify this situation, we realize that the way to handle this is that the primary keys of both the Projects table and Status table must also include the CompanyID column.

With the composite primary keys to those children with the common parent in place, we can now relate them to each other as needed and we will have full data intregrity between those two entities and any of their children.  You can see this in our new data model, as it will not allow you to assign a Status to a Task unless it has been defined for the Task's company.  We would not be able to enforce this without ensuring that the Task table had a full normalized, non-redundant CompanyID column, and the way to do that was to ensure that the primary key of Projects was CompanyID/ProjectID and that the primary key of Status was CompanyID/Status.   The rest follows naturally when assigning the foreign key constraints.

Another Example

Suppose we have Stores, and each Store has Departments, and each Department has Employees. That is a straight-forward, simple data model where no two entities share the same parent, and we can just use single-column identities all the way throughout and be fine.

But what if each Store has multiple Locations?  And each Employee works at a Location.  We cannot juse use LocationID as a primary key of Locations, otherwise we can assign any random Employee to any random Location, regardless of the Store they belong to.

So, our Location table needs to have a primary key of StoreID/LocationID.  Now we also need the StoreID column in the Employee table to relate Employees to Locations.  To get this, we need to also include StoreID in the primary key of the Department table. With that in place, we can now properly relate Employees to Locations and we are guaranteed they will be for the same Store.

Once again, once we saw that two entities had the same parent (Locations, Departments), and that somewhere "down the line" those two entities would be related (via Employees), it becomes clear that we need to incorporate the common parent's primary key (StoreID) into the primary key of those two child entities (Locations and Departments) to enable that relation (in the Employees table) to function properly.

Summary

There are 3 main points I am trying to make here today:

1. The "all-identities-all-the-time" approach can work well in some cases, but not in all.  If you insist on only using all identities for all of your primary keys, you're not always going to have strictly accurate data integrity.
   
2. Don't forget that Identity columns can be part of a composite primary key.
   
3. When a table has multiple child entities in the database, and there are any relations between any of those child entities, then all of those child entities should have a composite primary key that includes the parent table's primary key columns to facilitate those relations.
   

This is not to say that the "all-identities-all-the-time" approach is always wrong, just that it is not always right.  And it certainly is not always "easiest", unless you find continually scrubbing invalid data to be an easy task.

Source Click Here.

Fast on the heels of SQL 2008 is the Feature Pack for SQL 2008.  Cool goodies include stand-alone installers for SQLCMD and the SQL Native Client,  SQL 2008 Server Management Objects, SQL 2008 pre-defined Policies, and lots more. 

You can find it here.

http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C

 

Source Click Here.

Has anyone used SQL Server FineBuild before?  According to the web site, it "provides 1-click install and best-practice configuration of SQL Server 2005." 

It doesn't mention anything about clusters though, which is what I'd be interested in as installing SQL Server on a cluster is a bit of a pain. 

Do you know of other tools that simplify the installation of SQL Server?  Do they work on clusters?

Source Click Here.

Fellow SQL Server enthusiast and online friend of mine, Jonathan Kehayias is very active in the MSDN Forums. He frequently provides good, insightful information to those seeking advice.

In response to a recent question in the Database Engine Forum, Jonathan provided a good example how a process marked as sleeping can still hold locks on a SQL Server resource. I'd like to share a slightly modified version of it with you; you can read his original postings here.

Let's consider a very simple table in tempdb. Use SQL Server Management Studio to execute the following query:

USE tempdb ;
GO

--create a table for the demo
CREATE TABLE demo
        (
         column1 INT NOT NULL
                     IDENTITY
        ,column2 VARCHAR(10)
        ) ;

 

Next let's start a transaction and insert a new row into our newly created table.

--start a transaction
BEGIN TRANSACTION ;

--insert a row into the transaction
INSERT
        demo ( column2 )
VALUES  ( 'something' ) ;

 

For this demo, make sure you don't commit the transaction. Now, open a new query window in Management Studio so we can examine the locks.

USE tempdb ;
GO

--what's the dbid for tempdb?
DECLARE @dbid INT ;
SET @dbid = DB_ID() ;

--what's objectid for our demo table?
DECLARE @objectid INT ;
SET @objectid = OBJECT_ID(N'tempdb.dbo.demo') ;

--look at locking in the tempdb
SELECT
        resource_type
       ,resource_database_id
       ,resource_associated_entity_id
       ,request_mode
       ,request_type
       ,request_session_id
FROM
        sys.dm_tran_locks
WHERE
        resource_database_id = @dbid ;

--limit the results to only the demo table
SELECT
        *
FROM
        sys.dm_tran_locks
WHERE
        resource_database_id = @dbid AND
        resource_associated_entity_id = @objectid ;

 

The first resultset shows that there are four locks in the tempdb database. Obviously, I'm creating this demonstration on a non-production server. The second resultset limits the output on only the demo table.

And now, let's look at the information for the offending process.

--examine the
EXEC sp_who 58 ;

From this resultset, you can see that our process is considered 'sleeping' and 'awaiting command', yet it maintains its locks on the table.

When you give this some thought it makes sense. The process isn't actively doing anything at the moment, however it has an active transaction in progress. Until that transaction is either committed or rolled back, the locks will remain.

Go back to the original query window and commit the transaction.

--commit the transaction
COMMIT TRANSACTION ;

Re-running the queries from the second query window shows that the locks have been released.

Cheers!

Joe

Source Click Here.

Solutions and Projects were one of the really cool features introduced in SQL Server 2005, judging from the responses I got when I showed how it works.  Personally, I use them a lot.  SQL 2008 has the same feature in SQL Server Management Studio.  However, SSMS 2008 breaks this feature in SQL 2005 SSMS when installed side-by-side.

See all the ugly details here:

 https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=361706

This also affects 64-bit systems, I just happened to find it on a 32-bit box first.

I am guessing that this failure is inherited from SSMS's Visual Studio ancester, which may make it difficult for the SQL team to fix..  No matter how hard I try, I cannot come up with a good reason that it should work this way.  I have thought of several bad reasons, but I will hold off on the rock throwing.

 

Source Click Here.