Hi
Has anyone heard of MS SQL Server 7 dropping indexes?
I had created an index on a table. The next day
the index had disappeared. Has anyone expirienced such
a problem?
Thanks in advance
Winston
Dear All,
i'm planning to drop all the non clustered indexes (as they are not congigured well) on production database, and run the latest script to create fresh non clustered indexes on specific columns.
now my doubts are
1)will the replication affect with dropping and recreating of indexes?
2)query to drop all the non clustered indexes on that database....
can i use the query delete from sysindexes where indid>1
will the query works for me to drop all the non clustered indexes?
3)is it necessary to generate a snapshot again after creating the new indexes? or can i drop and run at subscriber also?
please guide me in this regard
Arnav
Even you learn 1%, Learn it with 100% confidence.
Looking for suggestions.
I have a database that is giving me a bad Index error. When I go to drop the necessary Index it is telling me that it either does not exist or cannot be dropped. However when I try to build that index, it tells me one already exists.
Is there any way to drop all of the indexes or at the very least see what the indexes are? This particular database is using 2005 Express.
Any help would be great!
Shawn
SQL 7 created by default a clustered index on my primary key field. I would like to drop this index and recreate it on another field, but it is not allowing me. Error message states: "An explicit DROP INDEX is not allowed... It is being used for PRIMARY KEY CONSTRAINT enforcement." Can anybody advise how I can solve this? TIA
View 1 Replies View RelatedI would like to know if there is a way to drop/ disenable all the indexes in a maintenance plan?
Or is it better to write scripts for dropping indexes and recreatig the same?
Purpose: Need to drop indexes(not the Primary key) before loading data and recreate the same after loading
Looking forward for suggestions/Solutions!
Thanks,
Janani
I need to add syntax to a stored procedure to check for the existence of a specific indexes on a table before dropping it. If they do not exist I need it to NOT through an error message. Performing this on a table is relatively simple:
if exists (select * from sysobjects where id = object_id(N'[dbo].[TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLENAME]
Since indexes are not represented in sysobjects how can I do this?
This is for SQL 2005 and I know how to right click an index and do a "script index as create to new query window"
Basically, the one of the Microsoft scripts or views will tell us that we have 100+ indexes that exist for a database but that they are not being used by SQL server.
I will probably take them "offline" for a while and then drop them later on.
Before dropping them, I would like to be able to recreate them within minutes if system performance is degraded after this happens.
I was working on a script to pull this info out of the sys. tables like below but was wondering if anyone already has a script or an easier way to do this. (...and I don't want to right click 100 indexes within 137 tables and script to a new window and then compile a big script)
SELECT
'Create ' +
i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS +
' Index [' +
i.name +
'] ON ' +
t.name +
CHAR(10) +
c.name +
' ASC'
FROM sys.index_columns ic
Join sys.indexes i ON i.index_id = ic.index_id
JOIN sys.tables t ON ic.object_id = t.object_id
Join sys.columns c ON ic.column_id = c.column_id
WHERE
i.name = 'IX_Sellers_StatusID' and
c.object_id = t.object_id and
i.object_id = ic.object_id
Thanks,
Brian
Hi all,
I have a huge table 170 Gb of size. On that table we have 10 indexes
of around 12 GB in size.
The application is designed such that it bulk inserts the file in to
sql server. But , often we are getting time outs and some latching
isssues ( as can be seen in activity monitor).
So, will this be a good idea of dropping those indexes and then
recreating them again for better performance.
1) Its SQL 2005 Standard Edition SP1
2) Databases are in SIMPLE Recovery mode.
3) Database is not OLTP.
Thanks.
//N
I am using Access 2000 to link tables from SQL Server. The tables are linked using ODBC DSN`s. I am running a query to return some rows to Access, but once I have returned the rows, I want to drop the linked table connection to SQL Server, because it is locking a table on the server. I think I need to write a bit of VBA code, but I don`t know where to start. Any ideas would be greatly appreciated. Thanks.
View 1 Replies View RelatedI have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
Are there any issues with dropping registration of a SQL 6.5 database and then registering it again. I think this is what I am going to have to do because a consultant we are using decided to change the security from standard to Integrated. Now we are getting the following error when we are in the SQL Enterprise Manager and trying to open TAP:
A connection could not be established to TAP[SQL Server]Login failed:Reason:Not associated with trusted SQL Server connection.
Thanks for any help
I have managed to successfully connect to another SQL Server instance using a linked server.
I can then do my select statement like this: select * from [servernameinstancename].[databasename].[databaseowner].[tablename]
I just wanted to know how I can drop the database on the other instance?I have tried doing: DROP DATABASE [servernameinstancename].[databasename].
There are procs to add linked servers, and procs to add and delete linkedserver logins, but I dont see a proc to delete the actual linked server definition. And yet EM will drop the definition of a linked server. So the obvious question is "How do you code the dropping of a linked server?
Thanks!
Michael
I have an asp.net 4.5 webpage that has a detailsview on it that has a bound field that points to a SQL Server 2012 datetime field (CompDate). This field is supposed to capture date AND time of completion of a record (this is a data gathering project).
<asp:BoundField DataField="CompDate" HeaderText="CompDate" SortExpression="CompDate" Visible="False" />
The code that inserts the datetime:
If e.NewValues.Item("Complete") = True Then
Dim dt As DateTime = Now
e.NewValues("CompDate") = dt
e.NewValues("UNAME") = Membership.GetUser.ToString
End If
Everything works great, except when the captured date/time is sent to sql server, the time portion is getting dropped and only zeros are getting stored in the time portion.
Results:
CompleteUNameCompDate
1 CarlR 2015-08-04 00:00:00
I've tried different formatting options and converting to string..
All,
I've a VB6 application that writes data to a SQL Server database using ADODB in the VB6 code. My market has been the United States. My application is written for the English language.
I've a new user in Germany. Unfortunately, every number that contains a decimal is now stored in the SQL Server database with the decimal dropped. For example, 29.15 gets stored as 2915. I've done some searching and found the culprit is probably the OS Regional Language setting. The Germany user is using German. So it seems ADODB is expressing "29.15" as "29,15", and SQL Server is having a fit when ADODB is trying to write to the database.
My client is reluctant to changing all their user profile language settings to German (or the other option, changing their German language setting decimal symbol option to "."). They fear other applications may be affected.
Can anyone suggest a solution on my software end? Does ADODB have some options of which I'm unaware that will ignore the regional language setting and express decimal-containing numbers simply as "."? Or is there a setting in SQL Server to compensate?
I appreciate all and any help. Thanx!
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
I have a query that produces unique rows. However, some of the unique rows have the column called testname that has the same test listed more than once. All I want to do is drop the older testname and keep the testdate column.
Select
Distinct
TestID,
TestDate,
TestName
From third.test
I want to keep testdate in the query....guessing I need to put in to a temp table then drop the oldest one somehow by doing a subquery using Select Max....
I am finding it difficult to find an example that allows for insertion of additional rows into a table, without dropping the table I'm inserting into. Or inserting specific values. Like this example..
[URL] ....
I have 6 table I am formatting the data to conform to the final table as I'm inserting it into, but none of these examples gives me the example needed. I am using SQL 2012.
<code>
SELECT
CONVERT(VARCHAR(50),[FName]) + ' ' + CONVERT(VARCHAR(50),[LName]) AS [CustName]
,CAST('ALARMCOM' as nvarchar(8)) as VendorName
,CONVERT(VARCHAR(25),[CUSTOMER_CS_ACCOUNT_NUMBER]) AS [Cust_ID]
,CONVERT(VARCHAR(40),[Charge_Description])as [ChargeType]
,CASE
[Code] ....
1. I have a simple JOIN statement between A and B, e.g. Cities A JOIN Countries B:
SELECT A.City_Name, B.Country_Code, B.Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';
That statement works absolutely fine, very fast (less than a second) and returns me 2 records
2. I need to replace Country Area column with 1 for Europe and 0 for all the rest. I implement so in the following way:
SELECT A.City_Name, B.Country_Code, CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area
FROM Cities A
JOIN Countries B
ON B.Country_Id = A.Country_Id
WHERE B.Country_Type='ABC';
Now to get the same two records it takes 03:55 minutes (!)
I have looked into Estimated Execution Plan, but couldn't spot any difference - all straight forward.
It is SQL 2012 SP1 with compatibility level set to 110
I want to create and drop the global temporary table in same statement.
I am using below command but I am getting below error
Msg 2714, Level 16, State 6, Line 11
There is already an object named '##Staging_Temp' in the database.
if object_id('Datastaging..##Staging_Temp') is not null
begin
drop table ##Staging_Temp
end
CREATE TABLE ##Staging_Temp(
[COL001] [varchar](4000) NULL,
[Code] ....
I have been given a SQL server database to look after (a steep learning curve).
A large (4.5 gigabyte) table has two indexes:
One is Surname and another is Surname and PostCode. Neither of the indexes are unique or clustered.
Is there any reason for supporting these two indexes which are 71Mb and 101Mb respectively?
I would appreciate any advice on why these two indexes may exist.
Thanks
jonathan
I am tasked with maintaining a large database still on SQL Server 7.0.Performance is an issue not surprisingly and I've targeted two ratherlarge tables to see what I can do with the indexes.The 2 tables are described as follows:MatterConflicts:Fields: MatterConflicts varchar(16), Matters varchar(16), HitMattersvarchar(16), IsInclude varchar(1)Index: MatterConflictsMatterConflictHits:Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),ColumnLabel varchar(40), Hit varchar(100)Index: MatterConflictHitsNow MatterConflicts row count is approaching 500K andMatterConflictHits is approaching 1 Million rows. There are only oneindex on each table, each for the table's primary key. The Mattersfield in MatterConflicts table joins back with a table that usersaccess directly.Question is, would it be beneficial to add, or modify the existingindexes for these tables to include both the primary and foreign keys,as well as an additional field?Doesn't seem to be to be very beneficial to have an index that onlyincludes the primary key. So for example I'm thinking of creating anindex for MatterConflicts that includes the fields: MatterConflicts,Matters, and HitMatters.Thoughts? Suggestions? Thanks...
View 22 Replies View RelatedWhat is Indexes in SQL Server? Where we use ?? How to use?
I need little explanation about this topic with any sample code.
Thanks in advance.
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
What are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?
There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately
Hi All.
Our inhouse app used to run on a SQL2000, but we've recently moved it to 2005.
The move was done by way of backup and restore (it was on a whole new server).
After the move, an odd problem showed up: once in a while, the server seems incapable of finding/using its indexes: everything starts working slowly, until I run a maintenance plan that rebuilds its indexes etc.
In the database/server all relevant options seem to be ok (auto update statictiscs etc.), and my conclusion that it doesn't use its indexes comes from the fact that:
* it gives the results from certain select statements in a totally different order (although the set of rows is the same),
* performance is (all of a sudden) very slow (seconds turning to minutes!!)
This happens:
* at least after a reboot of the server
* sometimes just in the middle of the day
The only way I've found to solve the matter, is by running the maintenance plan to rebuild the indexes, but sometimes this only seems to work the second time.
Does anybody share this experience, or know what to do about it?
Thanks,
Nick.
hi, if exists (select * from sysobjects where id = object_id('dbo.MRDD_FINAL') and sysstat & 0xf = 3)
drop table dbo.MRDD_FINAL
This code was generated when I used the create a script to build a table from an existing table.
is there a way to check if a a table contains data or not,
The whole idea is to check if table A contains data, I need to truncate the table,otherwise I do nothing...
regards
Ali
I wonder whether there's documentation about inline syntax for indexes for SQL Server 2014?
CREATE TABLE Consumer
(
Account nvarchar(20) null,
Consumption float null,
INDEX IX_Consumer_Account NONCLUSTURED (Account)
);
trying to create indexes on two tables:
SF_Affiliate_Customer
SF_Affiliate_Customer_Account
on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..
SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt]
FROM [dbo].[SF_Affiliate_Customer] C
WHERE
( [afflt_intrnl_cust_ind] = 'N'
AND [afflt_empl_ind] = 'N'
AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA')
)AND
[code].....
I have a scenario where I have 3 columns and all 3 of them are used in the where clauses of simple queries or ones having joins .
TABLE(
Column1 int
FLAG1 bit
FLAG2 bit
)
Sample queries :
Select * from TABLE where FLAG1 =1 and FLAG2 =0
(Any combination of these flags)
Select * from TABLE inner join SOMEOTHERTABLE on
TABLE.Column1 = SOMEOTHERTABLE .Column1
where FLAG1 =1 and FLAG2 =0
( any join and combination of flags)
Questions :
What would be the best nonclustered index strategy :
Column1 as the index key including FLAG1 and FLAG2
or
Column1,FLAG1 and FLAG2 in the index key
Points to note :
The queries are part of an ETL process and are used to track new records vs old records. The Flags switch states within the same job . So if we are creating an index on all 3 columns, the index has to be reorganized more than once based on the flag states. If we keep them in the include list , then its only about updating the leaf data with the latest flag values.
On the other hand, an index on all 3 columns will result in an index Seek alone , where as for the included list , there will be an index seek and a predicate .
Does the predicate cause more overhead than reorganizing the index or is it the opposite ?
This application runs on a SQL Server 2008 R2 database.This application receives messages from an integration module. It has a core table: Table-A. Each message is inserted as 1 row into Table-A. Then when it is processed, that row in Table-A is updated.
There are two environments which are both connected to the same integration. So in both environments, Table-A has exactly the same amount of records inserted and updated. In both environments Table-A has around 80 million rows, with an extra 150,000 rows being inserted and then updated every day.Table-A has 8 indexes. For some reason unknown to me, the 8 indexes fragment really quickly in one environment but not in the other.
e.g. In Environment-1 the index fragmentation ranges from 0 - 19% and this environment has not been re-indexed for over 2 months.BUT a reindex was performed in Environment-2 and only 2 days later the index fragmentation ranges from 72 - 99.93%!
Our DBA has confirmed the re-index in Environment-2 completed successfully and has shown stats before and after the reindex to show that the 8 indexes for Table-A in Environment-2 went down to 0% fragmentation.
My question is, how can the indexes in Environment-2 fragment so much more quickly than the indexes in Environment-1? Both environments are on exactly the same hardware and have exactly the same inbound messages. The database on Environment-1 is actually a clone from Environment-2. The only known differences between the 2 databases is Environment-1 is STANDARD edition - SQL Server 2008 R2 (SP2) whereas Environment-2 is ENTERPRISE edition - SQL Server 2008 R2 (SP1). Could this difference be due to the Service Pack levels or even because one is STANDARD and the other ENTERPRISE?
This is what I have checked so far:
1) In both Environments all 8 indexes have "Set Fill Factor" unchecked and "Automatically recompute statistics", "Use row locks...", "Use page locks..." checked.
2) The "Index Usage Statistics" report in both Environments shows a similar amount of #UserUpdates and #UserScans
I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
[code]....
How do i find indexes of the columns of all the tables of thedatbase...........most importantly in SQL server 2000Thanks a lot
View 3 Replies View Related