Accessing more than: key

Showing posts with label key. Show all posts

Thursday, March 29, 2012

Adding a primary key to a table with two records

This may seem like a stupid question but ...
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?
Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:

> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...[vbcol=seagreen]
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:

> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>
>
|||Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||another thing too (contributing an answer to my own question here) I couldn't
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:

> Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>

Adding a primary key to a table with two records

This may seem like a stupid question but ...
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
>> This may seem like a stupid question but ...
>> say if I have a table called Gender with only two records
>> ID DESC
>> 1 MALE
>> 2 FEMALE
>> is it worth building an index by making ID the primary key?|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:
> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> > This may seem like a stupid question but ...
> > say if I have a table called Gender with only two records
> > ID DESC
> > 1 MALE
> > 2 FEMALE
> > is it worth building an index by making ID the primary key?
>
>|||Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||another thing too (contributing an answer to my own question here) I couldn't
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:
> Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> > This may seem like a stupid question but ...
> > say if I have a table called Gender with only two records
> > ID DESC
> > 1 MALE
> > 2 FEMALE
> > is it worth building an index by making ID the primary key?
>

Adding a primary key to a table with two records

This may seem like a stupid question but ...
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:

> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...[vbcol=seagreen]
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
>|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:

> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>
>|||Why not? Make the PK a clustered index and you don't waste space. It won't i
mprove perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||another thing too (contributing an answer to my own question here) I couldn'
t
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:

> Why not? Make the PK a clustered index and you don't waste space. It won't
improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>

Tuesday, March 27, 2012

adding a new key; planning for millions of records

Hello all,
i'm going to be adding a new column (1 byte) that will become a non-unique
indexed value into a table that i'm adding records into. i know for a fact
that this table will grow from the current thousands to millions of records
over the span of a few w

s and have allocated the database file
accordingly.
since i already have the primary key as clustered is there something i can
do so that adding this key as part of insert operations will be done
efficiently? I'm adding tens of thousands of records a day. In a previous
incarnation of this database i had had problems with having to rebuild the
keys occasionally or insert operations would timeout. I know that part of
the problem was fragmentation of the database, but i'd like to give it a
'heads up' to prepare for a large B-tree for this key.
is there a knob that will control this when i create the index?
thanks,
johnJohn,
I'm assuming you're refering to index fragmentation for the 1 byte index.
Might try creating the index with a lower FILLFACTOR to help minimize
fragmentation.
HTH
Jerry
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:usOEf6$0FHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> i'm going to be adding a new column (1 byte) that will become a non-unique
> indexed value into a table that i'm adding records into. i know for a fact
> that this table will grow from the current thousands to millions of
> records
> over the span of a few w

s and have allocated the database file
> accordingly.
> since i already have the primary key as clustered is there something i can
> do so that adding this key as part of insert operations will be done
> efficiently? I'm adding tens of thousands of records a day. In a previous
> incarnation of this database i had had problems with having to rebuild the
> keys occasionally or insert operations would timeout. I know that part of
> the problem was fragmentation of the database, but i'd like to give it a
> 'heads up' to prepare for a large B-tree for this key.
> is there a knob that will control this when i create the index?
> thanks,
> john
>sql

Monday, March 19, 2012

Add the NOT FOR REPLICATION Option to Existing Tables

Hello,

How to add the NOT FOR REPLICATION Option to Existing Tables( IDENTITY column,FOREIGN KEY constraints )- TSQL.

Thanks,

Kanna.

alter table dbo.testtable

alter column [id] add NOT FOR REPLICATION

Thursday, March 8, 2012

Add or Modify Keys in Merge Replication

