In my opinion the expression: SUBSTRING(p.permlist,t.n+1, CHARINDEX(',',p.permlist,t.n+1)-t.n-1) permelement caused the problem, and the the reason behind this is that the optimizer decided to evaluate the join condition: p.permelement=d.elemid

We are dealing with SQL Server 2008 Enterprise Edition SP1 CU6 32-bit installed on Windows Server 2008 R2 (even though it is may installed on Windows 7, I did not consider this in my answer, because it is a client operating system, and it is not support...

**Scalar Functions** There are two kinds of scalar functions: - Built-in scalar functions like DATEADD. - User-defined scalar functions. And to answer this quiz correctly I will discuss each kind influence on performance. But as my answer w...

<p><i>"The method you choose depends on your replication topology and the needs of your application and its users.</i></p> <p><i>Use merge replication when:</i></p> <ul> <li><i>Replicated data is read and updated at the Subscriber.</i></li> <li><i>S...

There is an ambiguity in this question, and based on my interpretation of it, I would agree with the other answerers regarding the resemblance between this question and question #4. but to answer this question we should provide table structure, and popu...

The CXPACKET wait type is a parallel processing wait type that occurs when multiple threads are used to do the operation. When data distribution between threads is uneven, the thread with the smaller set of data will wait with this wait type for the oth...

SQL Server tries to reuse execution plans that have already been generated and save it in the plan cache, rather than producing a completely new execution plan each time, because query optimization can be a time consuming process. but in some cases a pr...

If we shutdown the SQL Server instance it is not guaranteed that all dirty pages from the database were written to disk before the shutdown. There would be no problem when you attach the database if the log file is available (The log file has a record o...

In the first query SQL Server considered the mis-spelled `UNION` as an alias (execution plan and results confirm this). In the second query SQL Server was unable to parse the query because it is not allowed to have an alias of more than on word (unle...

"The only solution available in the Standard edition, besides writing your own front end in .NET, is to create a subscription for each unique report, person, and parameter. After only a short amount of time, you've got a ton of subscriptions to manage."...