Wednesday, July 17, 2013

[T-SQL] check if index exists

[T-SQL] check if index exists


check if index exists

Posted: 17 Jul 2013 01:20 AM PDT

hii am creating script for non clustered ,i need to see of particular index on column on particular table exists or not

delete rows

Posted: 16 Jul 2013 05:36 AM PDT

hii need to move data to another table and then remove from old one ,which i needs to delete 30 millions of rows,i know its taking time but any idea i can do it faster like in a batch

How to get the first date of my table?

Posted: 16 Jul 2013 11:34 PM PDT

Hai friends, i ve the table like belowcreate table student ( class_atttend datetime, name varchar(20))insert into student values ('02-07-2013', 'A')insert into student values ('03-07-2013', 'A')insert into student values ('04-07-2013', 'A')insert into student values ('05-07-2013', 'A')insert into student values ('06-07-2013', 'A')insert into student values ('07-07-2013', 'A')insert into student values ('08-07-2013', 'A')my required output is from the firstdate of where was appeared on it?expecting output:===========02-07-2013

Select All Records from Temporary Table and Assign to VARCHAR?

Posted: 16 Jul 2013 10:17 PM PDT

Hello!I am new to TSQL and require some help with a problem I'm having. I have a stored procedure where I create a temporary table: Create Table #TempSourceOutput( SourceKey varchar(50), SourceID varchar(50), SourceComment varchar(max) )and I can successfully populate it using: Insert Into #TempSourceOutput (SourceKey, SourceID, SourceComment) Select @SourceKey, Cast(@SourceID as varchar), @SourceComment however, I would like to get all the values from this table and list them (along with other variables) in a varchar, resulting in something like this:Declare @TargetData varchar(max)Set @TargetData = "Target Project Name"Set @msg= 'Are you sure you wish to copy the following records to: ' + @TargetData + '?' + 'AA 12 Test Comment' 'AB 13 Another Test Comment' 'AC 14 And another one...'(where lines above AA, AB, AC are the result set of #TempSourceOutput - ie. columns SourceKey + SourceID + SourceComment)I would like to return it all as one complete varchar. Is it at all possible, and if so, how.Many thanks for your help in advance.Best wishesWinnie

Problem with FOR XML EXPLICIT

Posted: 16 Jul 2013 01:45 AM PDT

Hello everybody!I'm new to FOR XML...So here is my problem:I've got a query that returns the correct result regarding rows, nesting etc...[code="sql"] SELECT TAG, Parent, [Type!1!Type], [Type!1!TypeID], [Type!1!TypeName], [Type!1!TypeValue], [Property!2!Type], [Property!2!PropID], [Property!2!PropName], [Value!3!Type], [Value!3!ValID], [Value!3!ValName], [Value!3!ValValue] FROM ( -- get all Types from TBL_Value_Types SELECT DISTINCT 1 AS TAG, NULL AS Parent, 'Type' AS [Type!1!Type], T.TypeID AS [Type!1!TypeID], T.TypeName AS [Type!1!TypeName], T.Value AS [Type!1!TypeValue], T.DisplayOrder AS [Type!1!TypeDisplayOrder], NULL AS [Property!2!Type], NULL AS [Property!2!PropID], NULL AS [Property!2!PropName], NULL AS [Property!2!PropDisplayOrder], NULL AS [Value!3!Type], NULL AS [Value!3!ValID], NULL AS [Value!3!ValName], NULL AS [Value!3!ValValue], NULL AS [Value!3!ValDisplayOrder] FROM dbo.TBL_Value_Types AS T LEFT JOIN TBL_Value_Properties AS P ON P.TypeID_FK = T.TypeID LEFT JOIN TBL_Value_Property_Values AS V ON V.PropID_FK = P.PropID UNION -- get all Properties from TBL_Value_Properties SELECT 2 AS TAG, 1 AS Parent, NULL AS [Type!1!Type], T.TypeID, T.TypeName, T.DisplayOrder, T.Value, 'Property' AS [Property!2!Type], P.PropID, P.PropName, P.DisplayOrder, NULL AS [Value!3!Type], NULL AS [Value!3!ValID], NULL AS [Value!3!ValName], NULL AS [Value!3!ValValue], NULL AS [Value!3!ValDisplayOrder] FROM dbo.TBL_Value_Types AS T LEFT JOIN TBL_Value_Properties AS P ON P.TypeID_FK = T.TypeID LEFT JOIN TBL_Value_Property_Values AS V ON V.PropID_FK = P.PropID UNION -- get all Values from TBL_Value_Property_Values SELECT 3 AS TAG, 2 AS Parent, NULL AS [Type!1!Type], T.TypeID, T.TypeName, T.DisplayOrder, T.Value, NULL AS [Property!2!Type], P.PropID, P.PropName, P.DisplayOrder, 'Value' AS [Value!3!Type], V.ValID, V.ValName, V.ValValue, V.DisplayOrder FROM dbo.TBL_Value_Types AS T LEFT JOIN TBL_Value_Properties AS P ON P.TypeID_FK = T.TypeID LEFT JOIN TBL_Value_Property_Values AS V ON V.PropID_FK = P.PropID WHERE V.ValID IS NOT NULL ) AS TEMP ORDER BY [Type!1!TypeID], [Type!1!TypeDisplayOrder], [Property!2!PropDisplayOrder], [Value!3!ValDisplayOrder], [Type!1!Type] [/code]If I add "FOR XML AUTO" the result itself (meaning rows, nesting etc...) looks still fine, but it's not the desired XML-structure.If I add "FOR XML EXPLICIT" the result is missing several rows. After further investigation it seems that the last "Type" has lost all it's properties and values...Any comments are welcome!!!Thanks in advance....hsackmann

Unexpected Behaviour With SQL Server :,(

Posted: 16 Jul 2013 03:45 PM PDT

There is a SP which usually takes around 3 minutes to execute. Since last few days it is taking around 30-35 minutes.So we did Rebuilding of all the index and updating the statistics.After that we run the same SP (which USED to take 3 minutes), after rebuilding & Updating the statistics the SP executed relatively fast and it executed by taking some 3-4 minutes. After that there were no operations done on DB for next 5-6 hours. After 5-6 hours we again tried to run the SP, but it again took 30-35 minutes.We have observed a common pattern that when we Rebuild & update the statistics,the SP runs pretty quickly. But that is not feasible solution,that whenever we have to run the SP you Rebuild & Update the statistics.Moreover for fragmentation to come into picture, we need to do some insert/update/delete which we are not doing. Then WHY only after rebuild & Update statistics the SP runs smootly.

error

Posted: 16 Jul 2013 06:13 AM PDT

hiselect * from tableA with(nolock) ajoin tableB with(nolock) bon a.id = b.idwhere a.date > getdate()i am getting error incorrect syntax near a

No comments:

Post a Comment

Search This Blog