Thursday, July 18, 2013

[T-SQL] delete top 100

[T-SQL] delete top 100

delete top 100

Posted: 17 Jul 2013 05:56 AM PDT

himy query is giving me errodelete top(1000) from table a join table bon a.id = b.idand b.date < getdate()error: incorrect syntax near a.i want to delete top 1000 from table a

SP Date parameter default to GETDATE()?

Posted: 17 Jul 2013 10:34 AM PDT

I want my procedure to include an optional date parameter that defaults to the current date. Why does the following code show a Syntax error?[code="sql"]CREATE PROCEDURE sp_MyProcedure @Record_ID Varchar(12), @OtherKey_ID Int, @DateParameter DATE = GETDATE(), @Comments VARCHAR(MAX) = NULLAS ....[/code]

SPs inside BEGIN/ROLLBACK TRAN

Posted: 17 Jul 2013 10:20 AM PDT

When testing Stored Procedures, can you run something like[code="sql"]BEGIN TRANEXEC sp_deleteSomeRecords "Varchar Parameter", 999, NULL-- COMMIT TRAN-- ROLLBACK TRAN[/code]... to protect the data in case sometime goes wrong when the procedure itself contains BEGIN/COMMIT/ROLLBACK TRAN statements and a TRY/CATCH block?

Need help with Select Where clause using parameter equal blank or in a list

Posted: 17 Jul 2013 03:06 AM PDT

I have a stored procedure that passes in several parameters that can be blank or a list of selected filter values. For each parameter, I split the values and insert into their own temp tables. This all works fine. When I get to my Select statement, I want to select all records if the parameter that was passed is blank or filter the select if the parameter was not blank. Here is an example of what I am dfoing: of what I am doing:create procedure XYZ @param1 varchar(50), -- passing in 2 values @param2 varchar(50), -- passing in 2 values @param3 varchar(50) -- passing in a blank stringascreate table #Param1Values (Param1Value int) insert #Param1Values select * from SplitToInt(@param1)--Repeat above for other two parametersselect * from ABC where (@param1 = '' or (colA in (select Param1Value from #Param1Values)) and (@param2 = '' or (colB in (select Param2Value from #Param2Values)) and (@param3 = '' or (colC in (select Param3Value from #Param3Values))This all works, however, I found the timing for the query to fluctuate immensely when I alter the comparison of the second parameter. The query takes about 1:20 to run when coded as above. If I take out the "@param2 = '' or", the query runs in 11 seconds. I don't see this kind of adjustment if I take out the same portion of the first parameter. That only bring it down another 2 seconds. I have considered populating the temp table with all possible values, if the parameter is blank. The difference between that and just removing the whole second line(to simulate an All filter) is just 10 seconds. I also tried putting an index on the temp table, but that actually took longer.I do know that there are a lot more distinct values in colB then there are in the other two. Also, the primary table of the query does not have any indexes set up for any of the columns in the query. I haven't tried setting those up yet to see what kind of performance improvement I might get. Any ideas as to why there is such a big impact with this one column? Is there a better approach that I should be taking?Thanks.

Index and Table Size

Posted: 17 Jul 2013 01:36 AM PDT

Hello EveryoneHappy hump day to all.I am working on gather some stats for each table in a database. I am gathering row counts, which I already have. But my question is where can I find the data for the size of the table and the size of each index per each table? I like the way that one can see the index space, data space and row count for each particular table by selecting "Storage" under the properties of the table. Where may I find this information, so that I can select that data into another table for stats reporting?Thank you in advance for your assistance, suggestions and comments.Andrew SQLDBA

Query processor ran out of internal resources and could not produce a query plan

Posted: 17 Jul 2013 02:21 AM PDT

Hello,I'm having the following error when running a query :Msg 8623, Level 16, State 1, Line 1The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.Query (please see attachments for full query) :WITH SAWITH0 AS (select T73739."CONTACT_ID" as c1, T73739."PROSPECT_ID" as c2, T73739."ACCNT_ID" as c3, T73739."INTEGRATION_ID" as c4from "W_PARTY_PER_D" T73739where ( T73739."INTEGRATION_ID" in (29547 Integration_ids ))) select SAWITH0.c1 as c1, SAWITH0.c2 as c2, SAWITH0.c3 as c3, '0' as c4, '' as c5, '0' as c6, 1 as c7, 1 as c9, SAWITH0.c4 as c10from SAWITH0I managed to not get an error by reducing the number of IN items to 22000. I have attached the estimated query plan.I also managed to not get the error by removing the fixed values from the CTE select statement as shown below : select SAWITH0.c1 as c1, SAWITH0.c2 as c2, SAWITH0.c3 as c3, SAWITH0.c4 as c10from SAWITH0I have also attached the query plan. Query plan for select statement with IN list is attached as well.select T73739."CONTACT_ID" as c1, T73739."PROSPECT_ID" as c2, T73739."ACCNT_ID" as c3, T73739."INTEGRATION_ID" as c4from "W_PARTY_PER_D" T73739where ( T73739."INTEGRATION_ID" in (29547 Integration_ids ))Why am I having this behaviour ? I there some kind of limit at work here ? I know I haven't exceeded the IN limit.All related queries and estimated plans are attached.Kailash
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog