Transact SQL :: XML Not Using Proper Index?

Aug 26, 2015

I have two xml queries that take long: the 1st query takes about 5 minutes (returns 700 rows) and the 2nd query takes about 10 minutes (returns 4 rows). The total rows in the table is about 2 million. There are three secondary indexes: Property, Value and Path in addition to the clustered index on CardID and Primary XML index. Here is the table definition: 

CREATE TABLE [dbo].[Cards]
(
[CardId] [int] NOT NULL,
[Card] [xml] NOT NULL,
CONSTRAINT [PK_dbo_Cards_CardId] PRIMARY KEY CLUSTERED
([CardId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

[code]...

Looking at the execution plan, the query uses the Primary XML Index even if I add any of the secondary xml indexes. My question is why does not the optimizer use the Property 2ndary index instead of the Primary XML Index? Microsoft recommends that creating a Property index for Value() method of the xml datatype would work to provide a performance benefit. What would be another alternative to make the query run faster?

View 12 Replies


ADVERTISEMENT

Transact SQL :: Proper Use Of Cursor?

Jun 16, 2015

Am I using the cursor feature properly here?  -- of course there would be actual processing (replacing a while loop) going on and not simple print statements.

Declare @BadgeNumber varchar(20), @name varchar(100), @phone int, @status varchar(25)
Declare cursor Cursor For
Select jt.BadgeNumber, tj.Name, jt.Phone, zt.status
From employees jt
join employeestatus zt
On jt.id = zt.id

[code]....

View 6 Replies View Related

Transact SQL :: Query Not Displaying Proper Result Set

Jun 10, 2015

This syntax is not displaying verified OR timespentinclass accurately. Where did I miss syntax?

DECLARE
@studentid varchar(50)
SET @studentid = 'ALL'
declare
@count int,
@course varchar(100),

[Code] ....

View 11 Replies View Related

Transact SQL :: Index Rebuild MP

Oct 12, 2015

I would like to completely understand the difference between index rebuild Maintenace plan and the customized script.Maintenance Plan rebuilds every single index.It will take the long time as it checks every index.If we use a custom script as a job, it will rebuild the index which has fragmentation  >30%.So that, the job will not take much time.

View 11 Replies View Related

Transact SQL :: Difference Between Index And Primary Key

Aug 10, 2015

What is the difference between the Index and the Primary Key?

View 14 Replies View Related

Transact SQL :: Index Fragmentation For Identity Column?

Jun 26, 2015

In my database all columns have Identity Value as a PK.

Now today I check Index Fragmentation I saw that many cluster and Non cluster Index are avg.Fragmentation is around 99 % I thought that in Identity column record is always inserted at bottom so there is no fill factor assigned to it.

So in this case Do I need to set Fill factor for Cluster and Non Cluster Index?

If Yes Then For PK How much - 95 % or what? and same for Non cluster or It should around 85 to 90

View 4 Replies View Related

Transact SQL :: How To Determine Index Create Date

Nov 7, 2012

Is it possible to determine an index create date in SQL Server 2008 R2?  The index in question is not a primary key.

View 8 Replies View Related

Transact SQL :: Missing Index Feature Disabled?

Apr 23, 2015

I have an exact copy of a database on 2 instances of SQL server (dev and test - dev is restored from test).There's a view that I select top 1000 rows from that runs extremely slow on both instances (DEV and TEST) however on DEV the execution plan specifies that I'm missing an index however in TEST the execution plan does not specify that I'm missing an index.

View 3 Replies View Related

Transact SQL :: Tables With FK Column Without Non-clustered Index

Oct 22, 2014

I need to find out all the tables in database, which has FK columns and don’t have any Non-clustered index on them.

View 11 Replies View Related

Transact SQL :: Disadvantages Of Column Store Index

Nov 3, 2015

What are the disadvantages of columnstore index in Sql Server 2012

View 4 Replies View Related

Transact SQL :: Dropping Clustered Index On Primary Key Which Is In Replication?

Oct 2, 2015

I am trying to drop a primary key on column LID and then create a clustered index on a new identity column ID and then add the primary key back on the LID. I am not able to do so due the table being in replication. here is the error:

Cannot alter the table '' because it is being published for replication.

How do I get past the error and create the Clustered Index on ID column in both publisher and subscriber?

View 2 Replies View Related

Transact SQL :: Non Clustered Covered Index Not Utilizing In A Query Plan?

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

Transact SQL :: How Much Space Is Required By Index Rebuild Command For Each Database

Sep 24, 2015

I'm upgrading to SQL 2012 from 2008R2, while doing so i will be rebuilding all the indexes on all the database. In my previous environment while doing so, i got space related error in primary filegroup for insufficient space in the primary filegroup. Is there any rule of thumb about how much space is required by index rebuild command for each database, or is there a safe threshold for free space in the database?

View 9 Replies View Related

Transact SQL :: Index Maintenance / Defrag Fails On CDC Enabled Database - 2008 R2

Oct 18, 2012

We have a new database with cdc enabled on all of its tables.  This causes the index maintenance task to fail with following message:

"Executing the query "EXEC DBName.dbo.IndexDefrag_sp" failed with the following error:  "The unique index 'PK_TableName' on source table '[dbo].[TableName]' is used by Change Data Capture.  To alter or drop the index, you must first disable Change Data Capture on the table.  The transaction ended in the trigger. The batch has been aborted.".  Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" We would like to run the index maintenance without losing the cdc data.  We plan on installing SP2 on SQL Server 2008 R2 soon, would that solve the issue?  Disabling the cdc prior to index maintenance and then re-enabling back upon completion; would delete the data as I found in most discussions, but we would like to retain it. 

View 4 Replies View Related

Transact SQL :: Getting List Of Clustered Column-store Index In A Database In 2014?

Sep 17, 2015

How can we get the list of clustered columnstore index in a database in sql server 2014

View 3 Replies View Related

Transact SQL :: ORDER BY Clause Is Ignored When A Unique Index On Criteria Columns Exist

Sep 16, 2015

In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).

View 4 Replies View Related

Transact SQL :: Any Reason For Difference In Order Of Columns Between Index Of Constraint And Its Statistics Definition?

Sep 5, 2015

I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.

As per definition,

stats_column_id
int

1-based ordinal within set of stats columns

This script duplicates this for me.

BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,

[Code] ....

The result I get is this:

object_id       stats_name                                     
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID,  cpID,  ipuID,

and

object_id      index_name                                     
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID,  ipuID,  cpID,

Also a query I used to discover this in my db is:

WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc

[Code] .....

View 6 Replies View Related

Transact SQL :: Create Index On Temp Table To Reduce Run Time Of Update Query

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

Difference Between Index Seek && Index Scan && Index Lookup Operations?

Oct 20, 2006

please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio

thank you in advance

View 3 Replies View Related

SQL And Proper Concatenation Within VB

Jun 9, 2008

Hi all - I have posted inquiries on this rather vexing issue before, so I apologize in advance for revisting this. I am trying to create the code to add the parameters for two CheckBoxLists together. One CheckBoxList allows users to choose a group of Customers by Area Code, the other "CBL" allows users to select Customers by a type of Category that these Customers are grouped into. When a user selects Customers via one or the other CBL, I have no problems. If, however, the user wants to get all the Customers from one or more Area Codes who ALSO may or may not be members of one or more Categories; I have had trouble trying to create the proper SQL. What I have so far:Protected Sub btn_CustomerSearchCombined_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombined.Click        Dim CSC_SqlString As String = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Category.Category FROM Customers INNER JOIN Category ON Customers.CategoryID = Category.CategoryID WHERE "        Dim ACItem As ListItem        Dim CATItem As ListItem        For Each ACItem In cbl_CustomersearchAREA.Items            If ACItem.Selected Then                CSC_SqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR "            End If        Next        CSC_SqlString &= "' AND " <-- this is the heart of my problem, I believe        For Each CATItem In cbl_CustomersearchCAT.Items            If CATItem.Selected Then                CSC_SqlString &= "Customers.CategoryID = '" & CATItem.Value & "' OR "            End If        Next        CSC_SqlString = Left(CSC_SqlString, Len(CSC_SqlString) - 4)        CSC_SqlString &= "ORDER By Categories.Category"        sql_CustomersearchGrid.SelectCommand = CSC_SqlString    End SubAny help on this is much appreciated, many thanks -- 

View 5 Replies View Related

T-SQL Proper Function

May 23, 2005

T-SQL offers UPPER and LOWER functions for formatting text strings. Is there a PROPER function? Thanks.

View 1 Replies View Related

What Are The Proper Procedures?

Sep 18, 2000

What are the proper procedures to move a SQL 6.5 to another 6.5 (new box)

I need to move everything including stored procedures?

Any tips would be helpful.

Thanks,
Jason

View 1 Replies View Related

Proper Way To Us And With If Clause

Apr 3, 2006

I have a tsql where I need to do a patindex on a variable and check if a record exists to meet the where clause for a IF statement below. What am I doing wrong

declare @l_orderid int
set @l_orderid = 18
declare @l_SIGShort varchar(20)
set @l_SIGShort = '~KOP~'
-- KOP Orders
print patindex ( '%~KOP~%', @l_SIGShort)
if patindex ( '%~KOP~%', @l_SIGShort) <> 0 and (if exists (select * from orderoptions
where orderid = @l_orderid and ordertype = 'KOP'))

View 2 Replies View Related

Transact SQL :: Difference Between Cluster And Non-cluster Index

Jul 26, 2015

Wanted to know about difference between cluster and non cluster index with example.

When to use cluster index and non cluster index .

View 3 Replies View Related

Going From SQL 7.0 Beta To SQL 7.0 Proper - Problem ...

Jan 14, 1999

Greetings, I seem to be getting a problem during installing SQL 7.0 over the SQL 7.0 beta. It tells me that there are ODBC components need to be upgraded and they are read only ... I can find no way of changing this.

Alternatively if I remove the beta and then install the proper version, will all the old db created under the beta still be recognised ?

Kris Klasen

Act. Manager, Data Warehouse Project
Information Management Branch
Department of Education

E-mail: Kris.Klasen@Central.Tased.Edu.Au
http://www.tased.edu.au
Tel: 03 6233 6994
Fax: 03 6233 6969
Mobile: 0419 549237
73 Murray Street
2nd Floor
Hobart 7000
Tasmania
Australia

View 1 Replies View Related

Proper SQL Backup Procedure

Mar 18, 1999

Greets!

I have been told that simply stopping the SQL server service and backing up the data directory is all I have to do to do a backup of my data. Is this accurate?

Thanks,
Jimmy Ipock

View 2 Replies View Related

Proper Names Function

Jul 12, 2004

Hello: a nice simple question (I hope). Is there a MS SQL equivilant to PROPER (string) which would return "Fred Bloggs" from "FRED BLOGGS" and equally from "FrEd bLoggs" ? I cant find such ....


Gerry

View 3 Replies View Related

Proper Place For Sub Select?

Feb 14, 2013

I have a very simple query that gets a field to use as constraints in another query.

Code:

SELECT A.RIN
FROM apcType T
INNER JOIN apcAttribute a on A.T = T.RIN
WHERE T.Name like '%Sales'

The results of the first query are used in the following query where it is bolded and marked with and <<<<========

Code:
SELECT AP.Arg2, AP.Arg3, M.Parcel,
M.Serial, M.Name, M.Acres, M.District,
V.YearBuilt, V.Code, V.Size,
(SELECT SUM(V1.Acres)
FROM TRValue V1
WHERE V1.Year = V.Year and V1.Parcel = V.Parcel and
SUBSTRING(V1.Code, 1, 1) = 'L'

[code]....

My question is How can I fold the first query into the second?

View 1 Replies View Related

Proper Way Of Linking Tables?

May 2, 2015

I am no stranger to Databases, I worked a lot with MySQL but never really cared about proper DB design as long as it worked. Now I am playing with SQL in a ASP.NET project and want to get things done the right way.Let's say I have a Movies database. My movies can have multiple genres so I set my tables up like this:

[Movies]
MovieID
MovieName
MovieRelease

[code]....

Is this the proper way of doing things? The problem with this is when I want to enter a record manually I have to know the ID of the movie and the ID of the Genres of the movie. And what about naming conventions? By default the identifier is always Id, from my MySQL experience I liked naming it like the table, same goes with other columns. This is my T-SQL code for above tables in VS-2013.

CREATE TABLE [dbo].[Movies] (
[MovieID] INT IDENTITY (1, 1) NOT NULL,
[MovieName] VARCHAR (50) NOT NULL,
[MovieRelease] NUMERIC (18) NOT NULL,
CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED ([MovieID] ASC)

[code]....

View 2 Replies View Related

Convert To Proper Date

Apr 16, 2007

I read some questions where questioners ask "Sometimes client gives data where dates are expressed as float or integer values.
How do I find maximum date?".

Ex
March 02, 2006 can be expressed as
02032006.0
020306
2032006
20306
020306.0000
2032006
Assuming the values are expressed in dmy format

The possible way is convert that value into proper date so that all types of date related calculations can be done
Create function proper_date (@date_val varchar(25))
returns datetime
as
Begin
Select @date_val=
case when @date_val like '%.0%' then substring(@date_val,1,charindex('.',@date_val)-1)
else @date_val
end
return
cast(
case
when @date_val like '%[a-zA-Z-/]%' then case when ISDATE(@date_val)=1 then @date_val else NULL end
when len(@date_val)=8 then right(@date_val,4)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
when len(@date_val)=7 then right(@date_val,4)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
when len(@date_val)=6 then
case when right(@date_val,2)<50 then '20'
else '19'
end
+right(@date_val,2)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
when len(@date_val)=5 then
case when right(@date_val,2)<50 then '20'
else '19'
end
+right(@date_val,2)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
else
case when ISDATE(@date_val)=1 then @date_val else NULL end
end
as datetime
)
End

This function will convert them into proper date
select
dbo.proper_date('02032006.0') as proper_date,
dbo.proper_date('020306.000') as proper_date,
dbo.proper_date('02032006') as proper_date,
dbo.proper_date('020306') as proper_date,
dbo.proper_date('20306') as proper_date,
dbo.proper_date('020306') as proper_date

Apart from converting integer or float values to date, it will also convert date strings to date
Select
dbo.proper_date('March 2, 2006') as proper_date,
dbo.proper_date('2 Mar, 2006') as proper_date,
dbo.proper_date('2006 Mar 2') as proper_date,
dbo.proper_date('2-Mar-2006') as proper_date,
dbo.proper_date('3/02/2006') as proper_date,
dbo.proper_date('02-03-2006') as proper_date,
dbo.proper_date('2006/03/02') as proper_date,
dbo.proper_date('March 2006') as proper_date,
dbo.proper_date('2 Mar 2006') as proper_date


Madhivanan

Failing to plan is Planning to fail

View 8 Replies View Related

Proper Way To Return SCOPE_IDENTITY From SP

Mar 31, 2008

What is the proper way to return the identity of a newly inserted row from a stored procedure? Using a return value or a select statement? (I guess as an output parameter should also be considered...) As in

RETURN SCOPE_IDENTITY()

or

SELECT SCOPE_IDENTITY()

What are the pros/cons of using one approach over the other?

- Jason

View 4 Replies View Related

Proper Format Of BufferTempStoragePath

Dec 11, 2006

Hello, I have one package that seems to have continuous problems with memory. As of right now it loads a little over 1 million records. I tried leveraging the property, BufferTempStoragePath, but I don't seem to have the right path name. What sort of path do you put there? File? Folder? If it is a file, what sort of file should it be... text, dat, xml? If someone could point me in the right direction it would be greatly appreciated. Thanks.

PS: Below are the error messages I am getting:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

[DTS.Pipeline] Error: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.

View 3 Replies View Related

Proper Use Of Event Notifications

Apr 19, 2006

Hi.

I'm developing an app that uses Service Broker queues to allow a customer to create "events" that fire using a timer or a query notification. When these events fire, a message is sent to a Service Broker queue for processing. Because there is much managed code involved in processing these messages, I decided to use the External Activator application and an Event Notification to process these messages. My question is "what is the difference between using the External Activator application to launch another application (which simply RECEIVEs a message from the target queue and processes it) and creating a windows service that simply monitors the target queue (with a WAITFOR = -1 clause) and processes it?"

I guess I'm not sure how using the QUEUE_ACTIVATION Event Notification is really helping me.

Thanks,

Chris

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved