June 29, 2011 at 12:18 am
I want delete non clustered index on table but it is not hope.
drop index NonClust_anamoly, sysname,> on dbo.Tel_bincard_detail
error
Msg 159, Level 15, State 1, Line 1
Must specify the table name and index name for the DROP INDEX statement.
this sql statement not working, please tell me what was reason?
Schematable_nameindex_namecolumn_nametype_desc
dboTel_Bincard_DetailNonClust_anamoly, sysname,>ExtNONCLUSTERED
dboTel_Bincard_DetailNonClust_anamoly, sysname,>Remove_DateNONCLUSTERED
dboTel_Bincard_DetailNonClust_anamoly, sysname,>Tel_IdNONCLUSTERED
dboTel_Bincard_DetailNonClust_anamoly, sysname,>ExtNONCLUSTERED
dboTel_Bincard_DetailNonClust_anamoly, sysname,>SiteNONCLUSTERED
dboTEL_TXT_LI_STNonClust_TXT_Li_st, sysname,>SITENONCLUSTERED
dboTEL_TXT_LI_STNonClust_TXT_Li_st, sysname,>PLANTNONCLUSTERED
Thanks
ananda
June 29, 2011 at 12:50 am
It looks like someone created those indexes using the Management Studio templates and included the data type definition my mistake! You will need to enclose the index name in square brackets to delete it because it includes spaces and odd characters:
DROP INDEX [NonClust_anamoly, sysname,>] on dbo.Tel_bincard_detail
You should rename the remaining indexes to remove the ", sysname,>" on the end of each, and educate the person responsible.
June 29, 2011 at 12:56 am
Yes, thank you for reply...
I got it delete this index as below statement
drop index [NonClust_anamoly, sysname,>] on dbo. tel_bincard_detail
Thanks
ananda
June 29, 2011 at 1:52 am
hi SQLkiwi,
I have faced problem for fetching data long time around 56 seconds and application(asp web) getting time out expired error.
1.this is main table
select * from Tel_bincard_detail
(15366 row(s) affected) for 1 seconds, this table there is no issue
2. this is view
SELECT Site, 'IT' AS PLANT, Ext, EName AS NAME, Location
FROM Tel_Bincard_Detail AS bin
WHERE (Remove_Date IS NULL) AND (NOT EXISTS
(SELECT bin.Ext
FROM TEL_TXT_LI_ST AS st
WHERE (SITE = bin.Site) AND (A = bin.Ext)))
25 records, for 58 seconds
after that i executed as below steps
3. alter index PK_Tel_Bincard_Detail on Tel_Bincard_Detail rebuild
again ran above view, and fetching records 25 records, for 57 seconds
but there is no help for after rebuild the index
4. As per actual execution plan, they says need to update statistics
use database name
exec update_stats
after that no changes , fetching records 25 records, for 57 seconds
please tell me after executed update_stats still showing waring for update stats.
I have attached actual execution plan, please tell me how to resolve this issues?
Thanks
June 29, 2011 at 2:32 am
This should help:
USE [TELEDATA]
GO
-- Allow SQL Server to create missing statistics automatically
ALTER DATABASE TELEDATA SET AUTO_CREATE_STATISTICS ON;
GO
-- Index to help the specific query below
CREATE NONCLUSTERED INDEX [IX dbo.TEL_TXT_LI_ST site (a)]
ON dbo.TEL_TXT_LI_ST (SITE)
INCLUDE (A);
GO
-- Rewritten query
SELECT
bin.Site,
'IT' AS PLANT,
bin.Ext,
bin.EName AS NAME,
bin.Location
FROM dbo.Tel_Bincard_Detail AS bin
WHERE
bin.Remove_Date IS NULL
AND NOT EXISTS
(
SELECT
1
FROM dbo.TEL_TXT_LI_ST AS st
WHERE
st.SITE = bin.Site
AND CONVERT(INTEGER, st.A) = bin.Ext
);
June 29, 2011 at 3:01 am
Hi SQLkiwi,
Wonderful and Greate reply...
what i have done?
1. ALTER DATABASE TELEDATA SET AUTO_CREATE_STATISTICS ON;
2. CREATE NONCLUSTERED INDEX [IX dbo.TEL_TXT_LI_ST site (a)]
ON dbo.TEL_TXT_LI_ST (SITE)
INCLUDE (A);
GO
After that data feteching time 00:00:00, the existing query also 00 seconds and your query also 00 seconds.
By default AUTO_CREATE_STATISTICS ON in SQL 2008 version, please tell me, why it was off?
Thanks a lots.
ananda
June 29, 2011 at 3:26 am
ananda.murugesan (6/29/2011)
By default AUTO_CREATE_STATISTICS ON in SQL 2008 version, please tell me, why it was off?
No idea - it seems likely it was done manually...?
June 29, 2011 at 3:34 am
Ok, I understood .
Thanks you very mouch for Help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply