I want to make sure that usernames and folder names (that are stored in the database) are all unique when created by the admin through an admin control panel.
How would I code and use an Index to make sure that the 2 columns are unique?
I've looked at examples on the net but nothing is telling me how I can create an index through Enterprise Manager? Thanks
I want to make sure that usernames and folder names (that are stored in the database) are all unique when created by the admin through an admin control panel.
How would I code and use an Index to make sure that the 2 columns are unique?
I've looked at examples on the net but nothing is telling me how I can create an index through Enterprise Manager? Thanks :)
Is there a system table column to query to determine whether an index is defined as unique in SQL Server 2000? It is easy to find this info in SQL Server 2005, but I don't see a valid column for this on dbo.sysindexes.
View 5 Replies View RelatedI am reading "SQL Server Query Performance Tuning Distilled",on page 104 it talks about one of the index design recommendationswhich is to choose the column that has very high selectivity of valuesinstead of a column that has very few selectivity of values.My question is if I have currently indexes on my tables that have1, 2, 3, 4, ... values only on thousands of rows, are these nonclusteredindexes pretty much useless indexes that I should get rid of?And I know that pretty much the number of selectivity values willalways remain very low.Thank you
View 1 Replies View Related
Hi All,
Is there anyway to get the date an index was created on a table? I can see that there is a created date column on various system tables (sys.objects etc) but I can't find one related to an index.
Thanks
Rich
Hey. I've a table with about 18 columns Many of the queries which are made on this table include 4 or 5 columns in the where clause and thesame query has 5 or 6 of the other columns in the select clause. Somebody has created about 11 indexes, 1 for each column...In essence the query would look like this
select col1,col2,col3,col4,col5
from table1
where col6=100
and col7='test.exe'
and col8 = 'helloWorld'
I am thinking the index should be built on all 8 of this columns in SQL 2000. Also, in SQL 2005, I know I can use included columns for this but can someone tell me what columns should the non-clustered built on and which cols should be included? Thank you.
Is index created on only primary key or it can be created on secondary key ?
View 2 Replies View Relatedhistory.ix, index_a.ix, index_d_1.ix, index_di_1.ix, index_i_2.ix,
index_k_2.ix, index_kl_1.ix, index_klh_2.ix, index_n.ix,
index_r_l.ix, index_sv.ix, index_v.ix, index_v_ix.log, indexlog.dat.
This index files are created in my hidden folder. Now, my problem is,
1) Is this files associated with Full Text Search.
2) when the file will be created.
3) For what purpose, this files are stored.
I run a query
select col1, col2, col3, col4
from Table
where col2=5
order by col1
I have a primary key on the column.The execution plan showing the clustered index scan cost 30% & sort cost 70%..When I run the query I got missing index hint on col2 with 95% impact.So I created the non clustered index on col2.The total executed time decreased by around 80ms but I didn't see any Index name that is using in the execution plan.After creating the index also I am seeing same execution plan
The execution plan showing the clustered index scan cost 30% & sort cost 70% but I can see the total time is reducing & Logical reads on that table is reducing.I am sure that index is useful but why there is no change in the execution plan?
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
Hi,
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers.
Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'.
The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused:
I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Any ideas as to what is causing this error?
Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ??
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
Thanks
In Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
When I select a fieldname in a db as a primary key this will keep the uniqueness of that fieldname, but how is that work when I select 2 fieldnames (in Enterprise Manager) and click on the Key it will mark them as my primary key, but I can insert duplicates values in either fieldname.
Can someone explain this to me.
Thanks
hi
am new to sql...i need to write a query ..it is like two teams can play more than 1 game but on different dates..
so if i create the table like this
Create Table Games( TeamName1 char(30),
2 numGoals1 int,
3 TeamName2 char(30),
4 numGoals2 int,
5 play_date char(10),
6 primary key(TeamName1,TeamName2,play_date),
7 Check(TeamName1!=TeamName2)
8 );
this violates the constraint if i give the two teams on the same date..how to give the condition such that it checks for the violation of condition if the team names are interchanged.. for eg
if i give the values as
Insert Into Games values('chelsea',1,'arsenal',2,'17-1-2000');
and
Insert Into Games values('arsenal',1,'chelsea',2,'17-1-2000');
the above should violate the constraint ..how to do it
Hello,
My company is upgrading one of our products; this involves some data migration and a question about existing indexes with uniqueness constraints has been raised. Here is the issue:
We have a database table with two particular columns that that we index with a uniqueness constraint. My question is: is the uniqueness constraint case sensitive?
For instance, if I have a database that is using a collation of SQL_Latin1_General_CP1_CS_AS. (case sensitive) and I insert data in these two columns as follows:
test
col2test
Test
col2test
would this uniqueness constraint be violated even though I'm using a case sensitive collation? I've done tests on this issue and I can't seem to insert this 2nd column; I'm just wanting to be certain that this is the case 100% of the time.
Thanks for your assistance,
Aaron
Hi,
Can anyone tell me the order in which uniqueness constraints on indexes are enforced vs. when triggers are executed ? I have a unique constraint on an index and a trigger on the column on which the same index has been created. When a row is inserted, the trigger checks if the value for that column already exists in the table - if not, it inserts the row as is, else it gets the max() val of the column (based on another key column) and increments it by one, then does the insert. Creating an index across the two works fine, but if I set the Unique Values property for the index, subsequent inserts bomb out - yet there aren't any duplicates in the final table, as the trigger ensures this. Anyone got any ideas on this? My deduction is that the uniqueness constraint gets enforced before the trigger gets executed, but at the same time this *seems* illogical, as the row has not been inserted into the table at the point where the trigger is executed.
Regards,
Jon Reade.
OK - maybe my Google-fu is off today but I'm trying to find out if using a sequence table will provide values that are as unique as the GUID is supposed to be.The reason is that I'm building a DW from the ground up (they won't let me expense my booze which I think is grossly unfair ) and rather than using GUID it ~seems~ like a sequence table would give better results. My concern is that the values won't be unique across tables.
View 9 Replies View RelatedHi,First of all my apologies if you have seen this mail already but I amre-sending as there were some initial problems.This query is related to defining indexes to be unique or not andconsequences thereof.Some documented facts that I am aware of include1. Defining uniqueness allows optimiser to create optimal plans egselect based on keys in such an index allows the optimiser to determineat most only one row will be returned2. Defining uniqueness ensures that rule (business/Primary key) isenforced, regradless of how the data is entered.We have many cases where non unique indexes are defined. The approach todate has been that even though we are aware of some of the benefitsoffered by defining uniqueness , we have chosen not to add keys to nonunique indexes such that they become unique. The primary reason for thiswas that we did not want to make the keys comprising the indexesunnecessarily large and therefore ensuing consequences when DMLstatements are performed.However, I have concerns that having highly duplicate indexes can haveperformance impacts , including deadlocking. I am also aware Sybase usedto store duplicate values in overflow pages and therefore there wereperformance consequences. Could SQL 2000 have the same behaviour ?Thanking you in advancePuvendran*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Relatedtrying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
How Can I know who had created the table in my DB?
May I log it?
So, I have this idea of a database design I was hoping to get some input on.
I usually go through the process of creating a separate column for identity, like an auto-increment integer which serves as a context specific ID that easily can be passed around. However one of the short-comings I've come to realize is that the integer is just that, unique to the context and requires context information to make sense.
I'm pounding the idea of creating a separate table whose sole purpose is to provide global (database wide) unique IDs. As such each row in a table can still have an ID but instead of being tied to a specific context it will always make sense within the application. A foreign key constraint can ensure that and a relation.
Now, with a global ID which I can create FKs to, I don't have to create a separate table for relations between entities and can have relations through these global IDs and I think that's a good thing because if there are many relations between many entities they don't have to be defined more than once. There is also a single "hub" where all relations fit which will allow me to access most information through some relatively simple joins.
My concerns are performance and Linq to SQL implication. I'm worried that Linq to SQL might start pulling an excessive amount of data and I'm worried that the practicality of such a design might hurt the long running of the application development process.
But I'm surprisingly interested in what it could mean for a more ubiquitous view of data and tagging. Basically a way of any data to have relations to any other data through this yet simple design decision.
If you think this is a good idea or see no particular problem with, please let me know! If you think there's problems with my idea, please motivate your critic but do tell me what I should look out for, or if you know of any better approach to my idea of being able to look at the data through some more common mechanism (which applies to all tables and the entire database mostly from a programming perspective).
I"m trying to use a BULK INSERT command to insert data into a table from a file. There is a UNIQUE Index that is being violated and the BULK INSERT fails.
I do not want to drop or disable the index, however, i also do not want to load 'duplicate' records so i keep the CHECK_CONSTRAINTS parameter.
Is there a way to have the duplicate records outputed to the ERRORFILE ?
Is there a command I can use to find out WHO created a specific table?
~Le
I have this table that I thought I could just do a normal update to because I need to add a state and ID.
How do I add a state to a table that is already created?
Here is the behind the scenes. I have never had to deal with something like this.
This is what it looks like
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RegionStates](
[RegionID] [smallint] NOT NULL,
[code]....
How can I find out who created a table?
View 6 Replies View RelatedAll- I have what i'm sure is a question with a simple answer: Supposing I want to modify the select command below to force the display of records with only unique combinations of the two fields person_id and act_session_id (bolded). How would I do this. Note that neither is a key field. (Key field headcount_id was added for completeness.)
Thanks!
SELECT hd.headcount_id, hd.person_id, hd.act_session_idFROM headcount as hd
Hi all!
I would like to gain data from a temporaly table created by an EXEC command.
e.g. EXEC('SELECT col1, col2, col3 FROM Table WHERE ...') - that's right.
But I would like to use it:
SELECT * FROM _ThisTempTableTheExecCommandHasCreatedRigthNow
WHERE...
I know that a function can return a table but an sp cannot.
How can I do it?
Thx: Gurmy
Is there a way to give fields or rows default data when the table is created?
View 1 Replies View RelatedHello,
I have created a package that check if a table exist otherwise it drop it, then it creates a new one (both with SSIS execute SQL task).
Then a Flow task run some transformation SSIS to load the table just created.
The problem is that when I run the package an error occur telling me that the table does not exist (if runs for the first time and in this case the table has not created yet).
How can I run a package that create a table and then Load it.
Thank
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tab_db1'The above query will give the information of the table 'tab_db1' if it is available in the current database (say db1) connected.How can i access the information of a table (say 'tab_db2') which is created under a different database say db2 from the current db connected say db1.I tried the above query with changing the table_name to 'db2..tab_db2'but went invain.sysobjects also dint work..Any help on this will be appreciated..regards,Sathya V
View 2 Replies View RelatedI created a database called sports and then created a table which I'm going to use as a template for other tables within the same database.
What is the proper way to structure and implement copying this template table?
Should I store the template table under the system tables folder?
How do I copy the template table and create a new table from this template?
Thanks you
Goldmember
I use below trigger to email me when a database is created or dropped.
CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @bd VARCHAR(MAX)
DECLARE @tsql VARCHAR(MAX)
SET @tsql = EVENTDATA().value
[Code] ...
Is there a way we can get notification when a table is created or altered or dropped ?