Distributed Database Unique Index Creation
Nov 14, 2007
Can anyone suggest an appropriate strategy to create unique index's when a distributed database is used. As a bit of a background I have several SQL Server 2005 servers scattered around the country and I am trying to come up with a way to create a unique index i.e. one that hasn't been used in a particular table say Staff in any of the databases around the country.
Performance is an issue particularly as our network isn't that fantastic has anyone done something similar and achieved reasonable speeds?
Thanks ...
View 2 Replies
ADVERTISEMENT
Jul 5, 2015
This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
View 6 Replies
View Related
Sep 22, 2004
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
View 8 Replies
View Related
Jul 20, 2005
HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies
View Related
Jun 24, 2006
What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
View 1 Replies
View Related
Mar 7, 2001
Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?
Which one do one use ?
thanks
sonali
View 4 Replies
View Related
Jan 20, 2006
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
View 2 Replies
View Related
Mar 26, 2008
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
View 12 Replies
View Related
Nov 13, 2007
What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies
View Related
Sep 4, 2015
A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:
USE [AHMC]
GO
/****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSurgicalVolumes] AS
SELECT
[code]....
As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT *
FROM OPENQUERY ([PORTALWEBDB], 'SELECT
--AllLists
AL.tp_ID AS ALtpID
,AL.tp_WebID as altpwebid
,AL.tp_Title AS ALTitle
[code]....
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
[Code] ....
can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.
View 4 Replies
View Related
Jun 21, 2000
Looks like SQL decided to make an index, is this typical? Where can I look at the parameters for this? Thanks
From SQL Profiler:
create statistics hind_239_3 on [calljrnl] ( [cj_created_by] )
View 3 Replies
View Related
Feb 3, 2005
Hi,
like I said in other posts, I'm new to sql server, worked with informix for a long time. Right now I got into an "argument" with the present "dba", the indexes for all the tables in the database are being rebuilt, he wanted to start a monthly process, I told him that he can't do processing because when an index is built the table is locked, now, since I'm new at sql server I would like to know from the experts..... can you run process in sql server against tables when:
- indexes are being created
- the structure of a table is being modified
- the database is being shrunk
If we had been talking about informix then I would have probably slapped him if he tried to "teach" me about how things work, I'm pretty sure its the same in SQL but I think its better to have complete assureness of what I'm talking about before the slapping starts :)
Thanks in advance
Luis Torres
View 6 Replies
View Related
Jun 16, 2008
I have 2 questions,
1. I am trying to create an index, which is running for a long time. Is there an option to know the progress so far or how much more time it is going to take?
2. Is there anyway to find out how much space is required to rebuild an index?
------------------------
I think, therefore I am - Rene Descartes
View 2 Replies
View Related
Oct 27, 2005
Hi -
I could not find an SQL script, which would generate index creation DDL for a specific table/index. Query Analyzer does it, but I need to do it programmatically. Does anyone have a working script?
Thank you.
View 3 Replies
View Related
Jul 23, 2005
Does Microsoft SQL Server keep a record of an index creation date andlast modified date in a system table somewhere?
View 2 Replies
View Related
Jun 16, 2004
I am trying to create a new full text index on some table and getting the follwing error
"The Microsoft Search service
cannot be administered under the present user account"
i have already checked that Microsoft Search service is properly installed with the server version of SQL Server and this is running under the system account
could anyone plz comeout with the solution to this problem]
thanks in advance
bhavya
View 3 Replies
View Related
Jan 16, 2015
I was reading this blog below about Indexed Views and needed some clarification.
When the author choose to create the unique clustered index he excluded the PER.BusinessEntityID columns and only included, (CustomerID, SalesOrderID, ProductID).
Why didn't the PER.BusinessEntityID get added?
[URL] ....
CREATE VIEW Sales.vSalesSummaryCustomerProduct
WITH SCHEMABINDING
AS
SELECT CUST.CustomerID ,
SOH.SalesOrderID ,
SOD.ProductID ,
SUM(SOD.OrderQty) AS TotalOrderQty ,
[Code] .....
View 2 Replies
View Related
May 16, 2008
Hi
I'm trying to use a user defined alias type in a table creation, and everything works as expected, right until the moment where I rightclick on [Indexes] and click [new index] to create a new index.
Then I get this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Object reference not set to an instance of an object. (SqlManagerUI)
------------------------------
BUTTONS:
OK
------------------------------
This is the script I use to create the table:
exec sp_executeSql N'CREATE SCHEMA [TestSchema] AUTHORIZATION [dbo]'
GO
CREATE TYPE [TestSchema].[type_TestType]
FROM uniqueidentifier ;
GO
Create table [TestSchema].[Periode3]
(
Periode3_UID uniqueidentifier NOT NULL ROWGUIDCOL,
rowVersion timestamp NULL,
owner uniqueidentifier not null,
validFrom datetime
NOT NULL CONSTRAINT
DF_Periode_modified3 DEFAULT getdate()
);
Alter table [TestSchema].[Periode3]
add PeriodeType [TestSchema].[type_TestType];
now if I replace the 2 last lines with:
Alter table [TestSchema].[Periode3]
add PeriodeType uniqueidentifier;
everything works as expected.
Where did I go wrong ?
regards
Jens Chr
View 2 Replies
View Related
Mar 27, 2014
I need to find all the index and the creation date. I did cross apply of sys.objects & Sys.indexes on name column. I am getting some but the team is saying they created so many. Any other option to find Indexes and their creation date?
View 2 Replies
View Related
Aug 28, 2001
Hi,
Does anyone have any good tips for reducing the amount of disk space required for new clustered index creation.
I have just finished building a rather large table (48gbs) and durring the clustered index creation I came very close to running out of disk space.
What I am already doing:
1.Recovery Model simple
2. index padding 0
3. fillfactor 0
Any help would be appreciated
daLoonster
View 1 Replies
View Related
Jun 24, 2015
Script they use to generate indexes in SQL 2005.
I have 2 databases on a separate instance. I want to script out all indexes from database1 then execute it on database2.
How to accomplish this task efficiently.
View 5 Replies
View Related
Jul 20, 2005
is there a setting that will ebnable uniform extent allocation uponcreation of index/table by default ?if there isn't any default setting can you code it in?thanks,Doron
View 1 Replies
View Related
Sep 15, 2015
I have a Performance issue in ssrs report , query takes execution time 1 to 2 min. When I add the non clustered index for one of the table columns which takes only 2 sec to execute the query. But as from my environment I couldn't able to add the index due to these changes will effect for all reports. Without adding indexes which takes 2 min to run that report. My question is:
Is there any way to add the non-clustered index on the report itself like if a non clustered index exists "execute the query" else create a non clustered index and run that report with newly created index in report itself like a temp table concept. How to add this concept in this below query:Final Query without non clustered index, takes 2 min to execution time ---
with cte1
( MovementDate,
MovementTypeDesc,
CDCNumber,
MovementTime,
LocationReportMovement,
OtherLocationCode,
[code]....
View 7 Replies
View Related
Mar 29, 2004
Just found out that creating a unique index does not create a unique constraint, but creating a unique constraint creates unique index.
But effectively they do the same thing.
View 3 Replies
View Related
May 8, 2008
How to findout whether a Index is unique or not?
------------------------
I think, therefore I am - Rene Descartes
View 2 Replies
View Related
Mar 26, 2007
if this question is inappropriate here, I apologize (it's at least obliquely related). I have been using ssno as a unique key in a datawarehouse I have been working on because all of the component systems have had it. I now have a database to add where ssno is not available. I have first, last address, city, state,zip and dob.
Question is, how to construct a unique identifier from those components. If not unique, then at least usable?
Again, if this post is wrong here, I apologize
Thanks for any input
Walter
View 1 Replies
View Related
Apr 15, 2015
We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).
1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.
2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?
CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON)
ON TransactionsFG1;
GO
I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)
Placing Indexes on Filegroups:
[URL]
By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:
• Create nonclustered indexes on a filegroup other than the filegroup of the base table.
Move an Existing Index to a Different Filegroup:
[URL]
Limitations and Restrictions
• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.
• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
View 1 Replies
View Related
Feb 6, 2007
This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.
Can anyone shed any light on the problem?
Thanks.
View 5 Replies
View Related
May 21, 2002
I have read that you get better performance with unique indexes rather than non-unique indexes.
I have experimented with this in SQL 2000. I have two identical tables (with about 250000 rows each) with a 12-character unique column. In one table I define it as a regular index and in the other I define it as a unique index. No matter what I try I get identical performance, and the query optimizer shows an identical plan.
I even tried clauses such as
WHERE 1 < (SELECT COUNT(*) FROM TheTable
WHERE key_column = OtherTable.key_column)
which should obviously return nothing if TheTable.key_column is unique. However the query still ran a long time no matter if the index is unique or not.
I have also tried a unique constraint instead of a unique index and got the same (non)results.
Can anyone come up with an example where creating a unique index actually makes a performance difference?
View 1 Replies
View Related
Dec 17, 2006
Hello !
for MS SQL 2000
how can i set an unique index on Serial column but only if Serial IS NOT NULL
CREATE UNIQUE INDEX [IX_Product] ON [Product]([Serial]) ON [PRIMARY]
i can have 100 rows with a NULL Serial
thank you
View 9 Replies
View Related
Apr 19, 2013
I find to be able to have multiple NULL entries in the following constraint:
Code:
CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;
But is not possible to check multiple columns in "WHERE".
I using SQL Server 2012 Express
View 3 Replies
View Related
Feb 12, 2004
I'm trying to weight the pros and cons of unique constraints and unique indexes. I understand that creating a unique constraint also creates an index. If that is the case, why not just use a unique index? Could someone give me an example of when you would want an unique constraint over an unique indexes
Thanks in advance
View 7 Replies
View Related
May 7, 2008
What is the difference between unqiue constraint and unique index? What are the pros and cons? Are they interchangable?
------------------------
I think, therefore I am - Rene Descartes
View 4 Replies
View Related
