Transact SQL :: Query Plan Shows Table Not Even In Query?
Jul 22, 2015
I am trying to optimize a stored procedure in SQL 2008. When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? I can't just change the query script because the table in question isn't there.
View 10 Replies
ADVERTISEMENT
Jul 27, 2015
I had some SQL queries which are using department ID for join , filter , Group By and Select so , i am having index on department ID of my table File Master scheme ..
CREATE TABLE [dbo].[FILE_MASTER](
[FILE_ID] [INT] IDENTITY(1,1) NOT NULL,
[DEPARTMENT_ID] [INT] NULL,
[CLIENT_ID] [INT] NULL
,[LEAD_DETAIL_ID] [INT] NULL
[code]....
The above index only working when there is condition or group by on department ID .and i when i am querying ..
SELECT DISTINCT CL.CLIENT_ID,CL.LOAN_SANCTION_DATE,MIN(CL.INWARD_DATE)AS Inward
FROM dbo.FILE_MASTER AS CL
GROUP BY CL.CLIENT_ID,CL.LOAN_SANCTION_DATE
and the plan is showing Index scan on index Indx_FM_department_ID .. Why it is not using Index seek , i guess i have both group by Columns in cover index included columns what is the use of cover index then ?
because if i am giving where condition before group by for specific Client ID , Loan Sanction Date it is telling to create separate index on client ID , Loan Sanction Date as per Execution Plan missing index detail ..
View 7 Replies
View Related
Jul 19, 2015
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
View 3 Replies
View Related
Oct 6, 2015
SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
View 3 Replies
View Related
Oct 30, 2015
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
View 0 Replies
View Related
May 15, 2006
Hi Everyone:
*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error
"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I would have searched the forums myself, but at this moment in time, search is broken :(
If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.
It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them when appropriate.
fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message before...at least I don't recall ever seeing it.
Thanks in advance for other suggested examination paths.
View 10 Replies
View Related
Mar 14, 2015
Is only one plan is kept for one query in plan cache?
i heard generally hash is created for a query and plan is search with this hash.
View 2 Replies
View Related
May 21, 2015
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent
Type_1 is 2nd Parent
Type_2 is 3rd Parent
Type_3 is 4ed Parent
View 13 Replies
View Related
Oct 17, 2007
Good afternoon all,
I have created a cube in Analysis Services with a time dimension named Time. The data is only needed at the month level, so the fields in the table that the dimension is based on are [DTE MM] and [DTE YR]. In Visual Studio 2005 or in SQL Server Mgmt Studio I can browse the dimension and I see that Time has the following attributes: Year and Month both Regular attributes and Time (Key attribute). There is also a hierarchy named [Year - Month] and if I browse that it looks good dril down from All to Year to Month.
However, when I point my Reporting Services Datasource to the cube, and start Query Builder, I see two dimensions, [DTE MM] and [DTE YR] and no Time dimension.
[DTE MM] has attributes [DTE MM].[Month], [DTE MM].[Time], [DTE MM].[Year], [DTE MM].[Year - Month].
[DTE YR] has attributes [DTE YR].[Month], [DTE YR].[Time], [DTE YR].[Year], [DTE YR].[Year - Month]
This is causing me huge problems in my report. I need to use a date range in the query. To do this, I created Query Parameters and refer to Report Parameters that will be passed in. For example, I use ="[DTE YR].[Year - Month].[Year].&[" + Parameters!StartYear.Value + "].&[" + Parameters!StartMonth.Value +"]" (thanks to Simon Philips) as the query parameter for the Start Year Month, but if I use the [DTE YR].[Year - Month] in my SELECT, the data is not sliced in my query results. That is to say that the Year and Month show correctly, according to the date range, but the Measure is equal for each month and is equal to the total for the cube. To slice the data, I have to use [DTE YR].[Year].[Year] and [DTE MM].[Month].[Month] in my select, but then the data is shown for every month, i.e. the date range is ignored.
What am I doing wrong, or is this a quirk of RS that I can work around?
Thanks,
Kathryn
View 3 Replies
View Related
May 24, 2007
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
View 5 Replies
View Related
May 18, 2015
How Can I select Table Dynamically from in Side SQL Query
i.e.,
Select * from (Here I want Select the Dynamically from other Query)
View 6 Replies
View Related
Aug 31, 2015
I have a table called USERS, some of its records are marked as hiddenRcord, I want to load those records in a custom page in my asp.net webpage with paging enabled, each page contains 10 records.
I use the statement "SELECT Top 10 tableID,userName FROM USERS WHERE (hiddenRecord=0 AND tableID>@tableID)"
The pagination has 5 links (First,2,3,4,Last), I can of course put the last tableID in link number 2, but I don't know how to do it for the links (3,4,Last).
View 3 Replies
View Related
Sep 16, 2015
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
- SQL Functions cannot use Exec to execute query strings.
- SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
View 14 Replies
View Related
Oct 1, 2015
I have
Customer table, tblCust: ID; Name
1 Peter2 Mary
Product table, tblProduct: ID; Name
1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Name Banana Orange Apple
Peter 0 20 0
View 4 Replies
View Related
Jul 16, 2015
I need a query to list the tables in SQL sever with respective database they belong across databases.
I can obtain list of tables from Sys.Tables / Sys.Objects Views but how do I correlate to which DB a table belongs to?
The Sys.Databases View list the databases with their respective Ids. But, the DBID is not part of Sys.Tables / Sys.Objects Views.
Which View will allow me to fetch database to table mappings?
View 5 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Sep 17, 2015
I have been tasked with writing an update query to update a table with more than 150 million rows of data. Here are the table structures:
Source Tables :
OC
CREATE TABLE [dbo].[OC](
[OC] [nvarchar](255) NULL,
[DATE DEBUT] [date] NULL,
[DATE FIN] [date] NULL,
[Code Article] [nvarchar](255) NULL,
[INSERTION] [nvarchar](255) NULL,
[Code] ....
The update requirement is as follows:
DECLARE @Counter INT=0 --This causes the @@rowcount to be > 0
while @@rowcount>0
BEGIN
SET rowcount 10000
update r
set Comp=t.Comp
[Code] ....
The update took more than 48h and didn't terminate , how to accelerate it ?
View 6 Replies
View Related
May 20, 2015
I'm wondering if there is some sql I can run to check properties on a table. This would be used to verify things like data types, allow nulls and default values have been set to avoid mistakes. This could be done manually one table and one column at a time, but it would be a lot easier to look at it in the results window.
View 5 Replies
View Related
Jun 17, 2015
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
View 3 Replies
View Related
Jul 21, 2015
I have the following UNION ALL query with SELECT INTO @tblData temp table. I would like to confirm if my query is correct.
In my first SELECT statement, I have INSERT INTO @tblData.
Do I need another INSERT INTO @tblData again in my second SELECT statement after UNION ALL?
DECLARE @BeginDate as Datetime
DECLARE @EndDate as Datetime
SET @BeginDate = '7/1/2015'
SET @EndDate = '7/13/2015'
DECLARE @tblData table
[Code] ....
View 3 Replies
View Related
Oct 30, 2015
Lets say I have a table, tblPersons
FirstName | LastName | DateofBirth
----------------------------------------
Thomas | Alva Edison | 10-10-2015
Benjamin | Franklin | 10-10-2015
Thomas | More | 11-10-2015
Thomas | Jefferson | 12-10-2015
Suppose today's date is 09-10-2015 in (dd-MM-yyyy format), I want to perform a query in such a way that I should get the data from the table above WHERE DateofBirth is tomorrow, so I could get the following result.
FirstName | LastName | DateofBirth
----------------------------------------
Thomas | Alva Edison | 10-10-2015
Benjamin | Franklin | 10-10-2015
View 15 Replies
View Related
Jun 9, 2015
I have a non-partitioned table (TableToPartition) and I want to apply an existing partition scheme (PartSch) to it using a query. I didn't find any option so I used the StorageCreate Partition wizard to generate the script.why this clustering magic needed if it is dropped at the end? Isn't there another way without indexing to partition a table, say something with ALTER TABLE? (SQL Server 2012)
BEGIN TRANSACTION
CREATE CLUSTERED INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
(
[ID]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartSch]([ID])
DROP INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
COMMIT TRANSACTION
View 2 Replies
View Related
Jul 10, 2015
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
View 6 Replies
View Related
May 9, 2015
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
View 4 Replies
View Related
Apr 29, 2015
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC,
[Code] ....
View 7 Replies
View Related
Jul 23, 2002
I am noticing a discrepency in query plans when a process is run in Analyzer as either a proc or as straight sql.
I have a query that uses a view of 5 tables that have a check constraint on the year. When I run my query in query analyzer and state year = 1999 along with over parameters then the query plan only looks at the one table.
When I take that query and make a stored proc and run the process passing the year = 1999 along with other parameters the plan states that it is looking at all of the tables in the partitioned view.
Thanks,
Here is a copy of the proc
create procedure testproc
@CUST_I varchar(6),
@FISCAL_DD_D tinyint,
@FISCAL_MM_D tinyint,
@FISCAL_YY_D smallint,
@cont_cvarchar(1),
@invoice varchar(9)
as
Select
CONT_C,
INVC_I,
DIV_C,
REG_C,
LOC_I,
INVC_D,
CUST_I,
CR_PREF_C,
FISCAL_DD_D,
FISCAL_MM_D,
FISCAL_YY_D,
PAY_CODE,
REF_TEXT,
EC_TYPE,
ADJUST_A,
ALLOWANCE_A,
MAT_A,
TAX_A,
FRT_A,
REEL_A,
OTHER_A,
GST_A,
PRIOR_BAL
from MY_FIVE_YEAR_VIEW
whereFISCAL_YY_D = @FISCAL_YY_D
AND cont_c = @cont_c
AND FISCAL_DD_D = @FISCAL_DD_D
AND FISCAL_MM_D = @FISCAL_MM_D
AND (REF_TEXT LIKE '%' + @CUST_I + '%' or REF_TEXT LIKE '%' + @invoice + '%' )
order by cust_i, pay_code
View 1 Replies
View Related
Sep 24, 2002
Hello ,
I wanted to know whether we have an execution plan enabled in SQL 6.5 as we have it in SQL 7.0 and SQL 2000 .
I.e when we execute a query and if we enable ' show execution plan 'then it creates a map and shows the vital statistics .
If that is available on SQL 6.5 then i am missing that tool .
How can i have it installed on my SQL 6.5 server ??
Thanks.
View 3 Replies
View Related
Apr 25, 2006
Here's the setup:
Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.
A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.
Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.
If I open up the view and re-save it, all is well again for a few days.
What gives?
Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.
Any ideas on what causes this and what to do about it?
View 2 Replies
View Related
Mar 19, 2008
I have a query like below .. if i add where Served = 1 , the query takes foreever... if i remove it, it takes only 6 sec...
I am not sure why this is hapening?
select distinct a.Episode_Key,
case when ag.Category IN ('ASMI', 'COOC', 'SPCL') then 'SMI'
when ag.Category = 'SEDC' then 'SED'
when ag.Category = 'ACCA' then 'SA'
when ag.Category like 'CGA%' then 'Gam'
end as [Category],
ag.Agreement_Type_Name as [Agreement],
p.ServiceProvider,
s2.Served
from dbo.Assessment a
INNER JOIN (
select distinct Episode_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Episode_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
LEFT OUTER JOIN (
select distinct Episode_Key, p.ServiceProvider,
1 as [Served]
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '01/01/2008' and '01/31/2008'
and Procedure_CD is not null
and Encounter_Units > 0
) as s2
ON a.Episode_Key = s2.Episode_Key
and p.ServiceProvider = s2.ServiceProvider
????---where Served = 1
group by a.Episode_Key, ag.Agreement_Type_Name, p.ServiceProvider, Served,
case when ag.Category IN ('ASMI', 'COOC', 'SPCL') then 'SMI'
when ag.Category = 'SEDC' then 'SED'
when ag.Category = 'ACCA' then 'SA'
when ag.Category like 'CGA%' then 'Gam'
End
View 2 Replies
View Related
Sep 13, 2005
I would like to save a query plan (estimated or actual)created in Query Analyzer -- paste it into a document,or simply print. It doesn't seem to be possible toselect and copy the Execution Plan window, and printingit creates microscopic gibberish which is a waste ofpaper. Is it possible to do this?Set showplan_text is of limited help for the SP I'mlooking at -- while analyzing the SP, it reads aheadand complains that a temp table created inside the SPdoesn't exist (yet) and exits. Using Ctrl-K to capturethe query plan allows the SP to complete, but saving theplan is the problem.Thanks,Jim Geissman
View 2 Replies
View Related
Mar 28, 2006
I have a SQL 2000 table containing 2 million rows of Trade data. Hereare some of the columns:[TradeId] INT IDENTITY(1,1) -- PK, non-clustered[LoadDate] DATETIME -- clustered index[TradeDate] DATETIME -- non-clustered index[Symbol] VARCHAR(10)[Account] VARCHAR(10)[Position] INTetc..I have a view which performs a join against a security master table (togather more security data). The purpose of the view is to return allthe rows where [TradeDate] is within the last trading days.The query against the view takes over around 30 minutes. When I viewthe query plan, it is not using the index on the [TradeDate] column butis instead using the clustered index on the [LoadDate] column... Theodd thing is, the [LoadDate] column is not used anywhere in the view!For testing purposes, I decided to do a straight SELECT against thetable (minus the joins) and that one ALSO uses the clustered index scanagainst a column not referenced anywhere in the query.There is a reason why I have not posted my WHERE clause until now. Thereason is that I am doing what I think is a very inefficient clause:WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())The function calculates the proper trade date based on the specifieddate (in this case, the current date). It is my understanding that thefunction will be called for all rows. (Which COULD explain theperformance issue...)However, this view has been around for ages and never before caused anysort of problems. The issue actually started the day after I had torecreate the table. (I had to recreate the table because some columnswhere added and others where renamed.)On a side note, if I replace the WHERE clause with a hard-coded date(as in 'WHERE [TradeDate] >= '20060324'), the query performs fine butSTILL uses the clustered index on the [LoadDate] column.
View 4 Replies
View Related
Jul 20, 2005
I'm hoping somebody can explain exactly what's going on here - I can'tfind it documented anywhere.Go to the Northwind database, and run the following SQL:create index IX_UnitPrice on [order details](unitprice)Now, turn on SHOWPLAN (either graphical or text, it doesn't matter),and run the following query:select * from [order details]where unitprice = 2Output:StmtText|--Index Seek(OBJECT: ([Northwind].[dbo].[OrderDetails].[IX_UnitPrice]), SEEK: ([OrderDetails].[UnitPrice]=Convert([@1])) ORDERED FORWARD)Now, alter the SARG slightly by making it a float:select unitprice from [order details]where unitprice = 2.000Output:StmtText|--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1003], [Expr1004],[Expr1005]))|--Compute Scalar(DEFINE: ([Expr1003]=Convert(Convert([@1]))-1.00,[Expr1004]=Convert(Convert([@1]))+1.00, [Expr1005]=If(Convert(Convert([@1]))-1.00=NULL) then 0 else 6|If(Convert(Convert([@1]))+1.00=NULL) then 0 else 10))| |--Constant Scan|--Index Seek(OBJECT: ([Northwind].[dbo].[OrderDetails].[IX_UnitPrice]), SEEK: ([Order Details].[UnitPrice] >[Expr1003] AND [Order Details].[UnitPrice] < [Expr1004]), WHERE:(Convert([Order Details].[UnitPrice])=Convert([@1])) ORDERED FORWARD)Right. I understand that in both cases the SARG datatype is differentfrom the column datatype (which is money), and that in the firstexample the SARG constant gets implicitly converted from int -> money(following the datatype hierarchy rules), and so the index can stillbe used.In the second example, the datatype hierarchy dictates that money islower than float, so the table column gets implicitly converted frommoney -> float, which strictly speaking disallows the use of the indexon that column.What I DON'T understand is what exactly all that gubbins about theexpressions (especially the definition of [Expr1005] is all about; howdoes that statement decide whether Expr1005 is going to be NULL, 6, or10?I'm soon going to be giving some worked tutorials on index selectionand use of Showplan to our developers, and being a bolshi lot they'rebound to want to know exactly what all that output means. I'd ratherbe able to tell them than to say I don't actually know!How about it someone?Thanks,Phil
View 4 Replies
View Related
Oct 23, 2006
I may just be completely missing something here but, when I view a query plan from a SQL statment that involves a join with a synonym I do not see any reference to the synonym or the underlying table referenced by it in the query plan? Any thoughts?
Thx!
View 5 Replies
View Related
