Snowcounty

1. Explain the similarities and differences between a primary key and a unique key. Both primary key and unique key are used to identify a row as a unique entity for a table. A primary key can not be null; while a unique key can be null. 2. Explain the difference between a clustered index and a non-clustered...

A link to SQL 2005 sample databases: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction...

A Cartesian join is when you join every row of one table to every row of another table. You can also get one by joining every row of a table to every row of itself. One common mistake one makes is that when you join a table with itself. It forms a cartesian join. For example, a table with four duplicate...

Here is a situation, there is a table with over 100K records, using normal join would take a couple of minutes to complete. So I created one temp table to insert and update data. The result reduced to 2 minutes. Still it is not good. Here is a different version that using two temp tables. Insert into...

Database Design Principles: 1. 3 rd Normal Form: 1). Atomicity, each entity in a database should be indivisible and consist of a single logical entity. 2). Primary key dependence, the primary key for an entity should be solely required to identify an entity. 3). Attribute independence, every attribute...

There are two ways to select or delete duplicate rows in a sql table. SELECT sw1.term_id FROM myduplicate_table sw1 INNER JOIN myduplicate_table sw2 ON sw1.word_text = sw2.word_text WHERE sw1.term_id > sw2.term_id GROUP BY sw1.term_id, sw1.word_text OR SELECT * FROM TABLE1 A WHERE ID NOT IN (SELECT...

SELECT FirstName, LastName, Salary, DOB, CASE Gender WHEN ‘M’ THEN ‘Male’ WHEN ‘F’ THEN ‘Female’ END FROM Employees

Create a database user USE [TEMPDB] DECLARE @PW VARCHAR(50) SET @PW = ‘TEMPDB_user!!’ Exec(’CREATE LOGIN TEMPDB_user WITH PASSWORD = ‘'’ + @PW + ‘'’, CHECK_POLICY = OFF’) Exec(’CREATE USER [TEMPDB_user] FOR LOGIN [TEMPDB_user] WITH DEFAULT_SCHEMA=[dbo]’) GRANT table access to an user GRANT SELECT...

To create a temp table: CREATE TABLE #tmpTable ( tmpID int, tmpName char(30) ) select name from tempdb..sysobjects where name like ‘#tmpTable%’ drop table #tmpTable Temp table is a very useful way to compute complex business rules within database. The computing result is much, much faster then through...