I have several articles in a SQL Server 2000 merge publication where I need to do one of the following:
1. Delete a existing key and its column, and create a new one with a new column.
2. Add a new key using existing columns.
3. Add a new key using a new column to be added.
Since this is merge replication, I cannot drop the articles involved using sp_droparticle and then use sp_addarticle after making the modifications.
The publication has several active subscriptions.
Any suggestions and/or procedures for above 3 items would be very much appreciated.
Thanks.
Bill
Bill,
have a look at these three procedures to do what you require:
sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
For your three cases, something like:
1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
sp_repladdcolumn
2. sp_addscriptexec (alter table x add constraint ...)
3. sp_repladdcolumn
For the sp_addscriptexecs don't forget to do the same process on the
publisher, and be sure to get the syntax correct, otherwise there'll be a
continuous error.
HTH,
Paul Ibison
|||Thanks you, Paul! I will give these solutions a try.
"Paul Ibison" wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||Having done one of these (my situation calls for nuber 1) would I have to
reinitialise the snapshot?
On Mon, 5 Jul 2004 20:04:21 +0100, Paul Ibison <Paul.Ibison@.Pygmalion.Com>
wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn
> ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
|||"Paul Ibison" wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||Paul:
I tried using sp_addscriptexec to test the deletion of an existing key. However,
when the subscriber synchronizes, the script fails to run. It keeps saying the
script file or directory does not exists. However, it clearly does exist in the
path that is shown in the error.
The synchronization takes place over the internet. When I have run sp_addscripexec
I have tried each of the following in specifying the location of the script file on
the publisher/distributor:
-- "C:\<directory>\<sub directory>\<script file name>"
-- "\\<server name>\<share name>\<sub directory>\<script file name>"
-- "\\<IP of the server>\<share name>\<sub directory>\<script file name>"
-- "\\<URL of the server>\<share name>\<sub directory>\<script file name>"
The last one does not work when I run sp_addscriptexec. However, the first three
do. After sp_addscriptexec runs, it adds the script file to the snapshot location
on the server. I am running sp_addscriptexec at the publisher through a remote
desktop connection.
When the subscriber synchronizes, in the merge agent session details, the
synchronization is successful and data changes are merged, however, one of the
details shows the error that "osql" cannot find the file or directory at:
"\\<server name>\C$\<share name>\<snapshots folder>\<some long date & time
string>\<script file name>". The "<server name>" is the name of the
publisher\distributor.
I must be missing some step but I find nothing in BOL that helps. I also searched
Micorsoft.com and used Google to look for the osql error, but find nothing.
Do you or does anyone else have any suggestions or ideas to try?
Thanks in advance.
Bill
"Paul Ibison" wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||are you on the network? If you are not connected to the network or the
internet you will get this error.
If you are running workstation or professional you may have exceeded the
number of simultaneous connections for your os.
You may need to issue a net stop server and then a net start server and try
again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:43B07A06-4DBC-48C1-A815-E11BA7103C04@.microsoft.com...
> Paul:
> I tried using sp_addscriptexec to test the deletion of an existing key.
However,
> when the subscriber synchronizes, the script fails to run. It keeps
saying the
> script file or directory does not exists. However, it clearly does exist
in the
> path that is shown in the error.
> The synchronization takes place over the internet. When I have run
sp_addscripexec
> I have tried each of the following in specifying the location of the
script file on
> the publisher/distributor:
> -- "C:\<directory>\<sub directory>\<script file name>"
> -- "\\<server name>\<share name>\<sub directory>\<script file name>"
> -- "\\<IP of the server>\<share name>\<sub directory>\<script file name>"
> -- "\\<URL of the server>\<share name>\<sub directory>\<script file name>"
> The last one does not work when I run sp_addscriptexec. However, the
first three
> do. After sp_addscriptexec runs, it adds the script file to the snapshot
location
> on the server. I am running sp_addscriptexec at the publisher through a
remote
> desktop connection.
> When the subscriber synchronizes, in the merge agent session details, the
> synchronization is successful and data changes are merged, however, one of
the
> details shows the error that "osql" cannot find the file or directory at:
> "\\<server name>\C$\<share name>\<snapshots folder>\<some long date & time
> string>\<script file name>". The "<server name>" is the name of the
> publisher\distributor.
> I must be missing some step but I find nothing in BOL that helps. I also
searched
> Micorsoft.com and used Google to look for the osql error, but find
nothing.[vbcol=seagreen]
> Do you or does anyone else have any suggestions or ideas to try?
> Thanks in advance.
> Bill
>
> "Paul Ibison" wrote:
,[vbcol=seagreen]
a[vbcol=seagreen]

Tuesday, March 6, 2012

Add new primary key column for replicated table with pull subscrip

I need to add a new column to a replicated table. But in my case a new column should be a part of a primary key. So, I can't use sp_repladdcolumn procedure since we need to completely repopulate the whole table and there is no default value for a new colu
mn. In addition to that this column should be first in the table's columns list, not the last one what happens when you usually alter the table adding a new column.
More there, I have a pull subscription and it looks like there is no way to drop subscription for a specified article only, like it is possible for a push subscription. I would appreciate any help pointing to an easiest way to add a new primary key column
for a replicated table with pull subscription.
TIA
Libra,
ordinarily you should be able to use sp_droparticle, sp_addarticle and
sp_refreshsubscriptions however to add the new column in a specific position
you'll need to drop the published article then recreate it and resubscribe.
You might use a nosync subscription to make things easier if the snapshot is
particularly large, but this will have restrictions if you later want to add
an article or column, so I'd recommend initializing with the complete
snapshot as per usual.
HTH,
Paul Ibison
(BTW no doubt you have seen other threads on this but placing columns in a
particular order is not generally considered 'good form' as TSQL should
ideally use explicit column names rather than rely on position.)

Add mode problem on field setup with Foreign Key

Hello,

I have two tables (one-to-many relation) related thru a common key. I have setup the Relation using the Database Designer. The Form is populated using Drag-and-drop which automatically adds the BindingSource, TableAdapter, and Binding Navigator. The Relation works great in the change mode, and life is good.

In the Add mode the field setup with the Foreign Key is not automatically populated as it is in Change mode. The field can be updated manually, but that defeats the purpose of setting up the Relation.

Any ideas how to fix this?

I know this is difficult to visualize without seeing it.

Thank you for any ideas,

Tom

The detail grid (or some other graphic control) should be linked to the relation itself not the underlying detail database table. Master's PK should be FK in Detail.

See here on MSDN2:

One BindingSource is bound to the parent Customers table in the data set. This data is displayed in the master DataGridView control. The other BindingSource is bound to the first data connector. The DataMember property of the second BindingSource is set to the DataRelation name. This causes the associated detail DataGridView control to display the rows of the child Orders table that correspond to the current row in the master DataGridView control.

Add Keywords to Query Analyzers Syntax

Does anyone know of a way to add *key words* to Query Analyzer's syntax
highlighting feature? I would think that there would be a text/xml file
that could be modified. This certainly can't be hard coded.

Thanks,
BryanI think you are going to be disappointed then. I know of no way to add
keywords in the way you want.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Bryan Bullard" <reply@.to.group.com> wrote in message
news:zBylc.6707$l_1.5292@.newssvr23.news.prodigy.co m...
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.
> Thanks,
> Bryan|||"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:4096bd6a$0$31706$fa0fcedb@.lovejoy.zen.co.uk.. .
> I know of no way to add keywords in the way you want.

Then do you know of another way?|||Bryan Bullard (reply@.to.group.com) writes:
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.

There is no way to change the list of highlighted words. I would actually
guess that the list is hardcoded. After all, QA is to designed against a
certain version of SQL Server, so new keywords are not likely to appear
out of the blue. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bryan,

It seems that the keywords are contained in the file
"../tools/binn/sqllex.dll" -- part of which appears in plain-text. For fun,
I tried the following command from a Linux prompt (after backing up the
file):

sed 's/CHECKALLOC/RICHDILLON/' sqllex.dll > new.dll

... and then renamed the new file. Sure enough, QA stopped highlighing
"CHECKALLOC" in blue. Sadly, it wouldn't highlight my name, though. :-(
There must be something important about the rest of the gobbledygook in that
file ;-)

Rich

Quote:

Originally Posted by Erland Sommarskog

Bryan Bullard (reply@.to.group.com) writes:
> Does anyone know of a way to add *key words* to Query Analyzer's syntax
> highlighting feature? I would think that there would be a text/xml file
> that could be modified. This certainly can't be hard coded.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

It's actually hardcoded, and found this out the hardway when a dll was no longer registered.
the syntax highlighting functionality is done by a dll named sqllex.dll that is typically found in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory;
if you unregister the dll, you'll lose the syntax highlighting completely; re-registering it restores the functionality, which i discovered when uninstalling/reinstalling after a beta of SQL 2005.
because the list of keywords is in the DLL, and not in a file the DLL uses, there's no way to ADD keywords unless you could replace that dll with your own enhanced verison.
HTH.

Add Keywords to Query Analyzers Syntax

Thanks,
BryanI think you are going to be disappointed then. I know of no way to add
keywords in the way you want.

--
--

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bryan,

sed 's/CHECKALLOC/RICHDILLON/' sqllex.dll > new.dll

Rich

Saturday, February 25, 2012

Add Identity Increment per user

Hi
I have a Jobs Table: Job_ID,User_ID,Job_Info,...
Job_ID is the Primary Key.
I want to add [Job_ID_PerUser] so I wiil get:
Job_ID Job_ID_PerUser User_ID
1 1 A
2 1 B
3 1 C
4 2 A
5 3 A
6 2 C
Thanks

1) Set the key as primary key...

2) In the coloum properties there in Identity Specification

3) Set it to Yes

4) And set Increment Seed As 1

|||

I have a Primary Key with the Increment Seed=1 named [Job_ID]

I want another ID for a job [Job_ID_PerUser] that will start the count from 1 for every new user

and will increment by 1 every time this new user will add a job

|||

Assuming that you are using SQL Server, you can create the a SQL Server function the will calculate the next value of for the customer and add that as the column for the server.

CREATE FUNCTION dbo.GetCustomerMax (@.userIdint)RETURNSintASBEGINRETURN SELECT MAX(Job_ID_PerUser) + 1WHERE User_Id= @.userIdEND

In SQL Server Management Studio, you modify the table, add the column of type int.

Then create a stored procedure for the insertion of the record and call the GetCustomerMax function.

|||

Thanks it works greate.

do i need to put all of that in one transaction?

Add Foreign Key Constraint in filegroup

Hi,
I'd like to create a foreign key constraint and store it in a different
filegroup than "PRIMARY"
For this, I've created a new filegroup named "INDEX"
This is my script :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
(
[FIL_IDFOLDER]
) REFERENCES [dbo].[T_FOLDER] (
[FOL_IDFOLDER]
) ON DELETE CASCADE
ON [INDEX]
GO
And it doesn't work !
But what is very strange, is that I can create a primary key constraint
correctly on this filegroup :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
(
[FIL_IDFILE]
) ON [INDEX]
GO
And this works !
It would be great if someone can telle me where my error is !
ThanxFilegroups are containers for storing data. Data are either data pages or in
dex pages. The reason
you can specify a filegroup when you define a primary key is that it automat
ically creates an index
for you,. so the file group you specify defines where that index will be sto
red. A foreign key
doesn't come with an index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jerome.avoustin@.gmail.com> wrote in message
news:1137404237.380973.84080@.g43g2000cwa.googlegroups.com...
> Hi,
> I'd like to create a foreign key constraint and store it in a different
> filegroup than "PRIMARY"
> For this, I've created a new filegroup named "INDEX"
> This is my script :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
> (
> [FIL_IDFOLDER]
> ) REFERENCES [dbo].[T_FOLDER] (
> [FOL_IDFOLDER]
> ) ON DELETE CASCADE
> ON [INDEX]
> GO
> And it doesn't work !
> But what is very strange, is that I can create a primary key constraint
> correctly on this filegroup :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
> (
> [FIL_IDFILE]
> ) ON [INDEX]
> GO
> And this works !
> It would be great if someone can telle me where my error is !
> Thanx
>

Friday, February 24, 2012

Add composite primary key

I'm a bit unclear on the syntax to add a composite (multiple columns)
primary key to a table. The columns already exist. Thanks.ALTER TABLE dbo.TableName ADD CONSTRAINT
PK_ConstraintName PRIMARY KEY NONCLUSTERED
(
Col1,
Col2
) ON [PRIMARY]
When in doubt, you can always perform the action in Enterprise Manager
and then look at the change script to help clear up basic syntax
things.
HTH,
Stu|||Do:
ALTER TABLE tbl ADD CONSTRAINT pk PRIMARY KEY ( col1, col2 )
Anith|||Roughly this at table creation:
CONSTRAINT PK_NameIt PRIMARY KEY CLUSTERED (Column1, Column2, Column3)
Obviously if you need a NONCLUSTERED PK, use that syntax instead.
Mark
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1dc2cab9b0d541d8989902@.msnews.microsoft.com...
> I'm a bit unclear on the syntax to add a composite (multiple columns)
> primary key to a table. The columns already exist. Thanks.

Sunday, February 19, 2012

Add Column to an existing table and mention position of the column

Hi all,
Can anybody please help me in this case.

I have a table test(NAME,CITY)
Now i want to add a new column "ID" as primary key to table test and the position of the table should be before column "NAME".

If possible, Please suggest

Thanks
Bob

You can not do that using T-SQL. The way "Enterprise Manager" or "SSMS" does it, is creating a new table with layout you want, copying data, dropping constraint that references the old table, dropping the old table, renaming the new table and recreating all constraints and indexes.

In the relational world, the position of the column is not important.

AMB

|||The only way you can do that is to create a new table, then transfer the data from the old table to the new one, then drop the old table, and finally, rename the new table to the old table's name.|||

