I used the option "Script Table" --> "Create to" in SQL Server Management Studio Express for a table with secondary index (like IX_IndexName), but in the .sql script file there is only the instruction for the primary key and not for the secondary (I expected something like CREATE INDEX). What can I do?
View 3 RepliesI have a database around 500 GB. right now the database have only one data file and one log, it has only one filegroup also.all the indexes and table are placed in Primary Filegroup . we are going to separate them. the planing is to move all the indexes to Secondary filegroup and all the table will be in Primary filegroup.But there will be a problem while implementing it because there are around 600 tables and each table have at least 2 non-clustered index , so is there any way to move all the index to Secondary Filegroup.
View 3 Replies View Relatedi have created a new login in primary server and provided dbowner permission to primary db.how do i transfer this login to secondary server and assign the same permission to secondary db ?
View 2 Replies View RelatedHello
We have set up Log shipping between Primary and Secondary DB. The secondary DB is right now option: Standby/Read-Only. I can not take Backup of Secondary DB now.
Shall we disable Log shipping and change the DB Option to Multi-user mode and take backup? or any different method, without disabling log shipping?
please advice. Thanks in advance.
Jay
Do we have any way to insert,update,delete data from one table and update the changes onto second table. Also, while updating records into second table, can the data be encrypted.
I tried using view and it can insert, update, delete without any issues. But if i tried to encrypt any fields after inserting data into view, I am unable to do it.
For ex:
Table A:
EMPID LNAME FNAME
1 ABC DEF
2 DCE SKL
3 CKL DOP
Expecting output: by scrambling Lname FILED
Table B - vIEW TABLE
EMPID LNAME FNAME
1 CBD DEF
2 ECD SKL
3 LKC DOP
Tried below methods
CREATE Tableb_vw ON TableB
Instead of Insert
AS
Begin
update TableA
set Lname = (
--UserName = 'User' + substring(convert(varchar(32), UsersTrID), 1, 8)
SELECT REPLACE(LEFT(Lname, 2), '''', 'Z') AS LNAME)
FROM TableA
end
What I would like to get:
1. Can we update base tables and encrypt second table data while inserting or updating data
2. If not supported using base tables, can we do using views to encrypt view data [Some fields]
I was hoping for some information regarding table triggers. We have databases that are part of an AlwaysOn availability group on SQL Server 2012. Some of the tables have table triggers defined. There are both types of triggers, INSTEAD OF and AFTER INSERT, etc.
From my understanding of how secondary read-only replicas work, I am assuming that these triggers have no impact at all when the database is a read-only secondary replica and they only fire when the database is the primary read-write database.
However, after doing a search on the internet, I have not been able to find a definitive source of information.
I 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] ....
FYI: I'm using SQL2005 on a windows 2003 server.
So, I've written an Instead of Trigger to update a foreign key field based on information in another field of the same record.
To add some error handling to the process I updated the Trigger to insert any records that don't have legitimate foreign keys into a second table.
This process works great when I test it by just adding a record using the table view in the SQL Management Studio or through a query run in the query browser.
However, when a record is added via an ODBC connection I get foreign key constraint errors and records are not added to the second table. If the foreign key is legit the record is added and the part of trigger that updates that keyed field executes just fine.
Is anyone aware of this issue? Is there a way around it?
I found the following MSKB article but I'm not sure if it applies to my situation:
http://support.microsoft.com/kb/304096
Here's my current code, if that track the problem in anyway:
Code:
ALTER TRIGGER UpdateTicketID
ON Email
Instead of INSERT
AS
IF ((Select charindex('{', [subject]) FROM Inserted) = 0)
BEGIN
INSERT INTO
BadEmail ([Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLInk, SendTo, Cc, ContactID)
Select
[Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID
From
Inserted
END
ELSE IF ((Select substring([subject], charindex('{', [subject])+1, (charindex('}', [subject]) - charindex('{', [subject]))-1) From Inserted) NOT In (Select TicketID From Ticket))
BEGIN
INSERT INTO
BadEmail ([Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLInk, SendTo, Cc, ContactID)
Select
[Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID
From
Inserted
END
ELSE
BEGIN
INSERT INTO
Email ([Subject], Sender, Body, ticketID, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID)
Select
[Subject]
, Sender
, Body
, substring([subject], charindex('{', [subject])+1, (charindex('}', [subject]) - charindex('{', [subject]))-1)
, EntryID
, LastModificationTime
, AttachmentLink
, SendTo
, Cc
, ContactID
From
Inserted
END
GO
Thank very much for any help.
-Will
Why the Indexes on table slow down the DML operation on table, what is the exact reason?
View 5 Replies View RelatedHi there,
Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.
ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN
CREATE INDEX resultcol2_ind ON @Result
-- do some other stuff
RETURN
END
Hi
I am using SQL Server 2005 Developer Edition.
I want a list of the following things from the database: -
Table Name , FileGroup Table resides on
Table Name, Index Name, FileGroup index resides on
To put it simply, consider the following example:-
Lets say I have a table XYZ in my database created on Filegroup F1. It has a PK PK1 nonclustered index on Filegroup F2.
List1
-------
XYZ F1
List2
---------
XYZ PK1 F2
Please do not tell me of sp_help <table> option
Regards
Imtiaz
I'm looking for information on how to add indexes to a table in a SQL Server 2000 Database, why add them etc? Any source of good information on the web regarding this?
View 6 Replies View Relatedis there a way to drop all the indexes on a table other than listing them out separately in a drop index statement?
View 2 Replies View RelatedI'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?
Hello again,
Two Short questions this time,
I have a table with several indexes, currently most of them are very narrow (one column), and the question is, when I modify the table by updating a record, does all the indexes are calculated again?? Even if the modified field isn't indexed??? Or the server is smart and knows what indexes to calculate if any.
Second question, can I give to a query a low priority(In dynamic SQL), for example when I don't want my query to exploit too many system resources so it won't interfere the main system ?
Inon.
How can I list indexes with a (SQL) query ?
Thanks
Hi ALL,
Somehow one of tables in my database with clustered index got tempered and the pageid are not in proper order. I tried the checkdb command with repair_rebuild option but it is giving the following error.
Table error: Object ID 1984530649, index ID 1. Page (1:254682) is missing a reference from previous page (1:254681). Possible chain linkage problem.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1984530649, index ID 1. B-tree chain linkage mismatch. (1:254680)->next = (1:256198), but (1:256198)->Prev = (1:256197).
Not able to reindex the index of table. I can not even export the data to any other table with same structure.
Please help me...........
Thanks,
Pracheer
Hi,I simply want to create a duplicate of an existing table with adifferent name. I've tried the "select * into newtable fromorgtable", which works great, except that it doesn't mark the primarykey field from orgtable in the newtable. I tried creating thenewtable prior to using this select, but then sql server cries aboutthe table already existing. I tried creating the index after theselect "CREATE UNIQUE INDEX FieldAIndex ON newtable (FieldA)" - thatdidn't give a error, but also failed to mark the field as primary.Suggestions?
View 2 Replies View RelatedHi,
Regarding to my previous post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=553652&SiteID=1, just wondering if there's a way to detect any changes which is made on the existing indexes in a published table so that the device can determine whether reinitialization of subscrption is needed before every synchronization.
Cheers,
Justin
In our environment we have a very high insert intensive OLTP table, this has 60 million rows. From some of our applications we are very rarely getting timeouts when inserting data into this table. We are expecting that this table would soon run into billion rows and continue to grow. below is the table description and indexes. What indexes should we keep/remove on this table?
Column Name Datatype computed Length Precision Scale Nullable
Col1 int no 4 10 0 no
Col2 smallint no 2 5 0 no
Col3 smallint no 2 5 0 yes
Col4 int no 4 10 0 yes
Col5 int no 4 10 0 yes
Col6 int no 4 10 0 yes
Col7 char no 10 yes
Col8 int no 4 10 0 yes
Col9 uniqueidentifier no 16 yes
Col10 datetime no 8 yes
Col11 smallint no 2 5 0 yes
Col12 varchar no 15 yes
Col13 varchar no 30 yes
Col14 varchar no 50 yes
Col15 int no 4 10 0 yes
Col16 int no 4 10 0 yes
Col17 varchar no 12 yes
Col18 smallint no 2 5 0 yes
Col19 smallint no 2 5 0 yes
Col20 int no 4 10 0 yes
Col21 bit no 1 no
Col22 int no 4 10 0 yes
Col23 bit no 1 no
Col24 bit no 1 no
Col25 datetime no 8 no
Col26 char no 6 yes
Col27 char no 6 yes
Col28 smallint no 2 5 0 yes
Col29 char no 2 yes
Col30 int no 4 10 0 yes
Col31 smallint no 2 5 0 yes
Col32 smallint no 2 5 0 yes
Col33 bit no 1 yes
Index1 nonclustered, stats no recompute located on FileGroup2 Col9, Col10, Col12
Index2 nonclustered, stats no recompute located on FileGroup2 Col9, Col12, Col11, Col10, Col4
Index3 nonclustered, stats no recompute located on FileGroup2 Col6
Index4 nonclustered, stats no recompute located on FileGroup2 Col6, Col4
Index5 nonclustered, stats no recompute located on FileGroup2 Col5, Col9
Index6 nonclustered, stats no recompute located on FileGroup2 Col4, Col9
Index7 nonclustered, stats no recompute located on FileGroup2 Col29
Index8 nonclustered, stats no recompute located on FileGroup2 Col9
Index9 nonclustered, stats no recompute located on FileGroup2 Col6, Col9
Index10 nonclustered, stats no recompute located on FileGroup2 Col7
Index11 nonclustered, stats no recompute located on FileGroup2 Col12
Index12 clustered, unique, primary key, stats no recompute located on FileGroup3 Col1
Any links to MS best practises on indexes for sqlserver 2000 appreciated.
thanks a lot in advance.
How to find if there is a query that can be written on DMV's which will be able to retrieve the indexes that are not being used in a table.
View 2 Replies View RelatedI use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..
-- scFTX_CreateTableEmployees.sql
-- saved in C:/Documents/SQLServerIndexes_downloadCode
-- 26 May 2015 10:52 AM
USE ScottChangDB;
GO
CREATE TABLE Employees
[code]....
Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012.Â
how to take backup of indexes for a particular table.
View 1 Replies View RelatedHow to take backup of indexes for a particular table.
View 5 Replies View RelatedIS there a way to remove all indexes from a table with one single command ( without having to know what the index name is ) ;
View 2 Replies View RelatedDear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I€™m using 5 conditions, I can€™t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:
CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))
CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))
INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC
INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID
WHERE RecordedCalls.ID <= '9369907'
AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))
AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)
AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')
AND RecordedCalls.ServerID NOT IN('2')
AND RecordedCalls.AgentID NOT IN('1000010000')
AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))
AND RecordedCalls.IsDeleted='false'
GROUP BY RecordedCalls.CallType
SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')
WHEN 'Arabic' THEN #tempLookups.NameA
ELSE #tempLookups.NameE
END AS CallsType FROM
#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code
DROP TABLE #tempLookups
DROP TABLE #tempTable
Thanks all,
Tayseer
Any suggestions how to create efficient indexes??!!
I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:
Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.
Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!
-mike
Hi,
How can I copy a database table with all its data, indexes and constraints to a new table in the same database in sql server 2005
Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.
View 4 Replies View RelatedSo 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?
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string basedÂ
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedI'm trying to automate an auto-export of a table on a daily basis,using BCP. I'm using native format for the BCP because the text inone of the fields can encompass pretty much any ASCII characters, andusing the other options (including the null terminator, stuff like|||, etc) hasn't worked particularly well.So, I'm archiving out a table on a daily basis. I want to script outthe table at the same time; that way, if there are any table changes,an import will still work.How can I do this? I've been digging through google for scripts withno luck. Ideally I'd like a table-creation script, along with CREATEINDEX statements.Anybody have a script handy for this? I know it can be done by usingthe system tables, but I'm hoping to avoid reinventing the wheel.Thanks in advance.Michael
View 2 Replies View Related