If I could explain the problem..........

In SQL database we need to concatenate 2 fields to display in one and turn them into an email address in the following format

joe.bloggs@.company.co.uk

They are the following: forename & surname

The expression will require a . to be added between the forename & surname and @.company.co.uk at the end.

Anyone help...... ?This should work:

SELECT forename || '.' || surname || '@.company.co.uk' AS [name]
FROM [table]
[WHERE ...];

IF EXISTS SQL Question - using Query Analyzer

I want to only go after Distinct email addressess that contain an @. symbol.

But then I want to return all fields. How do you correctly do that for Microsoft SQL Server?

IFEXISTS (SELECT DISTINCT user_usernameFROM usrWHERE(user_usernameLIKE N'%@.%'))SELECT *FROM usrOrder By user_username

Try something like:

IFEXISTS (SELECT DISTINCT user_usernameFROM usrWHERE(user_usernameLIKE N'%@.%'))
BEGIN

SELECT *
FROM usrOrder By user_username

END 

|||

IF EXISTS only checks for existence of a record matching the WHERE condition. The SELECT is never evaluated. So DISTINCT in SELECT doesnt help. you might as well use SELECT * or SELECT 1. As soon as it finds at least one record that has an "@." in the User_Username the condition evaluates to TRUE.

Identity vs. Identity(1,1)

Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identity,
is (1,1) the assumed default?
Message posted via http://www.droptable.com
Hi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via droptable.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>
|||Hi
When specifying the identity property the seed and increment values are
optional, with a default of 1 for each. Therefore your two tables will be
equivalent. See http://msdn2.microsoft.com/en-us/library/ms186775.aspx for
more
John
"cbrichards via droptable.com" wrote:

> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>

Identity vs. Identity(1,1)

Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identit
y,
is (1,1) the assumed default?
Message posted via http://www.droptable.comHi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via droptable.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as ident
ity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>|||Hi
When specifying the identity property the seed and increment values are
optional, with a default of 1 for each. Therefore your two tables will be
equivalent. See http://msdn2.microsoft.com/en-us/library/ms186775.aspx for
more
John
"cbrichards via droptable.com" wrote:

> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as ident
ity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.droptable.com
>sql

Identity vs. Identity(1,1)

Is there a difference in the resulting values for the identity fields if I
create a table and specify one of the following:
CREATE TABLE #Temp (TempID int identity, Description(100) )
CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
In other words, if (1,1) is not specified after declaring a field as identity,
is (1,1) the assumed default?
--
Message posted via http://www.sqlmonster.comHi cbrichards
BOL says that "You must specify both the seed and increment or neither.
If neither is specified, the default is (1,1)." identity by itself
should be the same as identity(1,1)
When you insert a few records into each table and selected them back
out, what do you get?
CREATE TABLE #Temp (TempID int identity, Description varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
CREATE TABLE #Temp (TempID int identity(1,1), Description
varchar(100) )
GO
INSERT #temp DEFAULT VALUES
GO 10
SELECT * FROM #temp
DROP TABLE #temp
KenJ
cbrichards via SQLMonster.com wrote:
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.sqlmonster.com|||Yes, if you are not specifying the value then it will be defaulted to (1,1)
Thanks
Hari
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6a0b564bac5a5@.uwe...
> Is there a difference in the resulting values for the identity fields if I
> create a table and specify one of the following:
> CREATE TABLE #Temp (TempID int identity, Description(100) )
> CREATE TABLE #Temp (TempID int identity(1,1), Description(100) )
> In other words, if (1,1) is not specified after declaring a field as
> identity,
> is (1,1) the assumed default?
> --
> Message posted via http://www.sqlmonster.com
>

Identity Range not working for master

I have set up a publisher database and a subscriber database which is a replica of the publisher. For the identity fields I set it up so that they would have a range of 10 numbers with an 80% margin. I was testing this on the subscriber and replica at t
he same time. I disconnected the subscriber, used up 8 id's, then connected and sure enough it gave me a new range. However whilst using the publisher it used up all 10 in the range and then gave me an error message! Surely it should have automatically
given me a new range once I'd hit 80% of the previous range.
Any help?
Thanks
Adrian
that depends on how large the batch is. So if you update 20 records in a
batch, it won't get updated and you blow the range.
The idea is to pick ranges that are much larger than representative batches.
So if I were you, I'd try ranges in the 1000's or set ranges that will not
be exceeded in the life time of your replication solution.
Hilary
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> I have set up a publisher database and a subscriber database which is a
replica of the publisher. For the identity fields I set it up so that they
would have a range of 10 numbers with an 80% margin. I was testing this on
the subscriber and replica at the same time. I disconnected the subscriber,
used up 8 id's, then connected and sure enough it gave me a new range.
However whilst using the publisher it used up all 10 in the range and then
gave me an error message! Surely it should have automatically given me a
new range once I'd hit 80% of the previous range.
> Any help?
> Thanks
> Adrian
|||Adrian,
as well as Hilary's reply, you could also consider manual range management
and use an algorithm that ensures no overlap in the ranges:
http://www.mssqlserver.com/replicati...h_identity.asp
HTH,
Paul Ibison
|||Hilary
I do intend to use much larger ranges, however I was just testing out the process on a smaller range to see it in action. It worked for the subscriber but not the publisher?
Any thoughts?
Regards
Adrian
"Hilary Cotter" wrote:

> that depends on how large the batch is. So if you update 20 records in a
> batch, it won't get updated and you blow the range.
> The idea is to pick ranges that are much larger than representative batches.
> So if I were you, I'd try ranges in the 1000's or set ranges that will not
> be exceeded in the life time of your replication solution.
> Hilary
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Adrian" <Adrian@.discussions.microsoft.com> wrote in message
> news:855D2D7D-63EF-40D2-9457-65C97E67A33F@.microsoft.com...
> replica of the publisher. For the identity fields I set it up so that they
> would have a range of 10 numbers with an 80% margin. I was testing this on
> the subscriber and replica at the same time. I disconnected the subscriber,
> used up 8 id's, then connected and sure enough it gave me a new range.
> However whilst using the publisher it used up all 10 in the range and then
> gave me an error message! Surely it should have automatically given me a
> new range once I'd hit 80% of the previous range.
>
>
|||There are a couple of issues here
1) are you running your agent continuously? Running is on a schedule,
every 5-10 minutes, or even less can help the adjustment, otherwise you
might want to manually execute the increment procedure
(sp_adjustpublisheridentityrange) to adjust everything
2) its not clear to me that the number of rows in the batch was close
enough to the threshold to kick off the indentity range adjustment. Was it?
3) The allottment of ranges is not always intuitive. For instance if I
set a range on the Publisher of 100, the Publisher may "own" 0-200, where
the Subscriber "owns" 200-300. You have to look at the check constaints on
the indentity range tables to figure this out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:308EF6B0-3714-4D95-8BE6-4EBD352B1929@.microsoft.com...
> Hilary
> I do intend to use much larger ranges, however I was just testing out the
process on a smaller range to see it in action. It worked for the
subscriber but not the publisher?[vbcol=seagreen]
> Any thoughts?
> Regards
> Adrian
> "Hilary Cotter" wrote:
batches.[vbcol=seagreen]
not[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
on[vbcol=seagreen]
subscriber,[vbcol=seagreen]
then[vbcol=seagreen]
a[vbcol=seagreen]

Identity question

I have two fields that I am concerned with be unique.

Id, and Name.

The id is set to the primary key which automatically makes it unique. How would I set the Name to be unique as well?You add a constraint to it. And making it a primary key doesn't make it an identity. That's yet another type of constraint you apply to it. :)|||Ok here is the example

create table tester(a varchar primary key, b varchar(20) unique);

insert into tester values ('1','2');
insert into tester values ('1','2');
insert into tester values ('2','2');

the first insert statement inserts perfectly, and there will be errors with the second and third statement as a is primary key and is unique and second b is unique. so only first statement gets inserted in to the table.|||Ok, so I cannot specify it at the table level. It must be set at time of insert?|||You can check the uniqueness of the field when u try to insert or update the record/data. with data i think the table is useless.

So the concept of uniqueness comes when u r trying to do some transactions with the table right.

Phani...|||You DO specify it at the table level, either when issuing a CREATE or ALTER table statement.|||Is there any way to do it via Enterprise Manager. I've already created the tables.|||Yes. Check out the"Creating a Unique Constraint" article in MSDN (also in Books Online). It provdes step-by-step instructions. You might actually need a Unique Index instead (follow the link in the article for more information).

Terri|||I actually figured it out late last night, this article is exactly what I did.

Thanks for the help guys.

Identity fields - Why use them

I am building a database for a new project. I am not a DBA or a database designer so please bear with me on this question.

My boss believes that the only time to use an identity column is when we cannot determine a unique primary key. On tables where we can determine a unique primary key identify fields are a waste of reasources. For instance, one of the tables that I need to create is a customer table. Since all of our customers have unique customer numbers my boss believes that in this case an identity column is useless. I don't have enough experience to determine if he is correct or not.

Can someone please explain the pro's and con's of using identity fields to me.

Thanks

Check if any of these help:http://www.sqlteam.com/item.asp?ItemID=2599

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5113

|||

NewToDotNet:

My boss believes that the only time to use an identity column is when we cannot determine a unique primary key. On tables where we can determine a unique primary key identify fields are a waste of reasources. For instance, one of the tables that I need to create is a customer table. Since all of our customers have unique customer numbers my boss believes that in this case an identity column is useless.

I agree with your boss, but I know some people would disagree. It's like religion or politics, there's no point arguing about it.

You need some kind of unique reference for each record, or else you say update that record right there, and accidentally update another thousand that have the same surname and forename.

So if I just had a person's name, I might have several different John Smiths, and so I would want to generate an identity. If I had a real-world unique reference, like a social security number, and could rely on each person to supply it accurately, I would not create another "fictitious" Id.

Unless I was collecting names at a fast food restaurant, where I could not rely on each customer accurately supplying a social security number, so I would have to generate something.

Or unless I had to handle foreign tourists, who would not have a social security number.

Or unless maybe I had databases in different branches, and there was the chance that Joe Public with social security number xxx had done business with more than one branch.

So you have to stop and think how confident you are about the reliability of your "real world" reference, but if you have something dependable, use it.

|||

The issue here is whether one should use a "dataless" key or not. A dataless key is a key that has no intrinsic meaning, it's just a way to uniquely identify a row. Many data modelers believe that dataless keys should be used in all cases because they solve the problem of what happens when your keys domain changs (eg, you discover after 5 years that the key field you thought was big enough isn't and you have to make your item number a 12 character number instead of an 8 character field). This happens more than you think. Here's a nice article describing the issue:http://blogs.ittoolbox.com/eai/implementation/archives/physical-db-design-key-fields-15666. There's a lot of literature on this topic in the database/data modeling world.

OTOH, programmers hate dataless keys for obvious reasons. They are unintuitive, don't lead you "naturally" to join conditions, and make more work up front.

Personally, I hate dataless keys but concede that they are probably a better way to go.

I would bring the issue of dataless keys up with your boss to show him that you know more than he does but in the end go with what he wants -- 'cause he writes your review.

|||

I want to thank everyone who responded to my question. Based on the posts and articles that were presented I see that there are many opposing opionions on this subject, so I can't really say my boss is wrong. And as dbland07666 says, my boss does my review.

Thanks

|||

Your boss may be correct.

If you can find a natural primary key that is reasonable in size, is precise, guarantees uniqueness, will never change, and can never change, then using that as a primary key is ok.

Unfortunately, finding a primary key like that is harder than you may think. If you can't find one that has all the above attributes, then you may still be able to use it as a primary key with some drawbacks. Whether the drawbacks are worth it is a matter of opinion, and as you said, the only real opinion that counts is the one that writes your review.

|||

Thanks for the additional input. I appreciate it.

identity fields - losing values

Hi,
I've an application that consists of a of main table. Each record requires a
numeric reference and these references must be sequential with no gaps.
At the moment, this reference field is of an identity type. This works fine
most of the time, however every now and again the identity field 'loses' a
number (for example goes from 58 to 60) which is a right pain for me.
From my limited understanding of TSQL I see two alternatives
1. Have a manual identity field which I increment manually and use locking
and error trapping to ensure that no values are 'lost'. However, I'm not
really sure how to achieve this...
2. Even if when an identity value is 'lost' it is marked as such is fine, I
simply cannot have any gaps. Can I keep the identity value in place but use
error trapping to ensure that when an insert fails the identity is marked
appropriately rather than simply 'lost'.
Which of the above methods is the recommended one (or are there any other
alternatives) and can any kind soul point me in the right direction as to
how to achieve this (for example, links, etc).
Any and all advice is gratefully received.
Kind regards
Chris.You can't really avoid gaps with an IDENTITY column. If you care about
the value inserted then IDENTITY is the wrong solution.
You haven't explained how this value is to be used. If it is to be
purely based on insertion order then maybe you don't even need it in
the table. Just add a Creation Date column, order by that and display
the row number client-side or in a query.
You can increment a value yourself on the INSERT:
INSERT INTO YourTable (x, ...)
SELECT COALESCE(MAX(x),0)+1, ...
FROM YourTable
but this effectively serialises every INSERT, which may not be
acceptable in a multi-user environment. Logically there isn't a way out
of this: You can't allow concurrent updates if you need to maintain a
serial key in real-time because a rolled-back transaction will always
leave a gap.
David Portas
SQL Server MVP
--|||> 2. Even if when an identity value is 'lost' it is marked as such is
fine, I
> simply cannot have any gaps. Can I keep the identity value in place
but use
> error trapping to ensure that when an insert fails the identity is
marked
> appropriately rather than simply 'lost'.
If you mean that you just want to show the missing values in the data,
you can do so like this:
SELECT
N.num AS id, T.*
FROM Numbers AS N
LEFT JOIN YourTable AS T
ON N.num = T.id
WHERE N.num BETWEEN 1 AND 999999999
Where Numbers is a table containing the total range of IDs.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112956106.268466.113170@.f14g2000cwb.googlegroups.com...
> You can't really avoid gaps with an IDENTITY column. If you care about
> the value inserted then IDENTITY is the wrong solution.
> You haven't explained how this value is to be used. If it is to be
> purely based on insertion order then maybe you don't even need it in
> the table. Just add a Creation Date column, order by that and display
> the row number client-side or in a query.
> You can increment a value yourself on the INSERT:
> INSERT INTO YourTable (x, ...)
> SELECT COALESCE(MAX(x),0)+1, ...
> FROM YourTable
> but this effectively serialises every INSERT, which may not be
> acceptable in a multi-user environment. Logically there isn't a way out
> of this: You can't allow concurrent updates if you need to maintain a
> serial key in real-time because a rolled-back transaction will always
> leave a gap.
> --
> David Portas
> SQL Server MVP
> --
>
David,
Many thanks for your reply. For some background, the reference is simply a
way of identifying each record which as you suggested is based on insertion
order (although to be honest - this isn't terribly important - it wouldn't
be a problem for record X to have a lower ID than Y even if X was inserted
after Y).
I realise that ideally this wouldn't be a requirement as long as the ID is
unique but there is some inertia from another department who see gaps as
indicating missing records (and despite my best efforts can't be convinced
otherwise).
Based on what you've said, how does this sound:
Have a IDStore table. Two fields, "ID" and "committed". When an insert is
attempted, the first ID is found which is uncommitted and this is used. If
no uncommitted ID's are found then a row is inserted into the IDStore table,
with the next free ID and an uncommitted value. When the insert is
successful, the committed field is marked as committed. If it is
unsuccessful it is marked as uncommitted. And so on.
Assuming that I've managed to explain it clearly, does this sound like a
reasonable approach? I realise that I'll have to investigate locking and so
on but at least its something I can work towards..
Once again, your advice is gratefully received.
Chris.|||This is a risk whenever you expose IDENTITY to users. The IDENTITY
value acquires business meaning for the users and then you are lost
because there are too many scenarios in which you can't control the
IDENTITY value. The best policy is not to expose IDENTITY to users at
all.
Assuming the users don't have direct access to the tables (not usually
a good idea to allow this anyway), just don't display the IDENTITY,
which after all should only be used as an artificial key. I assume your
table has an alternative, business key as well. If not then you have a
more serious design problem. IDENTITY should never be the only key of a
table.
If the users need the "comfort factor" of seeing the numbers then see
my other reply on how to fill in the gaps. As you suggest, there are
alternative strategies but they all involve serializing the inserts, or
accepting that there will be gaps.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112958821.065794.173610@.o13g2000cwo.googlegroups.com...
> This is a risk whenever you expose IDENTITY to users. The IDENTITY
> value acquires business meaning for the users and then you are lost
> because there are too many scenarios in which you can't control the
> IDENTITY value. The best policy is not to expose IDENTITY to users at
> all.
> Assuming the users don't have direct access to the tables (not usually
> a good idea to allow this anyway), just don't display the IDENTITY,
> which after all should only be used as an artificial key. I assume your
> table has an alternative, business key as well. If not then you have a
> more serious design problem. IDENTITY should never be the only key of a
> table.
> If the users need the "comfort factor" of seeing the numbers then see
> my other reply on how to fill in the gaps. As you suggest, there are
> alternative strategies but they all involve serializing the inserts, or
> accepting that there will be gaps.
> --
> David Portas
> SQL Server MVP
> --
>
David,
Thanks again for your reply. Based on your advice, I have redesigned the
table in question so that the IDENTITY field is internal to the system and
not visible by the user.
I have created an additional, INT field in this table which will be used for
the reference and will be gapless and sequential - although I've yet to
figure out exactly how to achieve this (but I see triggers in use)...
Thanks once again,
Chris.|||CHris,
Does this new field (we say "column" in SQL btw) have to be immutable?
Will they throw a fit if the record 227 becomes record 226 after someone
deletes record 200?
Will it be impossible to delete records from the table?
If any of these answers are NO, And if the performance hit is acceptable,
then I might suggest that you not persist a value in this new column at all,
Just calculate it on display, as the Count of all other records in the table
with Identity Value <= IDentity value of the record you're displaying
Select <Other COls>,
(Select Count(*) From Table
Where ID <= T.ID) As RowID
From Table T
"Chris Strug" wrote:

> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1112958821.065794.173610@.o13g2000cwo.googlegroups.com...
> David,
> Thanks again for your reply. Based on your advice, I have redesigned the
> table in question so that the IDENTITY field is internal to the system and
> not visible by the user.
> I have created an additional, INT field in this table which will be used f
or
> the reference and will be gapless and sequential - although I've yet to
> figure out exactly how to achieve this (but I see triggers in use)...
> Thanks once again,
> Chris.
>
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:992173F7-F444-4C7E-821A-A18B4F385944@.microsoft.com...
> CHris,
> Does this new field (we say "column" in SQL btw) have to be immutable?
> Will they throw a fit if the record 227 becomes record 226 after someone
> deletes record 200?
> Will it be impossible to delete records from the table?
> If any of these answers are NO, And if the performance hit is acceptable,
> then I might suggest that you not persist a value in this new column at
all,
> Just calculate it on display, as the Count of all other records in the
table
> with Identity Value <= IDentity value of the record you're displaying
> Select <Other COls>,
> (Select Count(*) From Table
> Where ID <= T.ID) As RowID
> From Table T
Hi!
Unfortunately the answer to all your questions is a resounding "yes!".
However, the example you mention is something that I've crossed before and I
feel will be very useful sooner or later.
Thanks for your advice though!
Chris.

identity fields

how do I start an auto increment field at a certain number? other than 1CREATE TABLE test (
test_id int NOT NULL IDENTITY (100, 1)
)

will create a table where the test_id column starts at 100|||Open desired table in design mode
Set the Identity property to "Yes" for the desired field
Set Identity Seed property to the desired value

You can even set the increment value here.

?

Identity Fields

Do ALL identity fields need to be changed to (Not for Replication) before I
create Publisher and Subscribers even if some tables will never be changed
outside of the Publisher? Thanks.
David
David,
this depends on what type of replication you are setting up (and actually
which version of SQL Server as Hilary pointed out to me). Please can you
post back with a little more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry. We are planning to use Merge Replication with initially only
replicating to laptops with MSDE and not to a 2nd SQL Server. We are
using SQL Server 2000 with SP4.
Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||David,
yes - in merge replication, this setting is needed.
Rgds,
Paul Ibison
|||They only need to be set for not for replication if
1) you have more than two nodes in your replication topology (i.e. more than
one subscriber)
2) Inserts occur on both sides between merges
This does capture the majority of merge implementations, but not all of
them.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23tTlvK77FHA.1484@.tk2msftngp13.phx.gbl...
> David,
> yes - in merge replication, this setting is needed.
> Rgds,
> Paul Ibison
>

Identity column without script?

I have a database that is already created and I would like to change all the row id columns (which are currently bigint fields) and turn them into autonumbering Identity fields. Is there a way to do this through the enterprise manager or do I need to recreate all tables in the database using a script that creates identity columns in CREATE_TABLE and then import existing data into it?

Thanks in advance!USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 bigint, col2 char(2))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'
GO

-- This is what EM will Do

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_myTable99
(
Col1 int NOT NULL IDENTITY (1, 1),
col2 char(2) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 ON
GO
IF EXISTS(SELECT * FROM dbo.myTable99)
EXEC('INSERT INTO dbo.Tmp_myTable99 (Col1, col2)
SELECT CONVERT(int, Col1), col2 FROM dbo.myTable99 TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_myTable99 OFF
GO
DROP TABLE dbo.myTable99
GO
EXECUTE sp_rename N'dbo.Tmp_myTable99', N'myTable99', 'OBJECT'
GO
COMMIT
GO

SELECT * FROM myTable99
GO

sp_help myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Yes, but I wanted to do it through the enterprise manager (the database is already set up and populated, but not in production yet.). I was hoping maybe all I had to do was enter a formula in the design view for each table, and then do inserts in code without having to enter that field in my inserts.

Can this be done?|||if you are asking if you can add an identity to a column after the table has been created and the data exists, the answer is yes

open the enterprise manager
right click the table that you want to modify
Right click the table and select Design Table
in the grid at the top choose the column you want to add the identity to
and at the bottom select the following properties

identity = yes
seed = this is the initial value which in the case of existing data is the highest value placed in the column. os for example if your last value in the col was 234 the identityseed would be 234
increment the number that you want to increase the identity by. usually 1

when you insert another row to this table the identity will add 1 to the seed and give you 235 as your next value.

is this what you wanted? :eek:|||That's exactly what I wanted! Thanks!!

IDENTITY and IMPORTING DATA FROM EXISTING DATABASE TO A NEW DB

Hi there,
I need to migrate the database which has identity fields defined to a new DB
which will also be having an identity field. For example table A which has
Field A as IDENTITY and this needs to be migrated to Table B which has Field
B as IDENTITY. The data in Table A has been starting from 1 to 1000 and when
I migrate the data I have a constraint that only certain data will be
migrated it may be from 1 to 100 and then 200 to 400 in that case, if Table
B
has idenity field defined then the data I am migrating will be having Field
B
from 1 to 300 which is not correct. So if someone can give suggestions of ho
w
I can accomplish this migration. I also dont want to change the way IDENTITY
Field is defined. I need to have 301 generated for the new row that gets
generated in Table B.
I hope I am making myself clear here. Any help would be greatly appreciated.
Thanks
JohnJohn
1) If the structure of both databases is identical why not use
BACKUP/RESTORE command?
2) Look at SET IDENTITY_INSERT in the BOL
"John @. X" <JohnX@.discussions.microsoft.com> wrote in message
news:8C370CDD-31EC-46AF-9C6F-13223AAAF8EA@.microsoft.com...
> Hi there,
> I need to migrate the database which has identity fields defined to a new
DB
> which will also be having an identity field. For example table A which has
> Field A as IDENTITY and this needs to be migrated to Table B which has
Field
> B as IDENTITY. The data in Table A has been starting from 1 to 1000 and
when
> I migrate the data I have a constraint that only certain data will be
> migrated it may be from 1 to 100 and then 200 to 400 in that case, if
Table B
> has idenity field defined then the data I am migrating will be having
Field B
> from 1 to 300 which is not correct. So if someone can give suggestions of
how
> I can accomplish this migration. I also dont want to change the way
IDENTITY
> Field is defined. I need to have 301 generated for the new row that gets
> generated in Table B.
> I hope I am making myself clear here. Any help would be greatly
appreciated.
> Thanks
> John|||Try setting IDENTITY_INSERT on for that table
See BOL:
http://msdn.microsoft.com/library/d... />
t_7zas.asp
-- Jesse
On Wed, 9 Feb 2005 21:53:01 -0800, "John @. X"
<JohnX@.discussions.microsoft.com> wrote:

>Hi there,
>I need to migrate the database which has identity fields defined to a new D
B
>which will also be having an identity field. For example table A which has
>Field A as IDENTITY and this needs to be migrated to Table B which has Fiel
d
>B as IDENTITY. The data in Table A has been starting from 1 to 1000 and whe
n
>I migrate the data I have a constraint that only certain data will be
>migrated it may be from 1 to 100 and then 200 to 400 in that case, if Table
B
>has idenity field defined then the data I am migrating will be having Field
B
>from 1 to 300 which is not correct. So if someone can give suggestions of h
ow
>I can accomplish this migration. I also dont want to change the way IDENTIT
Y
>Field is defined. I need to have 301 generated for the new row that gets
>generated in Table B.
>I hope I am making myself clear here. Any help would be greatly appreciated
.
>Thanks
>John|||Thank You for the reply.
But the issue is that BACKUP/RESTORE can pull all the data and make the
IDENTITY correct but my requirement is let us say only 100 records of 1000
data is getting migrated and that too is not in a sequence. In that case my
IDENITY field will be getting wacked and the data is not correctly imported
right.
Thanks
John
"Uri Dimant" wrote:

> John
> 1) If the structure of both databases is identical why not use
> BACKUP/RESTORE command?
> 2) Look at SET IDENTITY_INSERT in the BOL
>
>
> "John @. X" <JohnX@.discussions.microsoft.com> wrote in message
> news:8C370CDD-31EC-46AF-9C6F-13223AAAF8EA@.microsoft.com...
> DB
> Field
> when
> Table B
> Field B
> how
> IDENTITY
> appreciated.
>
>|||John
> IDENTITY correct but my requirement is let us say only 100 records of 1000
> data is getting migrated and that too is not in a sequence. In that case
my
> IDENITY field will be getting wacked and the data is not correctly
imported
> right.
After Restortion you have to manage the tables at new database. I think it
would be easier to delete/truncate (whatever) than manage to insert /dts
from another one. But it is my own opinion.
"John @. X" <JohnX@.discussions.microsoft.com> wrote in message
news:F676A343-50B4-49EA-B48D-EAFA0E1615CF@.microsoft.com...
> Thank You for the reply.
> But the issue is that BACKUP/RESTORE can pull all the data and make the
> IDENTITY correct but my requirement is let us say only 100 records of 1000
> data is getting migrated and that too is not in a sequence. In that case
my
> IDENITY field will be getting wacked and the data is not correctly
imported
> right.
> Thanks
> John
> "Uri Dimant" wrote:
>
new
has
and
of
gets

Identifying unused columns in multiple databases

Hi - we have a situation where we have 23 identical databases on
servers across the country. We know that there are fields within this
db that no one is using and would like to be able to identify all of
them so they can be removed across the board. (There are multiple
tables and columns) We would of course have to verify that none of the
23 offices are using a particular field before it's removed as the db's
need to stay identical. (data in each db varies).
Anyway - I came across this posting which sounds like what we're
looking for, but it specifically references varchar and nvarchar fields
- I can't figure out what to modify in it to make this work for any
type of field. Can anyone point me in the right direction?
This is the posting:
> How can I find all the varchar columns in a table?

> I'm looking for an existing empty or null column rather than add a column.
This will generate a script for the current database that will tell you
all
of the varchar or nvarchar columns, and what percentage of them are
"unused" -- either NULL or empty strings.
SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
+')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
+' WITH (NOLOCK) WHERE
LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
UNION
SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
+')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
+' WITH (NOLOCK)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%varchar'
You can run this in Query Analyzer, and it will generate a script in
the
bottom pane. Copy to a new query window and let 'er rip. Should take
a
while on larger databases. I use WITH (NOLOCK) for this kind of task
to
avoid concurrency and blocking issues; if you need an
up-to-the-millisecond
guaranteed read on the whole table, you may opt to leave that out (but
I do
not recommend it on a production server).
Thank you in advance.Please do not take offense, but if are not familiar enough with SQL Server
and SQL in general to make the necessary change to this query, then you
really are not qualified to be working on the changes you are talking about.
I am not trying to be mean, but your question is much simpler than the task
you intend to eventually perform. If you need help at this point, you will
get disastrous results later on. It is best to get someone who is more of
an expert in such things to handle this project.
"Debbie" <deborah.young@.mail.va.gov> wrote in message
news:1140104286.351275.153030@.g43g2000cwa.googlegroups.com...
> Hi - we have a situation where we have 23 identical databases on
> servers across the country. We know that there are fields within this
> db that no one is using and would like to be able to identify all of
> them so they can be removed across the board. (There are multiple
> tables and columns) We would of course have to verify that none of the
> 23 offices are using a particular field before it's removed as the db's
> need to stay identical. (data in each db varies).
> Anyway - I came across this posting which sounds like what we're
> looking for, but it specifically references varchar and nvarchar fields
> - I can't figure out what to modify in it to make this work for any
> type of field. Can anyone point me in the right direction?
> This is the posting:
>
column.
>
> This will generate a script for the current database that will tell you
> all
> of the varchar or nvarchar columns, and what percentage of them are
> "unused" -- either NULL or empty strings.
> SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK) WHERE
> LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
> UNION
> SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK)'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE LIKE '%varchar'
>
> You can run this in Query Analyzer, and it will generate a script in
> the
> bottom pane. Copy to a new query window and let 'er rip. Should take
> a
> while on larger databases. I use WITH (NOLOCK) for this kind of task
> to
> avoid concurrency and blocking issues; if you need an
> up-to-the-millisecond
> guaranteed read on the whole table, you may opt to leave that out (but
> I do
> not recommend it on a production server).
>
> Thank you in advance.
>|||By "using the column", do you mean:
#1 if the column is populated with at least some data
#2 if there are any queries or procedures that actually reference the
column
The programming below seems to be querying for any columns that are all NULL
or zero length.
Also, a column may have been populated at some point in the past, but is
currently obsolete and not used. If you really need to audit which tables /
columns are in use by the application during normal usage, then you can
setup an object trace in SQL Server Profiler and let it run the background
for a complete business cycle.
http://msdn.microsoft.com/library/d...
ethowto15.asp
"Debbie" <deborah.young@.mail.va.gov> wrote in message
news:1140104286.351275.153030@.g43g2000cwa.googlegroups.com...
> Hi - we have a situation where we have 23 identical databases on
> servers across the country. We know that there are fields within this
> db that no one is using and would like to be able to identify all of
> them so they can be removed across the board. (There are multiple
> tables and columns) We would of course have to verify that none of the
> 23 offices are using a particular field before it's removed as the db's
> need to stay identical. (data in each db varies).
> Anyway - I came across this posting which sounds like what we're
> looking for, but it specifically references varchar and nvarchar fields
> - I can't figure out what to modify in it to make this work for any
> type of field. Can anyone point me in the right direction?
> This is the posting:
>
>
> This will generate a script for the current database that will tell you
> all
> of the varchar or nvarchar columns, and what percentage of them are
> "unused" -- either NULL or empty strings.
> SELECT 'SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''empty'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK) WHERE
> LTRIM(RTRIM(COALESCE('+COLUMN_NAME+','''
')))=''''
> UNION
> SELECT '''+TABLE_NAME+''','''+COLUMN_NAME+''','
''
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_L
ENGTH)
> +')'',''total'',COUNT(*) FROM '+TABLE_SCHEMA+'.'+TABLE_NAME
> +' WITH (NOLOCK)'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE LIKE '%varchar'
>
> You can run this in Query Analyzer, and it will generate a script in
> the
> bottom pane. Copy to a new query window and let 'er rip. Should take
> a
> while on larger databases. I use WITH (NOLOCK) for this kind of task
> to
> avoid concurrency and blocking issues; if you need an
> up-to-the-millisecond
> guaranteed read on the whole table, you may opt to leave that out (but
> I do
> not recommend it on a production server).
>
> Thank you in advance.
>|||I'd suggest running the trace (after it has been tuned to only trace relevan
t
events and return relevant data) for a month (or whatever the actual
turn-over time is).
After that you need to analyze the trace data, identifying individual unused
objects.
The following activities should not be done in live production.
Unused objects should first be renamed before actually being dropped -
rename one at a time to get a clear picture of their actual usage.
After the system has been running successfully for your specific turn-over
time with the renamed objects, you could consider dropping them - in a test
environment first, of course.
This is the bottom-to-top approach.
There is, however, another approach to this - analyze your actual business
requirements. If you think you have unused columns, maybe the entire data
model is wrong for you, and needs to be redesigned. This approach may even
yield better results.
ML
http://milambda.blogspot.com/