Are their any good examples for this action, i thinking it would be easier to add new columns to the aspnet_Profile Table. To expand the user's profile data into single columns instead of one blob of data that is potentially useless. When using a SqlDataProvider for the fact the data in the row comes out in one long string of information.

Unless theirs away to break that information up into useful sections for a FormView or GridView..

add column

I have a table with only about 40 rows. For some reason we accidentally
forgot the primary key. Is it possible to add a column to this table that
adds a number to each row and also adds an increment of 1 when a new item is
added. While we are at it can we make this the primary key column.
I am new at this but I will take a stab. Maybe someone can look at my syntax
and tell me if I am on the right track.
the table name is tblProductSubcategories the column I need to add will be
called intSubcategoryID
Here is my go at it.
Alter Table tblProductSubcategories
Add
intSubcategoryID int identity not null
I know that is not exactly what is needed. Can someone help me with this
syntax. Much appreciated.
Laura K>> I have a table with only about 40 rows. For some reason we
accidentally forgot the primary key. <<
That is not an accident; that is a major design flaw.
each row and also adds an increment of 1 when a new item is added.
While we are at it can we make this the primary key column. <<
BY DEFINITION this is not a primary key!! Start over and do a real
design.|||It's almost done. You just forgot the primary key. Try this
Alter Table tblProductSubcategories
Add
intSubcategoryID int identity not null <B>PRIMARY KEY</B>
--
Lionel
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||YES!! Just add another Column defined as Integer Identity... Say it's
Customers table, and the new PK will be CustomerID...
Alter Table Customers
(Add Column CustomerID Integer Identity Primary Key Not Null)
And there's NOTHING at all wrong with using this type of Primary Key... But
you should also, if at all possible, add another "key" in the form of a
unique constrraint or index, on whichever combination of "real" data columns
uniquely identifies each row... This will prevent the additon of two rows
(with different Idnetity values) from being added that represent the same
real world business object.
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||Laura,
YES, you can!! Just add another Column defined as "Integer Identity
Primary Key"... Say it's Customers table, and the new PK will be CustomerID.
.
Alter Table Customers
(Add Column CustomerID Integer Identity Primary Key Not Null)
And there's NOTHING at all wrong with using this type of Primary Key... But
you should also, if at all possible, add another "key" in the form of a
unique constrraint or index, on whichever combination of "real" data columns
uniquely identifies each row... This will prevent the additon of two rows
(with different Idnetity values) from being added that represent the same
real world business object.
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||Sorry, i forgot to remove the html tags from my answer. It should read:
Alter Table Employee
Add
intSubcategoryID int identity not null PRIMARY KEY
Lionel
"Lionel" wrote:
> It's almost done. You just forgot the primary key. Try this
> --
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null <B>PRIMARY KEY</B>
> --
>
> Lionel
>
> "Laura K" wrote:
>|||Thanks all will give it a try this evening. We actually didn't forget the
PK. We decided to use the subcat name but as I designed I got frustrated
with not having a number. Thus the change. Now when I do this will it add
the numbers incrementally to all 40 rows.
Laura
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:C8B1B527-538E-438C-A19A-26E665C25645@.microsoft.com...
> Laura,
> YES, you can!! Just add another Column defined as "Integer Identity
> Primary Key"... Say it's Customers table, and the new PK will be
> CustomerID...
> Alter Table Customers
> (Add Column CustomerID Integer Identity Primary Key Not Null)
> And there's NOTHING at all wrong with using this type of Primary Key...
> But
> you should also, if at all possible, add another "key" in the form of a
> unique constrraint or index, on whichever combination of "real" data
> columns
> uniquely identifies each row... This will prevent the additon of two rows
> (with different Idnetity values) from being added that represent the same
> real world business object.
>
> "Laura K" wrote:
>|||WOW you are so very helpful. I wish everyone could be as dramatic as you.
It really helps moral.
Laura K
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116025782.902845.162170@.f14g2000cwb.googlegroups.com...
> accidentally forgot the primary key. <<
> That is not an accident; that is a major design flaw.
>
> each row and also adds an increment of 1 when a new item is added.
> While we are at it can we make this the primary key column. <<
> BY DEFINITION this is not a primary key!! Start over and do a real
> design.
>|||Yes, Although the order will be arbitrary... If you want the records
"numbered" in a specific Order, then you need to do something a bit
different... RU interested int that?
(Remember, since this is a "Surrogate" key, the actual value should not be
publicly visible or used by end-users, so the value shouldn't be important,
just that it's unique...)
"Laura K" wrote:

> Thanks all will give it a try this evening. We actually didn't forget th
e
> PK. We decided to use the subcat name but as I designed I got frustrated
> with not having a number. Thus the change. Now when I do this will it ad
d
> the numbers incrementally to all 40 rows.
> Laura
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:C8B1B527-538E-438C-A19A-26E665C25645@.microsoft.com...
>
>|||Joe,
Jeez, -- Only "killing people"?
You mean the fate of the entire space-time continumn is not at risk from
the use of Identity columns ? What a relief!! And all this time I thought..
.
THis is really good news !!
<grin>
Charly
"--CELKO--" wrote:

> My purpose is not to make you feel good. One of my purposes on
> newsgroups is to keep incompetent progammers from killing people. But
> all that matters in your world is your feelings, isn't it?
> If you don't know me or understand that statement, then Google prior
> postings about how people who screwed up schema designs by not knowing
> what 1NF was and a medical supply program to Africa I consulted on.
> You will probably kludge it with an IDENTITY column. But it needs a
> total re-design.
>

Thursday, February 16, 2012

add a Primary Key (or something like that) to a View

Hi,
I have a View like this in my SQL Server 2000:
CREATE VIEW vw_oas_linkhead
AS
SELECT *
FROM oas_linkhead
WHERE (cmpcode = SUSER_SNAME())
WITH CHECK OPTION
The problem is: When, inside an Access-applciation, I put a link to this
View, I can't delete records from the View unless I have a Primary Key
defined on the View.
I can define that Primary Key in Access, but when I refresh the Linked Table
(View) the Primary Key disspaears. This doesn't happen when the Table itself
has a Primary Key on the SQL Server. So I wouldl ike to know if there is a
possiblity to put a Primary Key on a View in Sql Server (I thought this
isn't possible?) or to kind of simulate this on another way (with a check
constraint, ... ?)?
Does anybody has any idea?
Thanks a lot!
PieterI would assume that Access would pick up the underlying table's primary key definition. Do you have
such?
You can't define a PK on a view as the view doesn't store any data in itself. You can, in some
cases, make the view with SCHEMABINING and create a unique index on the view. The question is, of
course, whether Access would pick up on that. But that is a question for the Access experts.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a View like this in my SQL Server 2000:
> CREATE VIEW vw_oas_linkhead
> AS
> SELECT *
> FROM oas_linkhead
> WHERE (cmpcode = SUSER_SNAME())
> WITH CHECK OPTION
> The problem is: When, inside an Access-applciation, I put a link to this
> View, I can't delete records from the View unless I have a Primary Key
> defined on the View.
> I can define that Primary Key in Access, but when I refresh the Linked Table
> (View) the Primary Key disspaears. This doesn't happen when the Table itself
> has a Primary Key on the SQL Server. So I wouldl ike to know if there is a
> possiblity to put a Primary Key on a View in Sql Server (I thought this
> isn't possible?) or to kind of simulate this on another way (with a check
> constraint, ... ?)?
> Does anybody has any idea?
> Thanks a lot!
> Pieter
>|||Well, the problem is that the underlying table doesn't have a Primary Key,
but in access it should.
I'm not allowed to change the udnerlying table, so I should put something on
theView...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> I would assume that Access would pick up the underlying table's primary
key definition. Do you have
> such?
> You can't define a PK on a view as the view doesn't store any data in
itself. You can, in some
> cases, make the view with SCHEMABINING and create a unique index on the
view. The question is, of
> course, whether Access would pick up on that. But that is a question for
the Access experts.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have a View like this in my SQL Server 2000:
> > CREATE VIEW vw_oas_linkhead
> > AS
> > SELECT *
> > FROM oas_linkhead
> > WHERE (cmpcode = SUSER_SNAME())
> > WITH CHECK OPTION
> >
> > The problem is: When, inside an Access-applciation, I put a link to this
> > View, I can't delete records from the View unless I have a Primary Key
> > defined on the View.
> >
> > I can define that Primary Key in Access, but when I refresh the Linked
Table
> > (View) the Primary Key disspaears. This doesn't happen when the Table
itself
> > has a Primary Key on the SQL Server. So I wouldl ike to know if there is
a
> > possiblity to put a Primary Key on a View in Sql Server (I thought this
> > isn't possible?) or to kind of simulate this on another way (with a
check
> > constraint, ... ?)?
> >
> > Does anybody has any idea?
> >
> > Thanks a lot!
> >
> > Pieter
> >
> >
>|||The option for "putting something on the view" you find in my prior post. But the bigger question is
why the table doesn't have a PK...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Well, the problem is that the underlying table doesn't have a Primary Key,
> but in access it should.
> I'm not allowed to change the udnerlying table, so I should put something on
> theView...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
>> I would assume that Access would pick up the underlying table's primary
> key definition. Do you have
>> such?
>> You can't define a PK on a view as the view doesn't store any data in
> itself. You can, in some
>> cases, make the view with SCHEMABINING and create a unique index on the
> view. The question is, of
>> course, whether Access would pick up on that. But that is a question for
> the Access experts.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a View like this in my SQL Server 2000:
>> > CREATE VIEW vw_oas_linkhead
>> > AS
>> > SELECT *
>> > FROM oas_linkhead
>> > WHERE (cmpcode = SUSER_SNAME())
>> > WITH CHECK OPTION
>> >
>> > The problem is: When, inside an Access-applciation, I put a link to this
>> > View, I can't delete records from the View unless I have a Primary Key
>> > defined on the View.
>> >
>> > I can define that Primary Key in Access, but when I refresh the Linked
> Table
>> > (View) the Primary Key disspaears. This doesn't happen when the Table
> itself
>> > has a Primary Key on the SQL Server. So I wouldl ike to know if there is
> a
>> > possiblity to put a Primary Key on a View in Sql Server (I thought this
>> > isn't possible?) or to kind of simulate this on another way (with a
> check
>> > constraint, ... ?)?
>> >
>> > Does anybody has any idea?
>> >
>> > Thanks a lot!
>> >
>> > Pieter
>> >
>> >
>>
>|||I don't know why it doesn't have a Primary Key: It's a table of a big
Accountant Software, so I can't change anything to the table... :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
> The option for "putting something on the view" you find in my prior post.
But the bigger question is
> why the table doesn't have a PK...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Well, the problem is that the underlying table doesn't have a Primary
Key,
> > but in access it should.
> > I'm not allowed to change the udnerlying table, so I should put
something on
> > theView...
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> >> I would assume that Access would pick up the underlying table's primary
> > key definition. Do you have
> >> such?
> >> You can't define a PK on a view as the view doesn't store any data in
> > itself. You can, in some
> >> cases, make the view with SCHEMABINING and create a unique index on the
> > view. The question is, of
> >> course, whether Access would pick up on that. But that is a question
for
> > the Access experts.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> >
> >> > I have a View like this in my SQL Server 2000:
> >> > CREATE VIEW vw_oas_linkhead
> >> > AS
> >> > SELECT *
> >> > FROM oas_linkhead
> >> > WHERE (cmpcode = SUSER_SNAME())
> >> > WITH CHECK OPTION
> >> >
> >> > The problem is: When, inside an Access-applciation, I put a link to
this
> >> > View, I can't delete records from the View unless I have a Primary
Key
> >> > defined on the View.
> >> >
> >> > I can define that Primary Key in Access, but when I refresh the
Linked
> > Table
> >> > (View) the Primary Key disspaears. This doesn't happen when the Table
> > itself
> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if there
is
> > a
> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
this
> >> > isn't possible?) or to kind of simulate this on another way (with a
> > check
> >> > constraint, ... ?)?
> >> >
> >> > Does anybody has any idea?
> >> >
> >> > Thanks a lot!
> >> >
> >> > Pieter
> >> >
> >> >
> >>
> >>
> >
> >
>|||I see :-(. I suggest you post this to an Access forum to see whether you can define in Access what
column define uniqueness.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:e9Y2cqA6EHA.4028@.TK2MSFTNGP15.phx.gbl...
>I don't know why it doesn't have a Primary Key: It's a table of a big
> Accountant Software, so I can't change anything to the table... :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
>> The option for "putting something on the view" you find in my prior post.
> But the bigger question is
>> why the table doesn't have a PK...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
>> > Well, the problem is that the underlying table doesn't have a Primary
> Key,
>> > but in access it should.
>> > I'm not allowed to change the udnerlying table, so I should put
> something on
>> > theView...
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
>> >> I would assume that Access would pick up the underlying table's primary
>> > key definition. Do you have
>> >> such?
>> >> You can't define a PK on a view as the view doesn't store any data in
>> > itself. You can, in some
>> >> cases, make the view with SCHEMABINING and create a unique index on the
>> > view. The question is, of
>> >> course, whether Access would pick up on that. But that is a question
> for
>> > the Access experts.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi,
>> >> >
>> >> > I have a View like this in my SQL Server 2000:
>> >> > CREATE VIEW vw_oas_linkhead
>> >> > AS
>> >> > SELECT *
>> >> > FROM oas_linkhead
>> >> > WHERE (cmpcode = SUSER_SNAME())
>> >> > WITH CHECK OPTION
>> >> >
>> >> > The problem is: When, inside an Access-applciation, I put a link to
> this
>> >> > View, I can't delete records from the View unless I have a Primary
> Key
>> >> > defined on the View.
>> >> >
>> >> > I can define that Primary Key in Access, but when I refresh the
> Linked
>> > Table
>> >> > (View) the Primary Key disspaears. This doesn't happen when the Table
>> > itself
>> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if there
> is
>> > a
>> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
> this
>> >> > isn't possible?) or to kind of simulate this on another way (with a
>> > check
>> >> > constraint, ... ?)?
>> >> >
>> >> > Does anybody has any idea?
>> >> >
>> >> > Thanks a lot!
>> >> >
>> >> > Pieter
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Well I did, and I just got the answer!
It shoudl have been the nicest oslution if I could implement it on the view,
but now I have some VBA that puts the index on the linked view after I
refreshed everything... It seemsto work fine.
Thanks a lot for the effort!
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOSsf5A6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> I see :-(. I suggest you post this to an Access forum to see whether you
can define in Access what
> column define uniqueness.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:e9Y2cqA6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> >I don't know why it doesn't have a Primary Key: It's a table of a big
> > Accountant Software, so I can't change anything to the table... :-)
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
> >> The option for "putting something on the view" you find in my prior
post.
> > But the bigger question is
> >> why the table doesn't have a PK...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> >> > Well, the problem is that the underlying table doesn't have a Primary
> > Key,
> >> > but in access it should.
> >> > I'm not allowed to change the udnerlying table, so I should put
> > something on
> >> > theView...
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> >> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> >> >> I would assume that Access would pick up the underlying table's
primary
> >> > key definition. Do you have
> >> >> such?
> >> >> You can't define a PK on a view as the view doesn't store any data
in
> >> > itself. You can, in some
> >> >> cases, make the view with SCHEMABINING and create a unique index on
the
> >> > view. The question is, of
> >> >> course, whether Access would pick up on that. But that is a question
> > for
> >> > the Access experts.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> >> > Hi,
> >> >> >
> >> >> > I have a View like this in my SQL Server 2000:
> >> >> > CREATE VIEW vw_oas_linkhead
> >> >> > AS
> >> >> > SELECT *
> >> >> > FROM oas_linkhead
> >> >> > WHERE (cmpcode = SUSER_SNAME())
> >> >> > WITH CHECK OPTION
> >> >> >
> >> >> > The problem is: When, inside an Access-applciation, I put a link
to
> > this
> >> >> > View, I can't delete records from the View unless I have a Primary
> > Key
> >> >> > defined on the View.
> >> >> >
> >> >> > I can define that Primary Key in Access, but when I refresh the
> > Linked
> >> > Table
> >> >> > (View) the Primary Key disspaears. This doesn't happen when the
Table
> >> > itself
> >> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if
there
> > is
> >> > a
> >> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
> > this
> >> >> > isn't possible?) or to kind of simulate this on another way (with
a
> >> > check
> >> >> > constraint, ... ?)?
> >> >> >
> >> >> > Does anybody has any idea?
> >> >> >
> >> >> > Thanks a lot!
> >> >> >
> >> >> > Pieter
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

Thursday, March 29, 2012

Adding a primary key to a table with two records

Adding a primary key to a table with two records

Adding a primary key to a table with two records

Tuesday, March 27, 2012

adding a new key; planning for millions of records

Monday, March 19, 2012

Add the NOT FOR REPLICATION Option to Existing Tables

Thursday, March 8, 2012

Add or Modify Keys in Merge Replication

Tuesday, March 6, 2012

Add new primary key column for replicated table with pull subscrip

Add mode problem on field setup with Foreign Key

Add Keywords to Query Analyzers Syntax

Add Keywords to Query Analyzers Syntax

Saturday, February 25, 2012

Add Identity Increment per user

Add Foreign Key Constraint in filegroup

Friday, February 24, 2012

Add composite primary key

Sunday, February 19, 2012

Add Column to an existing table and mention position of the column

add column

Thursday, February 16, 2012

add a Primary Key (or something like that) to a View

add a Primary Key (or something like that) to a View

Accessing more than

Blog Archive

About Me