You receive some data from UI and need to populate table Customer if the name received from UI does not exist in database. Which of options below should be used for this purpose:
1.
IF NOT EXISTS ( SELECT * FROM Customer
WHERE Name = @CustomerName )
INSERT INTO [Customer]
( [Name])
VALUES
( @CustomerName)
2.
INSERT INTO [Customer]
( [Name])
SELECT @CustomerName
WHERE NOT EXISTS ( SELECT * FROM Customer
WHERE Name = @CustomerName )
Correct answer:
Option 2 is the one to use
Explanation:
Option 1 has 2 separate statements. The first one applies locks relevant table resources in order to perform check for existence. As soon as it's completed SQL Server releases all applied locks. At the moment parallel SPID may insert a record into the table BEFORE following INSERT statement has applied its locks.
Therefore initial check may be irrelevant at the moment when following INSERT starts. IF there is a unique constraint on Customer.Name INSERT would fail despite you've done the check for existence.
The second option does everything in a single transaction. It applies the locks and holds it until INSERT transaction has finished its job. Another SPID cannot insert another row until all locks on the object applied by INSERT statement are released.
So, the first option is unacceptable, it relies on a user's luck not to have another user doing same thing at the same time. Of course, probability of the failure is quite low but it's a possible event. In active transactional systems with hundreds of transactions per second it does not look so improbable.
Script to run the test:
USE pubs
GO
CREATE TABLE Customer (
ID int IDENTITY(1,1) NOT NULL,
Name nvarchar(100) NOT NULL,
UNIQUE (Name)
)
GO
DECLARE @CustomerName nvarchar(100)
SET @CustomerName = 'Customer2'
IF NOT EXISTS ( SELECT * FROM Customer
WHERE Name = @CustomerName )
BEGIN
WAITFOR DELAY '00:00:05'
/* this 5 sec pause lets you insert same row from another QA window:
DECLARE @CustomerName nvarchar(100)
SET @CustomerName = 'Customer2'
INSERT INTO [Customer]
( [Name])
VALUES
( @CustomerName)
*/
INSERT INTO [Customer]
( [Name])
VALUES
( @CustomerName)
END
SET @CustomerName = 'Customer3'
INSERT INTO [Customer]
( [Name])
SELECT @CustomerName
WHERE NOT EXISTS ( SELECT * FROM Customer
WHERE Name = @CustomerName )
GO
select Object_Id('Customer')
DROP TABLE Customer
What will be the output?
Declare @var int
Select @var = isnull(@var,1) + Value1
From (Select 1 Value1 Union All Select 1 Union All Select 2) as a
Select @var
Answer: 5, the sql behave like v=v+1
What will be the count against each category? Each row in the result is shown as a pair in the answers.
CREATE TABLE #CATEGORY
(CATID INT
,VAL1 INT)
INSERT INTO #CATEGORY VALUES(1,NULL)
INSERT INTO #CATEGORY VALUES(2,1)
INSERT INTO #CATEGORY VALUES(3,2)
CREATE TABLE #DATA
(VAL1 INT)
INSERT INTO #DATA VALUES(1)
INSERT INTO #DATA VALUES(1)
INSERT INTO #DATA VALUES(1)
INSERT INTO #DATA VALUES(2)
INSERT INTO #DATA VALUES(2)
INSERT INTO #DATA VALUES(3)
SELECT C.CATID, COUNT(*)
FROM #DATA D
INNER JOIN #CATEGORY C ON C.VAL1 = D.VAL1 OR C.VAL1 IS NULL
GROUP BY C.CATID
Answer: 1 6, 2 3, 3 2
Simulating memory pressure
You have been asked to optimize a stored procedure that runs against a terabyte-sized database. The stored procedure executes several steps consecutively. The performance problems appear to be mainly I/O related.
You install a severely trimmed down test version of the database (1 GB in size) on your desktop computer running SQL Server Developer Edition. Before you start optimizing, you want to establish a baseline by timing the stored procedure on your development machine, so that you can later compare performance after adding indexes and tweaking code.
However, your desktop has 2 GB of memory installed, and you are concerned that the performance test results may be skewed because the test version of the database fits entirely in cache. What is the best way to simulate the production circumstances as closely as possible?
Correct answer:
Execute sp_configure and RECONFIGURE to limit server memory to 512 MB, then restart the SQL Server service.
Explanation:
Executing "EXEC sys.sp_configure N'max server memory (MB)', N'512'" followed by "RECONFIGURE WITH OVERRIDE" will limit the memory used by your server to 512 MB. Since SQL Server might have already allocated more memory, which will not be released immediately, a reboot will force this memory setting to be respected.
Creating a bigger test database will also work, but involves a lot more work, will cause your tests to run longer, and might require you to clean up your collection of holiday pictures. Removing some RAM from your computer works as well, but will cause all other processes in your computer to slow down as well. Not recommended (especially if you are as clumsy in handling electronic equipment as I am)
Executing DBCC FREEPROCCACHE before starting the tests will cause the FIRST query to run with no data in cache, but the remaining steps of the stored procedure will once more be able to read data from cache. This is not a good simulation of the production circumstances. Starting memory hungry applcations might indeed cause available memory for SQL Server to be reduced. But it will also cause extra strain for the CPU, and you might get SQL Server and the other applications "fighting" for memory or Windows starting to page some applications in and out of memory. Again, not a good simulation of the production circumstances.
Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts
Wednesday, October 1, 2008
Monday, July 16, 2007
SQL hierarchial query
"Solving problems that are hierarchical and nested in nature are best helped with a record oriented approach rather than a SET ORIENTED approach"
This assertion comes from the Visual FoxPro environment, where developers can navigate through result sets (VFP cursors…not to be confused with SQL Server Cursors) at the record level, very quickly and easily. No question, VFP is a great database tool. So great in fact, that some have used it for so long, and to solve so many problems, that it's easy to conclude that VFP's offerings are the only optimal hammer in the toolbox for certain operations.
SQL Server 2005 conforms more closely to the ANSI-99 SQL spec – and some of the features of the ANSI-99 standard making querying more flexible than before. One such feature is Common Table Expressions and recursive querying of hierarchical data. I'm going to devote some blog entries over the next few weeks to recursive querying, as I believe developers regularly face this challenge.
I'm going to start with a very simple example, to demonstrate the mechanics of recursive querying in SQL 2005. First, here's a simple set of hierarchical data – a product hierarchy with Brands, Groups, and Items. Each row contains a unique integer identifier, a description, and a reference to the row's parent:
DECLARE @tProducts TABLE (ID int, Name char(50), ParentID int)
INSERT INTO @tProducts VALUES (1, 'Brand 1', null)
INSERT INTO @tProducts VALUES (2, 'Brand 2', null)
INSERT INTO @tProducts VALUES (3, 'Brand 3', null)
INSERT INTO @tProducts VALUES (6, 'Brand 1, Group 1', 1)
INSERT INTO @tProducts VALUES (7, 'Brand 1, Group 2', 1)
INSERT INTO @tProducts VALUES (8, 'Brand 1, Group 3', 1)
INSERT INTO @tProducts VALUES ( 9, 'Brand 2, Group 1', 2)
INSERT INTO @tProducts VALUES (10, 'Brand 2, Group 2', 2)
INSERT INTO @tProducts VALUES ( 11, 'Brand 3, Group 3', 3)
INSERT INTO @tProducts VALUES (12, 'Brand 1, Group 1, Item 1', 6)
INSERT INTO @tProducts VALUES (13, 'Brand 1, Group 1, Item 2', 6)
INSERT INTO @tProducts
VALUES (14, 'Brand 1, Group 1, Item 1, SKU 1', 12)
Here's the challenge: for any specific item in this table, I want to know all the child records, going all the way down the hierarchy. Think about how you'd handle this query in SQL 2000 using SQL statements. While it was doable, querying hierarchical data was never an easy task. Fortunately, SQL 2005 makes this much simpler. Here's the entire query, which sets a search string, and then retrieves all the child records for that search string:
DECLARE @cSearch char(50)
SET @cSearch = 'Brand 1, Group 1'
; WITH ProductCTE AS
-- Anchor query
(SELECT ID, Name, ParentID
FROM @tProducts
WHERE Name = @cSearch
UNION ALL
-- Recursive query
SELECT Prod.ID, Prod.Name, Prod.ParentID
FROM @tProducts Prod
INNER JOIN ProductCTE
ON ProductCTE.ID = Prod.parentID )
SELECT * FROM ProductCTE
I'll confess, when I first looked at CTE and recursive query syntax, I felt pretty stupid that I couldn't grasp it. So, I took an online example, completely changed the sample data, built my own example, and then I didn't feel quite so stupid. ;)
Here's the deal/trick with recursive queries - in a "normal" SQL SELECT statement, you're querying from a table that isn't changing during the lifetime of the query (OK, data could be inserted from another session, but that's a different topic). You're querying directly FROM one table, and into a totally separate result set.
In a recursive query, some of the data you need to query is actually a result of a prior phase of the query. So results become the source for future results. Think about this with respect to our simple example, where we want all the children of a particular row – we can't pull the lowest level (SKU) without pulling the level above that (Item), and we can't do that without pulling the level above that (Group) and so on.
So let's take a look at the anatomy of the query, which has three parts. The first part is defining the Common Table Expression…
; WITH ProductCTE AS
As I said in my last blog post, a CTE is like a temporary derived table or view, with a very short lifespan. What we're going to do in the next two steps is query into ProductCTE for the search string, and then continue to query all the way down the hierarchy by comparing IDs to ParentIDs.
The second part is called the main or anchor query:
(SELECT ID, Name, ParentID
FROM @tProducts
WHERE Name = @cSearch
The anchor query executes first, so we know that ProductCTE will contain the ID, Name, and ParentID for the single row that matches the search condition.
The final part is the actual recursive query, which is connected to the anchor query with a UNION ALL:
UNION ALL
-- Recursive query
SELECT Prod.ID, Prod.Name, Prod.ParentID
FROM @tProducts Prod
INNER JOIN ProductCTE
ON ProductCTE.ID = Prod.ParentID )
Note that the anchor query is performing a join between the original list of products and the ProductCTE. So each "hit" between an ID and a ParentID will go into ProductCTE, and the query will continue until no more matches are found.
OK, so that handles querying DOWNWARD….suppose we wanted to find all the parent rows instead? Just switch the column names on the INNER JOIN in the recursive query:
ON ProductCTE.ParentID = Prod.ID )
If you're wondering about any limit to the number of recursions, the default is 100, which can be configured: check out MAXRECURSION in the online help if you ever have a hierarchy with more than 100 levels. (I'm almost afraid to see a hierarchy with that many levels!)
See, that wasn't so bad? There are many examples of hierarchical data out there, such as Bill of Material data, organization chart data, etc. Anyone who has ever worked with e-commerce applications is likely VERY familiar with the challenges of hierarchical data. SQL Server 2005 makes life much easier to deal with these challenges.
This assertion comes from the Visual FoxPro environment, where developers can navigate through result sets (VFP cursors…not to be confused with SQL Server Cursors) at the record level, very quickly and easily. No question, VFP is a great database tool. So great in fact, that some have used it for so long, and to solve so many problems, that it's easy to conclude that VFP's offerings are the only optimal hammer in the toolbox for certain operations.
SQL Server 2005 conforms more closely to the ANSI-99 SQL spec – and some of the features of the ANSI-99 standard making querying more flexible than before. One such feature is Common Table Expressions and recursive querying of hierarchical data. I'm going to devote some blog entries over the next few weeks to recursive querying, as I believe developers regularly face this challenge.
I'm going to start with a very simple example, to demonstrate the mechanics of recursive querying in SQL 2005. First, here's a simple set of hierarchical data – a product hierarchy with Brands, Groups, and Items. Each row contains a unique integer identifier, a description, and a reference to the row's parent:
DECLARE @tProducts TABLE (ID int, Name char(50), ParentID int)
INSERT INTO @tProducts VALUES (1, 'Brand 1', null)
INSERT INTO @tProducts VALUES (2, 'Brand 2', null)
INSERT INTO @tProducts VALUES (3, 'Brand 3', null)
INSERT INTO @tProducts VALUES (6, 'Brand 1, Group 1', 1)
INSERT INTO @tProducts VALUES (7, 'Brand 1, Group 2', 1)
INSERT INTO @tProducts VALUES (8, 'Brand 1, Group 3', 1)
INSERT INTO @tProducts VALUES ( 9, 'Brand 2, Group 1', 2)
INSERT INTO @tProducts VALUES (10, 'Brand 2, Group 2', 2)
INSERT INTO @tProducts VALUES ( 11, 'Brand 3, Group 3', 3)
INSERT INTO @tProducts VALUES (12, 'Brand 1, Group 1, Item 1', 6)
INSERT INTO @tProducts VALUES (13, 'Brand 1, Group 1, Item 2', 6)
INSERT INTO @tProducts
VALUES (14, 'Brand 1, Group 1, Item 1, SKU 1', 12)
Here's the challenge: for any specific item in this table, I want to know all the child records, going all the way down the hierarchy. Think about how you'd handle this query in SQL 2000 using SQL statements. While it was doable, querying hierarchical data was never an easy task. Fortunately, SQL 2005 makes this much simpler. Here's the entire query, which sets a search string, and then retrieves all the child records for that search string:
DECLARE @cSearch char(50)
SET @cSearch = 'Brand 1, Group 1'
; WITH ProductCTE AS
-- Anchor query
(SELECT ID, Name, ParentID
FROM @tProducts
WHERE Name = @cSearch
UNION ALL
-- Recursive query
SELECT Prod.ID, Prod.Name, Prod.ParentID
FROM @tProducts Prod
INNER JOIN ProductCTE
ON ProductCTE.ID = Prod.parentID )
SELECT * FROM ProductCTE
I'll confess, when I first looked at CTE and recursive query syntax, I felt pretty stupid that I couldn't grasp it. So, I took an online example, completely changed the sample data, built my own example, and then I didn't feel quite so stupid. ;)
Here's the deal/trick with recursive queries - in a "normal" SQL SELECT statement, you're querying from a table that isn't changing during the lifetime of the query (OK, data could be inserted from another session, but that's a different topic). You're querying directly FROM one table, and into a totally separate result set.
In a recursive query, some of the data you need to query is actually a result of a prior phase of the query. So results become the source for future results. Think about this with respect to our simple example, where we want all the children of a particular row – we can't pull the lowest level (SKU) without pulling the level above that (Item), and we can't do that without pulling the level above that (Group) and so on.
So let's take a look at the anatomy of the query, which has three parts. The first part is defining the Common Table Expression…
; WITH ProductCTE AS
As I said in my last blog post, a CTE is like a temporary derived table or view, with a very short lifespan. What we're going to do in the next two steps is query into ProductCTE for the search string, and then continue to query all the way down the hierarchy by comparing IDs to ParentIDs.
The second part is called the main or anchor query:
(SELECT ID, Name, ParentID
FROM @tProducts
WHERE Name = @cSearch
The anchor query executes first, so we know that ProductCTE will contain the ID, Name, and ParentID for the single row that matches the search condition.
The final part is the actual recursive query, which is connected to the anchor query with a UNION ALL:
UNION ALL
-- Recursive query
SELECT Prod.ID, Prod.Name, Prod.ParentID
FROM @tProducts Prod
INNER JOIN ProductCTE
ON ProductCTE.ID = Prod.ParentID )
Note that the anchor query is performing a join between the original list of products and the ProductCTE. So each "hit" between an ID and a ParentID will go into ProductCTE, and the query will continue until no more matches are found.
OK, so that handles querying DOWNWARD….suppose we wanted to find all the parent rows instead? Just switch the column names on the INNER JOIN in the recursive query:
ON ProductCTE.ParentID = Prod.ID )
If you're wondering about any limit to the number of recursions, the default is 100, which can be configured: check out MAXRECURSION in the online help if you ever have a hierarchy with more than 100 levels. (I'm almost afraid to see a hierarchy with that many levels!)
See, that wasn't so bad? There are many examples of hierarchical data out there, such as Bill of Material data, organization chart data, etc. Anyone who has ever worked with e-commerce applications is likely VERY familiar with the challenges of hierarchical data. SQL Server 2005 makes life much easier to deal with these challenges.
SQl Server Managment
Recently I came accross are really painfull problem of 'msdb label SUSPECT by the recovery'.
The reasons for msdb marked 'SUSPECT' is unrecognizable. But after it, I am unable to access msdb database, which i found out is the backbone for all server related queries. If this happens, you are left with no choice but to restore the DB, but first off all Do you have the backup of msdb database ?
advice : aways take backup of msdb database as soon as you install the sql server.
usefull article : http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1052507,00.html
With SQL Server 2005, there is default remote connections disabled.
use Microsoft SQL Server 2005 -> Configuration Tools -> Surface Area Configuration
To give alias name to an existing Database (which in my project was a limitation; should be local)
use Microsoft SQL Server 2005 -> Configuration Tools -> Configuration Manager (Native CLient Configuration)
Prefer to use authentication as 'windows and sql mode'.
--To do this Right-Click Server on the Managment studio and goto properties
--Select Security and choose desiired authentication.
The reasons for msdb marked 'SUSPECT' is unrecognizable. But after it, I am unable to access msdb database, which i found out is the backbone for all server related queries. If this happens, you are left with no choice but to restore the DB, but first off all Do you have the backup of msdb database ?
advice : aways take backup of msdb database as soon as you install the sql server.
usefull article : http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1052507,00.html
With SQL Server 2005, there is default remote connections disabled.
use Microsoft SQL Server 2005 -> Configuration Tools -> Surface Area Configuration
To give alias name to an existing Database (which in my project was a limitation; should be local)
use Microsoft SQL Server 2005 -> Configuration Tools -> Configuration Manager (Native CLient Configuration)
Prefer to use authentication as 'windows and sql mode'.
--To do this Right-Click Server on the Managment studio and goto properties
--Select Security and choose desiired authentication.
Subscribe to:
Posts (Atom)
