I simply want to create a duplicate of an existing table with a
different name. I've tried the "select * into newtable from
orgtable", which works great, except that it doesn't mark the primary
key field from orgtable in the newtable. I tried creating the
newtable prior to using this select, but then sql server cries about
the table already existing. I tried creating the index after the
select "CREATE UNIQUE INDEX FieldAIndex ON newtable (FieldA)" - that
didn't give a error, but also failed to mark the field as primary.
Suggestions?
Does anyone know a quick wat to copy indexes? BAscially I have 3 servers (Production, test and dvlp) and I need to make sure that all 3 are in sync with the indexes. I can manually duplicate them (not preferred) and I don't want to override any table data. I assume there has to be a way to just copy indexes with nothing else. At least I hope. Any assistance or suggestions that anyone has is appreciated
I would like some advice on a data and query problem I face. I have adata table with a "raw key" value which is not guaranteed to be validat its source. Normally, this value will be 9 numeric digits and map toa "names" table where the entity is given assigned an "official name".My problem is that I'd like to be able to identify data values that are"close" to being "correct". For example, in the case of anine digit number such as 077467881, I'd like to be able to identifyrows with values close to this raw string. That is, ifthere were a row with a value for this column that was "off" by say, atransposed single digit (such as 077647881 in this example)I would like to find a query to locate the "close candidates" in aresult set. If I can find rows having a raw keyvalue that is close to a "good key" then I can allow my user to useother criteria to possibly assign the "close key" asan alternate or alias of the official key. Here is part of my schema:CREATE TABLE MYData (StateCD char (2) NOT NULL ,CountyCD char (3) NOT NULL ,MYID int NULL ,RawNumString varchar(9) NULL ,SaleMnYear datetime NOT NULL ,NumberWidgets int NOT NULL ,)CREATE TABLE MYNames (MYID int IDENTITY (1, 1) NOT NULL ,OfficialName varchar (70) NOT NULL ,CONSTRAINT PK_MYNames PRIMARY KEY CLUSTERED(MYID))CREATE TABLE MYAltID (RawNumString varchar (9) NOT NULL ,MYID int NOT NULL ,CONSTRAINT PK_MYALTID PRIMARY KEY CLUSTERED(RawNumString) ,CONSTRAINT FK_HasName FOREIGN KEY(MYID) REFERENCES MYNames (MYID))So, how to generalize something like:SELECT * FROM MYData WHERE RawNumString = '077467881'OR RawNumString = '077647881'
I have a fuzzy lookup task that compares a source list of contacts to a reference list of contacts with the default settings. I did some testing by adding seed data that I knew would produce somewhat high similarity hits. All of the seeded contacts but one came back with the expected high sim values. When I looked for the one that didn't, I noticed another match had come up but it had a very low similarity of .17. I then did some research and discovered the reason was the MaxOutputMatchesPerInput setting which was set to 1. I then set it to 3 and reran the package and sure enough my seeded contact that was missing before now showed up. I thought the best match would show up if the MaxOutputMatches was set to 1? That is not the case in my testing.
For example, Donna Mizeman was in the reference list. I added Don Miseman to the source list to seed it. The only match that came back was something like Dieman Abdul .... So the initial match had a similarity of .17 but when MaxOutputMatchesPerInput is set to 3 the best match (seeded) has a similarity of .72.
My requirement is to sling a rowset from one place in SQL server into a table in another place in the most performant way. I want this to be parameterizable - I want to provide just a connection string and some SQL for the source and a connection string and a table name for the destination. The package should do the rest.Â
The solution I chose was an 2014 SSIS package with source and destination as ADO.NET connections configured from project variables. The package has a script task to bulk copy the data. For performance I disable the non-clustered indexes first.Â
But this performance precaution causes the bulk copy to timeout after delivering the correct rowcount to the destination table. What I can do to avoid this error?
Here's my script code:
//get hold of the source and a data reader from it SqlConnection sqlconnSource = new SqlConnection(); sqlconnSource = (SqlConnection)(Dts.Connections["source"].AcquireConnection(Dts.Transaction) as SqlConnection); SqlCommand sourcesqlCommand = new SqlCommand(SourceSQL, sqlconnSource); sourcesqlCommand.CommandTimeout = 1500;
[Code] ....
This takes 128 seconds to put 13 million thin rows into my empty destination table and then throws an exception with this message:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
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.
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
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing: dbo.Categories dbo.CustomerCustomerDemo dbo.CustomerDemographics dbo.Customers dbo.Employees dbo.EmployeeTerritories dbo.Order Details dbo.Orders dbo.Products dbo.Regions dbo.Shippers dbo.Suppliers dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing: dbo.Categories New Table... Modify Open Table Script Table as |> CREATYE To |> DROP To |> SELECT To |> INSERT To |> New Query Editor Window File.... Clipboard UPDATE To |> DELETE to |> From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance, Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
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?
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 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 ?
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.
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.
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?
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.
I 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.Â
PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber
The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..
Dear 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!
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.
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem 1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right? 2) these two table have One Prinary Key column with IDENTITY feature. Any Help Greatly appriciated.Regards.
