Question regarding Identitiy field

Guys,
I have a identity flag set on a column. When i am testing the system,
inserting records the identity goes all the way to 60 and so on. I need to
put the table in production system now. However i need to reset the identity
field to start from 1. How can i clear the identity field and make it start
from the begining.
Thanks
MannyUse this
DBCC CheckIdent('TableName')
"Manny Chohan" wrote:

> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||I'm not sure why you care about the starting value but you can either
truncate the table or execute DBCC CHECKIDENT with the RESEED option.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||Dan,
Just fyi, dbcc checkident defaults to reseed... so,
checkident ('TableName')
is equivilent to
checkident ('TableName', RESEED)
"Dan Guzman" wrote:

> I'm not sure why you care about the starting value but you can either
> truncate the table or execute DBCC CHECKIDENT with the RESEED option.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||Thanks All
"CBretana" wrote:
> Dan,
> Just fyi, dbcc checkident defaults to reseed... so,
> checkident ('TableName')
> is equivilent to
> checkident ('TableName', RESEED)
> "Dan Guzman" wrote:
>|||I am with Dan all the way on this one. Why do you care about the value of
the identity? I admit that I would probably want to reset it myself when
going into production, just because it looks more "tidy." It is however,
always concerning when someone states that they want to know how to do this
because it often means they are using these values in some manner where the
user will care about the value, and identities are pretty bad for this sort
of thing (users HATE gaps, and gaps are just generally part of the identity
experience, since row with the identity property cannot be updated (ever).)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the
> identity
> field to start from 1. How can i clear the identity field and make it
> start
> from the begining.
> Thanks
> Manny|||In theory, I agree 100% with you and Dan on this... I do not believe the
value of any surrogate key, much less an Identoty, should be significant to
anyone...
However, in practice, simply out ofa sense of esthetics, I find myself doing
exactly the same thing this gentleman asked about...
In theory, if we really didn't care about the actual values, we wouldn't
always set the seed value for Identity columns = 1, we'd set it to the lowes
t
legal value for the underlying datatype, (TinyInt, SmallInt, Int).
(Either zero, (0), -32,768, or -2,147,483,648, respectively)
But practically, we do care what the value is, because in coding, and
debugging, and manipulating the data in Query Analyzer, we use these values,
and so they do matter.
"Louis Davidson" wrote:

> I am with Dan all the way on this one. Why do you care about the value of
> the identity? I admit that I would probably want to reset it myself when
> going into production, just because it looks more "tidy." It is however,
> always concerning when someone states that they want to know how to do thi
s
> because it often means they are using these values in some manner where th
e
> user will care about the value, and identities are pretty bad for this sor
t
> of thing (users HATE gaps, and gaps are just generally part of the identit
y
> experience, since row with the identity property cannot be updated (ever).
)
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:6C4D0EBE-7AA5-4F18-9EAA-ED2547B1BD23@.microsoft.com...
>
>|||That was pretty much what I meant too. It is really a weird thing too.
Sure when we start out programming it is easier to have the value start out
at 1, since it is easier to type while doing initial programming, but by all
logic if we really mean that we don't care about the value then starting
at -minvalue would be better (if we are lucky our values will get that big
anyhow!)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:7EF6C6ED-EE40-4AA2-8173-8A99226E0CEA@.microsoft.com...
> In theory, I agree 100% with you and Dan on this... I do not believe the
> value of any surrogate key, much less an Identoty, should be significant
> to
> anyone...
> However, in practice, simply out ofa sense of esthetics, I find myself
> doing
> exactly the same thing this gentleman asked about...
> In theory, if we really didn't care about the actual values, we wouldn't
> always set the seed value for Identity columns = 1, we'd set it to the
> lowest
> legal value for the underlying datatype, (TinyInt, SmallInt, Int).
> (Either zero, (0), -32,768, or -2,147,483,648, respectively)
> But practically, we do care what the value is, because in coding, and
> debugging, and manipulating the data in Query Analyzer, we use these
> values,
> and so they do matter.
>
> "Louis Davidson" wrote:
>|||Manny,
you have two options:
1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN A
TABLE WITHOUT ROLLBACK OPTION!)
Pawel
"Manny Chohan" wrote:

> Guys,
> I have a identity flag set on a column. When i am testing the system,
> inserting records the identity goes all the way to 60 and so on. I need to
> put the table in production system now. However i need to reset the identi
ty
> field to start from 1. How can i clear the identity field and make it star
t
> from the begining.
> Thanks
> Manny|||> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
TRUNCATE does allow a ROLLBACK. However, like other SQL data modification
statements, the transaction will need to be started explicitly unless
IMPLICIT_TRANSACTIONS is on.
CREATE TABLE MyTable(Col1 int NOT NULL)
INSERT INTO MyTable VALUES(1)
BEGIN TRAN
TRUNCATE TABLE MyTable
SELECT Col1 FROM MyTable
ROLLBACK
SELECT Col1 FROM MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Pawel Potasinski" <PawePotasiski@.discussions.microsoft.com> wrote in
message news:11BF931F-D7BC-41A3-B0FD-E2F85943B14B@.microsoft.com...
> Manny,
> you have two options:
> 1. use DBCC CHECKIDENT statement with RESEED option (see Books OnLine),
> 2. use TRUNCATE TABLE on your table (however THIS WILL DELETE ALL ROWS IN
> A
> TABLE WITHOUT ROLLBACK OPTION!)
> Pawel
> "Manny Chohan" wrote:
>

Question on using patindex

Hello,

I am trying to update a column in a table with an 8 digit string.
The 8 digit number is obtained from another column which is a comments
field.

e.g.

Comments Field :

1) RD-V0216970 RQST NON SMKING KING Unknown # : 22338921
2) Received verbal authorization Authorization # 040345
3) international plaza, singapore # 96722540

The code that I am using is

UPDATE U SET U.NUM =
CASE
WHEN U.BOOKED_COMMENTS_TXT LIKE
('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%')
THEN
SUBSTRING(U.BOOKED_COMMENTS_TXT,
PATINDEX('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%',
U.BOOKED_COMMENTS_TXT), 8)
ELSE NULL
END
FROM UNKNOWN1_RESERVATIONS U

Here's what my result set looks like

1)V0216970
2)040345
3)96722540

But this is how I want my result set to look like

1)22338921
2)null
3)96722540

What I need is a way to restrict the search criteria to exactly 8
numeric digits. Any suggestions will be helpful.

Thanks in advanceSQL_developer (vlavanya@.gmail.com) writes:
> I am trying to update a column in a table with an 8 digit string.
> The 8 digit number is obtained from another column which is a comments
> field.
> e.g.
> Comments Field :
> 1) RD-V0216970 RQST NON SMKING KING Unknown # : 22338921
> 2) Received verbal authorization Authorization # 040345
> 3) international plaza, singapore # 96722540
> The code that I am using is
>...
> Here's what my result set looks like
> 1)V0216970
> 2)040345
> 3)96722540
> But this is how I want my result set to look like
> 1)22338921
> 2)null
> 3)96722540
> What I need is a way to restrict the search criteria to exactly 8
> numeric digits. Any suggestions will be helpful.

From the sample it appears that the string you are looking for is
always at the end of the comments field. In this case, this could do:

UPDATE tbl
SET col = CASE WHEN reverse(str)
LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]'
THEN right(str, 8)
ELSE NULL
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank your for your response Erland.

There are times when the string is in the middle or beginning of the
comments field. That's the reason I had to go with Patindex.
Is there anyway I can include the length check into the code ?|||On 31 Mar 2005 06:32:21 -0800, SQL_developer wrote:

>Thank your for your response Erland.
>There are times when the string is in the middle or beginning of the
>comments field. That's the reason I had to go with Patindex.
>Is there anyway I can include the length check into the code ?

Hi SQL_developer,

The reason your query failed is that you searched for 6 numeric
characters instead of 8. Changing that solves it. I also replace the
proprietary UPDATE FROM syntax with the much more portable ANSI-standard
UPDATE syntax - in this case, there is no reason at all to use UPDATE
FROM!

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 040345'
UNION ALL
SELECT 3, 'international plaza, singapore # 96722540 Not in the middle'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN BOOKED_COMMENTS_TXT LIKE
'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN SUBSTRING(BOOKED_COMMENTS_TXT,

PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',
BOOKED_COMMENTS_TXT), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> The reason your query failed is that you searched for 6 numeric
> characters instead of 8. Changing that solves it.

Not really. Consider:

INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V02169709 RQST NON SMKING KING Unknown # : 22338921'

Do we really want the number after the RD-V?

Here is an improved version of your query - built on the assumption
that the number must be surrounded by blanks:

UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN BOOKED_COMMENTS_TXT + ' ' LIKE
'% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %'
THEN SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('% [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %',
BOOKED_COMMENTS_TXT + ' ') + 1, 8)
ELSE NULL
END

> I also replace the proprietary UPDATE FROM syntax with the much more
> portable ANSI-standard UPDATE syntax - in this case, there is no reason
> at all to use UPDATE FROM!

So what! In the general case in SQL Server there is all reason to use
FROM in UPDATE statements, since it gives you so much power and flexibility.
Sure, for this query it's an overkill, but if you think that always
using an alias is good programming style, then you need a FROM clause.
(And that is just one of the reasons that FROM with UPDATE is so good,
it permits you to specify an alias for the table being updated.)

Yeah, it's correct, if your join conditions are incomplete, your UPDATE
will be imcomplete. And I've seen more than one who had gone wrong with
his correlated subqueries. Nevermind that the performance even with
correct correlated subqueries is generally a lot poorer.

I really wish everyone would stop people from bashing people from using
FROM in UPDATE statements. This is a newsgroup for MS SQL Server. If
you don't like FROM in UPDATE clauses, go elsewhere.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 31 Mar 2005 21:26:02 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> The reason your query failed is that you searched for 6 numeric
>> characters instead of 8. Changing that solves it.
>Not really. Consider:
> INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
> SELECT 1, 'RD-V02169709 RQST NON SMKING KING Unknown # : 22338921'
>Do we really want the number after the RD-V?
>Here is an improved version of your query - built on the assumption
>that the number must be surrounded by blanks:
(snip)

Hi Erland,

Thanks for that!

I assumed that the OP wanted to use the first 8-digit number. Upon
rereading his post, I now see that he writes "exactly 8 numeric digits".

Let's hope that your assumption about the number always being surrounded
by spaces is correct. If not, a further change would be:

UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN ' ' + BOOKED_COMMENTS_TXT + ' ' LIKE

'%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%'
THEN SUBSTRING(BOOKED_COMMENTS_TXT,

PATINDEX('%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%',
' ' + BOOKED_COMMENTS_TXT + ' '), 8)
ELSE NULL
END
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for your response Erland and Hugo!

I used the UPDATE FROM statement because this code will be used as a
step in a DTS package and hence I need all the flexibility I can get :)
.. I also find that using an Alias makes it easy for me to read my code
at a later point of time.

The table I am getting my comments field from, gets updated daily and I
will be running the DTS to pick up the 8 digit number every week.
As for the number itself, it may or may not have blanks surrounding it.
I used Hugo's code and it seems to work for most cases.
There are a few cases where the comments field looks like

" Confirmation number 4234235608532, Room for 4"

So when I run this code, it picks up the first 8 digits. But I don't
want it to pick up the 8 digits in this case. It should return null
when the number is greater than 8 digits. Sorry I didn't mention this
earlier. It's just that there are so many records and I keep running
into more situations like these as I modify the code.

Thanks for all your help!|||On 31 Mar 2005 14:36:23 -0800, SQL_developer wrote:

(snip)
>I used Hugo's code and it seems to work for most cases.
>There are a few cases where the comments field looks like
>" Confirmation number 4234235608532, Room for 4"
>So when I run this code, it picks up the first 8 digits. But I don't
>want it to pick up the 8 digits in this case. It should return null
>when the number is greater than 8 digits.
(snip)

Hi SQL_developer,

Did you test it with the code in my first post (addressed to you), or
the code in my second post (replied to Erland)? The latter *SHOULD*
exclude the example above - but I've already shut down my server, so I
can't test it now. Let me know if you saw this happening with the code
in my second post - it's probably a typo; I'll look into it tomorrow.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,

I used the code from your second post. To make it easier for you to run
it in a query analyser I will post the entire code to you.

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN '' + BOOKED_COMMENTS_TXT + '' LIKE
'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN
SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', '' +
BOOKED_COMMENTS_TXT + ''), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Thanks again!|||Hello Hugo,

I used the code from your second post. To make it easier for you to run
it in a query analyser I will post the entire code to you.

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN '' + BOOKED_COMMENTS_TXT + '' LIKE
'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN
SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', '' +
BOOKED_COMMENTS_TXT + ''), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Thanks again!|||On 31 Mar 2005 14:53:50 -0800, SQL_developer wrote:

>Hello Hugo,
>I used the code from your second post. To make it easier for you to run
>it in a query analyser I will post the entire code to you.
(snip)

Hi SQL_developer,

The code you posed was NOT the code from my second post; it was a mix of
code from my first and second post. I've copied the script from your
post into Query Analyzer and corrected the errors - here it is:

Note - use copy and paste to copy it to QA at your end, don't copy it by
hand. One of the errors in your version was that you changed ' ' (quote,
space, quote) to '' (quote, quote). These spaces are very significant!
Another error was that you left out the additional [^0-9] (note the
caret!!) in the LIKE and PATINDEX pattern.

CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V0216970 RQST NON SMKING KING Unknown # : 22338921'
UNION ALL
SELECT 2, 'Received verbal authorization Authorization # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN ' ' + BOOKED_COMMENTS_TXT + ' ' LIKE
'%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%'
THEN
SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX('%[^0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%', ' ' +
BOOKED_COMMENTS_TXT + ' '), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,

Thanks for your reply.

For some reason when I copy and paste data from the post to the DTS
package the Space between the Quotes disappears. That explains why I
did not have the spaces in my code.

I'm sorry about the missing Claret in my code. But I tested the latest
version and it works like a charm. Thank you so much for all your help!

Question on SQL Server2000s order by clause

Hi,
I have problem of order by clase.
Must I specify the column in both select clause and order by clause
so as to get the correct result?

If I omit the order by column in select clause(for example:
select order.*, cl.ID from T_ORDER order, T_CLIENT cl where ... order by cl.code),
would MS SQL server 2000 still correctly or ignore order by clause completely?


It seems the latter actually happens.

BTW, how would other DBMS handle this case?

Regards,
Justinin SQL the ORDER BY clause is independent of the actual query is so far as you can sort by a column that is not listed in the select list

for example

select fname , lastname, middle
from t1
order by zipcode

on a side note you can also order by the ordinal number of the columns in the result set but this is confusing and not good form

ex
select fname , lastname, middle
from t1
order by 2

does this answer your question?sql

question on renaming columns

Hi,
Quick question!
Is there anyway to rename a column such that it reflects all columns on all reference tables, stored procedures,views, etc.,
Ex. table1 is with col1 (primary key)

table2 with col2, col1(FK--table1(col1)

if i try to rename col1 on table1 it has to rename col1 on table2 automatically.
Any help is greatly appreciated.
-SSFirst, buy a magic wand....

I've left typos alone because it wasn't worth the risk...or energy...

Question on refining query

I tried to search for google but i wasn't exactly how to phase it. My tables has a column "DATE", i am populating a listbox using a record set and the generic query "Select DATE From Table1". I want to display only the Uniqu dates, but that i mean if 08/31/04 shows up a 100 times I only want to show it once in that listbox. I have tried altering the values from the recordset and storing them in a collection and looking to see if they reappear but i keep getting errors. So I figured that there had to be an easier way of just redefining the orinal query just to get those dates and then populating the listbox because much more trivial.

any help would be appreciatedFigured it out, just had to add distinct into the query

thanks

Question on partitioning indexes

We have two tables that have full text indexes, currently both are using the
same catalog.
One table is much larger and the column being indexed contains more data.
Would there be any advantage of seperating the indexes into tow seperate
catalogs?
Kyle!
Kyle,
This is one of those questions, where the answer is that it depends... First
of all, see SQL Server 2000 BOL title "Full-Text Search Recommendations" -
"There are also full-text indexing and searching considerations when
determining whether to include multiple SQL tables in one full-text catalog
versus one SQL table per full-text catalog. There is a trade-off between
performance and maintenance when considering this design question with large
SQL tables and you may want to test both options for your environment. If
you choose to have multiple SQL tables in one full-text catalog, you incur
the overhead of longer-running full-text search queries as well because
incremental populations will force the full-text indexing of all other SQL
tables in that full-text catalog. If you choose to have a single SQL table
per full-text catalog and have multiple SQL tables full-text indexed, you
have the overhead of maintaining separate full-text catalogs with a total
limit of 256 full-text catalogs per server."
Another consideration is whether or not you are using CONTAINSTABLE or
FREETEXTTABLE with RANK as having multiple tables in one FT Catalog can
affect the Ranking values...
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:epPuhv3wFHA.3812@.TK2MSFTNGP09.phx.gbl...
> We have two tables that have full text indexes, currently both are using
> the same catalog.
> One table is much larger and the column being indexed contains more data.
> Would there be any advantage of seperating the indexes into tow seperate
> catalogs?
> Kyle!
>
|||This is good stuff. The article was good as well.
We can't seperate the catalog onto a different drive as we only have a RAID5
setup with 6 physical drives and one logical drive.
One table has over 100K records, the other over 94K records. It's not
millions of records, but we are trying to tweak search performace as much as
we can. I don't forsee ever adding 252 more catalogs anywhere in the
future. So seperating the FTI for each table into it's own catalog
shouldn't be an issue.
Thanks
Kyle
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OVq6y79wFHA.1456@.TK2MSFTNGP11.phx.gbl...
> Kyle,
> This is one of those questions, where the answer is that it depends...
> First of all, see SQL Server 2000 BOL title "Full-Text Search
> Recommendations" - "There are also full-text indexing and searching
> considerations when determining whether to include multiple SQL tables in
> one full-text catalog versus one SQL table per full-text catalog. There is
> a trade-off between performance and maintenance when considering this
> design question with large SQL tables and you may want to test both
> options for your environment. If you choose to have multiple SQL tables in
> one full-text catalog, you incur the overhead of longer-running full-text
> search queries as well because incremental populations will force the
> full-text indexing of all other SQL tables in that full-text catalog. If
> you choose to have a single SQL table per full-text catalog and have
> multiple SQL tables full-text indexed, you have the overhead of
> maintaining separate full-text catalogs with a total limit of 256
> full-text catalogs per server."
> Another consideration is whether or not you are using CONTAINSTABLE or
> FREETEXTTABLE with RANK as having multiple tables in one FT Catalog can
> affect the Ranking values...
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
> news:epPuhv3wFHA.3812@.TK2MSFTNGP09.phx.gbl...
>
|||You're welcome, Kyle,
Actually, I wrote that years ago (before SQL 2000 shipped) while I was at
MSFT. You may want to review the collection of FTS related articles at
SQL Server 2000 Full-Text Search Resources and Links
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
for more information on performance and problems/workarounds.
Enjoy!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:%23VlkPPDxFHA.3124@.TK2MSFTNGP12.phx.gbl...
> This is good stuff. The article was good as well.
> We can't seperate the catalog onto a different drive as we only have a
> RAID5 setup with 6 physical drives and one logical drive.
> One table has over 100K records, the other over 94K records. It's not
> millions of records, but we are trying to tweak search performace as much
> as we can. I don't forsee ever adding 252 more catalogs anywhere in the
> future. So seperating the FTI for each table into it's own catalog
> shouldn't be an issue.
> Thanks
> Kyle
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OVq6y79wFHA.1456@.TK2MSFTNGP11.phx.gbl...
>

question on inserting a record on sql server with identity column as key

Hi, All:

Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).

A table on SQL server has an identity column as the key.

We have trouble on adding records to this table using the following SQL.

strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL

The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.

When we try to run the query, it gives errors indicating the key is violated
or missing.

Should I figure out the autonumber for it first and then add to the SQL
server table?

Many thanks,

HSHongyu Sun (sun@.cae.wisc.edu) writes:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C,
D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the
> "myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID"
> and the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is
> violated or missing.

Could you please post the exact error message?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

question on inserting a record on sql server with identity column as key

Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HSThere are two options depending your answer to this question:
Do you want you myTableOnSQLServer table to have the same value of ID from
myTableonAccessLocal.
1. If NO. Then don't specify the ID column in your INSERT INTO statement.
2. If YES. Use SET IDENTITY_INSERT command to allows explicit values to be
inserted into the identity column of a table. Like this:
SET IDENTITY_INSERT myTableOnSQLServer ON
insert into ....
SET IDENTITY_INSERT myTableOnSQLServer OFF
Hope it helps.
"Hongyu Sun" <sun@.cae.wisc.edu> wrote in message
news:dq7fe7$f6b$1@.news.doit.wisc.edu...
> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C,
D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the
"myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID"
and
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is
violated
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
>

Question on Flat File Import

I have a flat file that uses tabs as the column delimiters and cr-lf as row delimiters. The first portion of the file consists of only two columns for approximately 10 rows and then the file changes to 4 columns for the balance of the file, about 21 rows. The column names are in the first column and the data of interest is in the second column for the first 10 rows and then in the third column for the last 21 rows. Is it possible to set up something like this for parsing in SSIS? I've tried using two columns in the data flow task but then I get columns 1 and 2 through the whole file. If I tell it there are 4 columns in the file, it appends rows to each other so that there is a total of 4 columns in the first 10 rows. This reduces the row count to less than 10 and the data in these rows isn't in the proper place. Is there a way to handle this file in SSIS?

TIA

The way I've seen this handled in the past is to set up the flat file source to read the entire line as one column, then use a script task or a derived column to parse the columns.

Kirk Haselden
Author "SQL Server Integration Services"

|||

KirkHaselden wrote:

The way I've seen this handled in the past is to set up the flat file source to read the entire line as one column, then use a script task or a derived column to parse the columns.

Kirk Haselden
Author "SQL Server Integration Services"

i believe you mean "script component" not "script task".|||

Duane Douglas wrote:

KirkHaselden wrote:

The way I've seen this handled in the past is to set up the flat file source to read the entire line as one column, then use a script task or a derived column to parse the columns.

Kirk Haselden
Author "SQL Server Integration Services"

i believe you mean "script component" not "script task".

Ha ha. The teacher becomes the pupil

Nice one Duane!!

Question on derived column expression

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on derived column expression. The expression I am trying to use for the derived column is as below (column1 is a numeric data type column):

case

when column1<0 then 'yes'

else 'no'

end

But I got the error message though, would please any experts here give me any advices on the expression I used? What is wrong with the expression I used above?

Thank you very much and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

column1 < 0 ? "yes" : "no"

That's the correct format for SSIS expressions.|||

What error message are you getting, and where exactly are you trying to do this? If it is in an Execute SQL task or and OLE DB source, that syntax should work. If it is in the Derived Column transform, you need to use something like (freehanding):

Code Snippet

[column1] < 0 ? "yes", "no"

|||

Hi, Phil,

Thanks a lot and would you please kindly advise me if there is any SSIS expression reference?

With best regards,

Yours sincerely,

|||

Helen999888 wrote:

Hi, Phil,

Thanks a lot and would you please kindly advise me if there is any SSIS expression reference?

With best regards,

Yours sincerely,

You can search around here a bit to find examples and such, but you can use Books Online. SSIS expressions are derived from C/C++.

http://msdn2.microsoft.com/en-us/library/ms140206.aspx|||

Hi, Jwelch,

Thank you, yes, the expression i used was in the derived column transform.

Where could I actually find the SSIS expression reference?

Thanks and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

|||Both of the currently available books on SSIS (Professional SQL Server Integration Services and Microsoft SQL Server Integration Services) include information on expressions.|||

Hi, Jwelch,

Thanks a lot.

With best regards,

Yours sincerely,

Question on constraints and partitioned views

Can you use a combination of columns for the partitioning column in a
partition view? This is what I want to do but I cannot get it to work.
I get an error that UNION ALL view 'vw_My_Data' is not updatable
because a partitioning column was not found.
Here's my DDL
--Group1 July 2003
CREATE TABLE [dbo].[Group1_07_2003] (
[Sample_ID] [uniqueidentifier] NOT NULL ,
[Group_Constraint] [int] Check(Group_Constraint = 1) NOT NULL ,
[Month_Constraint] [int] Check (Month_Constraint = 7)NOT NULL ,
[Year_Constraint] [int] Check (Year_Constraint = 2003)NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[msec] [int] NOT NULL ,
[Device_ID] [bigint] NOT NULL ,
[Topic_ID] [bigint] NOT NULL ,
[Sample_Type_ID] [bigint] NOT NULL ,
[Calculated_Value] [float] NOT NULL ,
[Original_Value] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Group1_07_2003] ADD
CONSTRAINT [PK_Group1_07_2003] PRIMARY KEY NONCLUSTERED
(
[Sample_ID],
[Group_Constraint],
[Month_Constraint],
[Year_Constraint]
) ON [PRIMARY]
GO
I would like to create other tables for Group_Constraint=2, and
Month_Constraint = 7, and Year_Constraint = 2003 and so on. But when
I add in the other group, I start getting the errors when I try to
insert into the view. Can I only use one column?Thank you for your recommendation but it did not fix the problem.
Apparently you can only have one column as your partitioning column.
However, I am ready to ditch the partitioned view design. We have
tried every other way we can to actually apply what we want to do and
either we cannot insert into the view or when we query the data it
goes to all of the tables instead of one table based on the
constraint. Even one of the examples in BOL will not work.
Here's the code from BOL:
CREATE TABLE May1998sales
(OrderID INT,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL
CHECK(DATEPART(mm, DeliveryDate) = 5)
CONSTRAINT OrderIDMonth PRIMARY KEY(OrderID, OrderMonth)
CREATE VIEW Year1998Sales
AS
SELECT * FROM Jan1998Sales
UNION ALL
SELECT * FROM Feb1998Sales
UNION ALL
SELECT * FROM Mar1998Sales
UNION ALL
SELECT * FROM Apr1998Sales
UNION ALL
SELECT * FROM May1998Sales
UNION ALL
SELECT * FROM Jun1998Sales
UNION ALL
SELECT * FROM Jul1998Sales
UNION ALL
SELECT * FROM Aug1998Sales
UNION ALL
SELECT * FROM Sep1998Sales
UNION ALL
SELECT * FROM Oct1998Sales
UNION ALL
SELECT * FROM Nov1998Sales
UNION ALL
SELECT * FROM Dec1998Sales
SELECT *
FROM Year1998Sales
WHERE OrderMonth IN (5,6) AND CustomerID = 64892
--
I created just two tables and modified the select statement
SELECT *
FROM Year1998Sales
WHERE OrderMonth =5
I didn't put any data in the tables, I just ran the above select
statement. If you look at the execution plan. It does not go directly
to May1998sales.
I have only gotten one example to actually work and that is using the
customer example
-- On Server1:
CREATE TABLE Customers_33
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
-- On Server2:
CREATE TABLE Customers_66
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 33000 AND 65999),
... -- Additional column definitions)
-- On Server3:
CREATE TABLE Customers_99
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 66000 AND 99999),
... -- Additional column definitions)
However for how we want to partition the data it does not seem to
work. It has been a nightmare. Also don't try to make your
partitioning column on datetime. You can insert into the view but look
at your execution plan. If you use a datetime variable in your where
clause to specify a date, it will not work. I can elaborate more if
anyone is interested. If anyone else is looking at using this design,
BEWARE! It is not documented well in BOL. I think I have said enough.
If anyone has actually gotten this to work, feel free to comment.
"Quentin Ran" <ab@.who.com> wrote in message news:<#of9d4sVDHA.2328@.TK2MSFTNGP12.phx.gbl>...
> Loretta,
> try by putting the PK and constraint in your create table statement. Avoid
> alter table if the table participates in a partitioned view. I do not have
> the reason, but alter table tends to spoil the partitioned view / tables.
> hth
> Quentin
>|||We have similar problem. We have a large table which we
always fetch data by giving certain date.
So, we broke the table into per month bases and created a
partitioned view. The date column (datetime datatype)in
each member table has a check constraint.
When I quire to the view by giving the data condition SQL
server access all member table and it takes very long time
for fetching.
You mentioned that datetime column wouldn't work.
But it must be the once of the most typical case one want
to have a partition view scenario, isn't it?
Is there any workaround from Microsoft side?
I am looking forward to hearing from you!!!
>--Original Message--
>Thank you for your recommendation but it did not fix the
problem.
>Apparently you can only have one column as your
partitioning column.
>However, I am ready to ditch the partitioned view design.
We have
>tried every other way we can to actually apply what we
want to do and
>either we cannot insert into the view or when we query
the data it
>goes to all of the tables instead of one table based on
the
>constraint. Even one of the examples in BOL will not work.
>Here's the code from BOL:
>CREATE TABLE May1998sales
> (OrderID INT,
> CustomerID INT NOT NULL,
> OrderDate DATETIME NULL
> CHECK (DATEPART(yy, OrderDate) = 1998),
> OrderMonth INT
> CHECK (OrderMonth = 5),
> DeliveryDate DATETIME NULL
> CHECK(DATEPART(mm, DeliveryDate) = 5)
> CONSTRAINT OrderIDMonth PRIMARY KEY(OrderID,
OrderMonth)
>CREATE VIEW Year1998Sales
>AS
>SELECT * FROM Jan1998Sales
>UNION ALL
>SELECT * FROM Feb1998Sales
>UNION ALL
>SELECT * FROM Mar1998Sales
>UNION ALL
>SELECT * FROM Apr1998Sales
>UNION ALL
>SELECT * FROM May1998Sales
>UNION ALL
>SELECT * FROM Jun1998Sales
>UNION ALL
>SELECT * FROM Jul1998Sales
>UNION ALL
>SELECT * FROM Aug1998Sales
>UNION ALL
>SELECT * FROM Sep1998Sales
>UNION ALL
>SELECT * FROM Oct1998Sales
>UNION ALL
>SELECT * FROM Nov1998Sales
>UNION ALL
>SELECT * FROM Dec1998Sales
>SELECT *
>FROM Year1998Sales
>WHERE OrderMonth IN (5,6) AND CustomerID = 64892
>--
>I created just two tables and modified the select
statement
>SELECT *
>FROM Year1998Sales
>WHERE OrderMonth =5
>I didn't put any data in the tables, I just ran the above
select
>statement. If you look at the execution plan. It does not
go directly
>to May1998sales.
>I have only gotten one example to actually work and that
is using the
>customer example
>-- On Server1:
>CREATE TABLE Customers_33
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 1 AND 32999),
> ... -- Additional column definitions)
>-- On Server2:
>CREATE TABLE Customers_66
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 33000 AND
65999),
> ... -- Additional column definitions)
>-- On Server3:
>CREATE TABLE Customers_99
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 66000 AND
99999),
> ... -- Additional column definitions)
>However for how we want to partition the data it does not
seem to
>work. It has been a nightmare. Also don't try to make your
>partitioning column on datetime. You can insert into the
view but look
>at your execution plan. If you use a datetime variable in
your where
>clause to specify a date, it will not work. I can
elaborate more if
>anyone is interested. If anyone else is looking at using
this design,
>BEWARE! It is not documented well in BOL. I think I have
said enough.
>If anyone has actually gotten this to work, feel free to
comment.
>
>
>
>"Quentin Ran" <ab@.who.com> wrote in message
news:<#of9d4sVDHA.2328@.TK2MSFTNGP12.phx.gbl>...
>> Loretta,
>> try by putting the PK and constraint in your create
table statement. Avoid
>> alter table if the table participates in a partitioned
view. I do not have
>> the reason, but alter table tends to spoil the
partitioned view / tables.
>> hth
>> Quentin
>>
>.
>|||I agree, it does seem like partitioning by date makes the most sense
but we don't know if any work around. What actually got our team
looking at partitioned views was an article called "Add Scalability
with Data Partitioning" by Jon Rauschenberger. I only have a print
out. Sorry I don't have a link. Anyways, this article seems too good
to be true. But with the design we have in mind, it just might be to
complex. We are still considering dividing our data into smaller
tables but we do not plan on using a view for inserting and querying
the data. We are considering writing our own custom apps to do this
but we are still working on other areas and that has its own
drawbacks. Not sure what the work around is for now. Sorry, no help.
"didi" <carlsdottar@.hotmail.com> wrote in message news:<1a1e01c360c9$c23f09b0$3501280a@.phx.gbl>...
> We have similar problem. We have a large table which we
> always fetch data by giving certain date.
> So, we broke the table into per month bases and created a
> partitioned view. The date column (datetime datatype)in
> each member table has a check constraint.
> When I quire to the view by giving the data condition SQL
> server access all member table and it takes very long time
> for fetching.
> You mentioned that datetime column wouldn't work.
> But it must be the once of the most typical case one want
> to have a partition view scenario, isn't it?
> Is there any workaround from Microsoft side?
> I am looking forward to hearing from you!!!
>
> >--Original Message--

Question on column mappings between mining structure and case table for lift chart

Hi, all experts here,

I am a bit confused for the model evaluation (lift chart), should we map all the columns for both the mining structure and the case table? I mean for those predictive models, we have a predict column, shouldnt we ignore the mapping of the predictive column between the mining structure and the case table? But it seemes we are not allowed to miss the predictive column mapping between the mining structure and the case table.

Why is that? Could any experts here give me some explanation on that?

Hope my question is clear for your help.

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The mapping of the predicted column is required to compare the prediction with the actual test data. It is not used in the actual prediction|||

Hi, Bogdan,

Thanks a lot for your advices.

With best regards,

Yours sincerely,

Question on changing data types on a table

Hi Guys,
I have always wondered what SQL Server actually does when I try to change
the data type on any column in a table. I know that SQL Server will rebuild
the whole table when its changing the data type, so if I had multiple columns
that I wanted to change data types for, this is the only way I have been able
to do it using TSQL:
ALTER TABLE TEST
ALTER COLUMN A (char(4))
ALTER TABLE TEST
ALTER COLUMN B (INT)
ALTER TABLE TEST
ALTER COLUMN C (FLOAT)
Does the above mean that SQL Server has to rebuild my table 3 different
times? How could I rewrite this code so that SQL Server can convert all my
columns with one rebuild? Thanks guys!well! u dont have that flexibility :(
u can add multiple columns using ALTER
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:
> Hi Guys,
> I have always wondered what SQL Server actually does when I try to change
> the data type on any column in a table. I know that SQL Server will rebuild
> the whole table when its changing the data type, so if I had multiple columns
> that I wanted to change data types for, this is the only way I have been able
> to do it using TSQL:
> ALTER TABLE TEST
> ALTER COLUMN A (char(4))
> ALTER TABLE TEST
> ALTER COLUMN B (INT)
> ALTER TABLE TEST
> ALTER COLUMN C (FLOAT)
> Does the above mean that SQL Server has to rebuild my table 3 different
> times? How could I rewrite this code so that SQL Server can convert all my
> columns with one rebuild? Thanks guys!
>|||Okay, how about doing it in Enterprise Manager's Design Table section? I can
see all the columns with their data types at once... if I changed multiple
data types there, does SQL Server rebuild the table for each column, or does
do everything in one shot? Appreciate the help!
"Chandra" wrote:
> well! u dont have that flexibility :(
> u can add multiple columns using ALTER
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Shishir Viriyala" wrote:
> > Hi Guys,
> >
> > I have always wondered what SQL Server actually does when I try to change
> > the data type on any column in a table. I know that SQL Server will rebuild
> > the whole table when its changing the data type, so if I had multiple columns
> > that I wanted to change data types for, this is the only way I have been able
> > to do it using TSQL:
> >
> > ALTER TABLE TEST
> > ALTER COLUMN A (char(4))
> >
> > ALTER TABLE TEST
> > ALTER COLUMN B (INT)
> >
> > ALTER TABLE TEST
> > ALTER COLUMN C (FLOAT)
> >
> > Does the above mean that SQL Server has to rebuild my table 3 different
> > times? How could I rewrite this code so that SQL Server can convert all my
> > columns with one rebuild? Thanks guys!
> >|||it rebuilds for each column.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
> > well! u dont have that flexibility :(
> > u can add multiple columns using ALTER
> >
> > --
> > best Regards,
> > Chandra
> > http://chanduas.blogspot.com/
> > http://groups.msn.com/SQLResource/
> > ---
> >
> >
> >
> > "Shishir Viriyala" wrote:
> >
> > > Hi Guys,
> > >
> > > I have always wondered what SQL Server actually does when I try to change
> > > the data type on any column in a table. I know that SQL Server will rebuild
> > > the whole table when its changing the data type, so if I had multiple columns
> > > that I wanted to change data types for, this is the only way I have been able
> > > to do it using TSQL:
> > >
> > > ALTER TABLE TEST
> > > ALTER COLUMN A (char(4))
> > >
> > > ALTER TABLE TEST
> > > ALTER COLUMN B (INT)
> > >
> > > ALTER TABLE TEST
> > > ALTER COLUMN C (FLOAT)
> > >
> > > Does the above mean that SQL Server has to rebuild my table 3 different
> > > times? How could I rewrite this code so that SQL Server can convert all my
> > > columns with one rebuild? Thanks guys!
> > >|||Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
to execute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>> well! u dont have that flexibility :(
>> u can add multiple columns using ALTER
>> --
>> best Regards,
>> Chandra
>> http://chanduas.blogspot.com/
>> http://groups.msn.com/SQLResource/
>> ---
>>
>> "Shishir Viriyala" wrote:
>> > Hi Guys,
>> >
>> > I have always wondered what SQL Server actually does when I try to change
>> > the data type on any column in a table. I know that SQL Server will rebuild
>> > the whole table when its changing the data type, so if I had multiple columns
>> > that I wanted to change data types for, this is the only way I have been able
>> > to do it using TSQL:
>> >
>> > ALTER TABLE TEST
>> > ALTER COLUMN A (char(4))
>> >
>> > ALTER TABLE TEST
>> > ALTER COLUMN B (INT)
>> >
>> > ALTER TABLE TEST
>> > ALTER COLUMN C (FLOAT)
>> >
>> > Does the above mean that SQL Server has to rebuild my table 3 different
>> > times? How could I rewrite this code so that SQL Server can convert all my
>> > columns with one rebuild? Thanks guys!
>> >|||Aha! Thanks for the tip Tibor! I have included what the change script was
showing me. The two columns for which I changed data types were CASEID and
DX. Note that both those columns are being converted in one statement.
Using EP is definitely the way to go!
Thanks to you and Chandra for the input......
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_ip_adm_dx
(
CASEID int NULL,
DX varchar(5) NULL,
DXNO int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.ip_adm_dx)
EXEC('INSERT INTO dbo.Tmp_ip_adm_dx (CASEID, DX, DXNO)
SELECT CONVERT(int, CASEID), CONVERT(varchar(5), DX), DXNO FROM
dbo.ip_adm_dx TABLOCKX')
GO
DROP TABLE dbo.ip_adm_dx
GO
EXECUTE sp_rename N'dbo.Tmp_ip_adm_dx', N'ip_adm_dx', 'OBJECT'
GO
COMMIT
"Tibor Karaszi" wrote:
> Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
> to execute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
> news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
> > Okay, how about doing it in Enterprise Manager's Design Table section? I can
> > see all the columns with their data types at once... if I changed multiple
> > data types there, does SQL Server rebuild the table for each column, or does
> > do everything in one shot? Appreciate the help!
> >
> > "Chandra" wrote:
> >
> >> well! u dont have that flexibility :(
> >> u can add multiple columns using ALTER
> >>
> >> --
> >> best Regards,
> >> Chandra
> >> http://chanduas.blogspot.com/
> >> http://groups.msn.com/SQLResource/
> >> ---
> >>
> >>
> >>
> >> "Shishir Viriyala" wrote:
> >>
> >> > Hi Guys,
> >> >
> >> > I have always wondered what SQL Server actually does when I try to change
> >> > the data type on any column in a table. I know that SQL Server will rebuild
> >> > the whole table when its changing the data type, so if I had multiple columns
> >> > that I wanted to change data types for, this is the only way I have been able
> >> > to do it using TSQL:
> >> >
> >> > ALTER TABLE TEST
> >> > ALTER COLUMN A (char(4))
> >> >
> >> > ALTER TABLE TEST
> >> > ALTER COLUMN B (INT)
> >> >
> >> > ALTER TABLE TEST
> >> > ALTER COLUMN C (FLOAT)
> >> >
> >> > Does the above mean that SQL Server has to rebuild my table 3 different
> >> > times? How could I rewrite this code so that SQL Server can convert all my
> >> > columns with one rebuild? Thanks guys!
> >> >
>|||On Fri, 5 Aug 2005 08:15:38 -0700, Shishir Viriyala wrote:
>Okay, how about doing it in Enterprise Manager's Design Table section? I can
>see all the columns with their data types at once... if I changed multiple
>data types there, does SQL Server rebuild the table for each column, or does
>do everything in one shot? Appreciate the help!
Hi Shishir,
Neither. Enterprise Manager will simply create a new table, copy over
the data, then drop the old table. If the table is involved in any
foreign key relationships, it will drop and recreate them as well. And
if the table has any triggers, they too will be recreated.
Copying the data over to a new table might be the best strategy in your
specific case, but in general, the methods chosen by EM to apply changes
tend to be overkill. (And very slow on large tables!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Question on changing data types on a table

Hi Guys,
I have always wondered what SQL Server actually does when I try to change
the data type on any column in a table. I know that SQL Server will rebuild
the whole table when its changing the data type, so if I had multiple column
s
that I wanted to change data types for, this is the only way I have been abl
e
to do it using TSQL:
ALTER TABLE TEST
ALTER COLUMN A (char(4))
ALTER TABLE TEST
ALTER COLUMN B (INT)
ALTER TABLE TEST
ALTER COLUMN C (FLOAT)
Does the above mean that SQL Server has to rebuild my table 3 different
times? How could I rewrite this code so that SQL Server can convert all my
columns with one rebuild? Thanks guys!well! u dont have that flexibility
u can add multiple columns using ALTER
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:

> Hi Guys,
> I have always wondered what SQL Server actually does when I try to change
> the data type on any column in a table. I know that SQL Server will rebui
ld
> the whole table when its changing the data type, so if I had multiple colu
mns
> that I wanted to change data types for, this is the only way I have been a
ble
> to do it using TSQL:
> ALTER TABLE TEST
> ALTER COLUMN A (char(4))
> ALTER TABLE TEST
> ALTER COLUMN B (INT)
> ALTER TABLE TEST
> ALTER COLUMN C (FLOAT)
> Does the above mean that SQL Server has to rebuild my table 3 different
> times? How could I rewrite this code so that SQL Server can convert all m
y
> columns with one rebuild? Thanks guys!
>|||Okay, how about doing it in Enterprise Manager's Design Table section? I ca
n
see all the columns with their data types at once... if I changed multiple
data types there, does SQL Server rebuild the table for each column, or does
do everything in one shot? Appreciate the help!
"Chandra" wrote:
[vbcol=seagreen]
> well! u dont have that flexibility
> u can add multiple columns using ALTER
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Shishir Viriyala" wrote:
>|||it rebuilds for each column.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I
can
> see all the columns with their data types at once... if I changed multipl
e
> data types there, does SQL Server rebuild the table for each column, or do
es
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>|||Check and see :-). Press the "save change script" button and you will see th
e SQL that EM is about
to execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in mess
age
news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I
can
> see all the columns with their data types at once... if I changed multipl
e
> data types there, does SQL Server rebuild the table for each column, or do
es
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
>|||Aha! Thanks for the tip Tibor! I have included what the change script was
showing me. The two columns for which I changed data types were CASEID and
DX. Note that both those columns are being converted in one statement.
Using EP is definitely the way to go!
Thanks to you and Chandra for the input......
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_ip_adm_dx
(
CASEID int NULL,
DX varchar(5) NULL,
DXNO int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.ip_adm_dx)
EXEC('INSERT INTO dbo.Tmp_ip_adm_dx (CASEID, DX, DXNO)
SELECT CONVERT(int, CASEID), CONVERT(varchar(5), DX), DXNO FROM
dbo.ip_adm_dx TABLOCKX')
GO
DROP TABLE dbo.ip_adm_dx
GO
EXECUTE sp_rename N'dbo.Tmp_ip_adm_dx', N'ip_adm_dx', 'OBJECT'
GO
COMMIT
"Tibor Karaszi" wrote:

> Check and see :-). Press the "save change script" button and you will see
the SQL that EM is about
> to execute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in me
ssage
> news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
>|||On Fri, 5 Aug 2005 08:15:38 -0700, Shishir Viriyala wrote:

>Okay, how about doing it in Enterprise Manager's Design Table section? I c
an
>see all the columns with their data types at once... if I changed multiple
>data types there, does SQL Server rebuild the table for each column, or doe
s
>do everything in one shot? Appreciate the help!
Hi Shishir,
Neither. Enterprise Manager will simply create a new table, copy over
the data, then drop the old table. If the table is involved in any
foreign key relationships, it will drop and recreate them as well. And
if the table has any triggers, they too will be recreated.
Copying the data over to a new table might be the best strategy in your
specific case, but in general, the methods chosen by EM to apply changes
tend to be overkill. (And very slow on large tables!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Question on changing data types on a table

Hi Guys,
I have always wondered what SQL Server actually does when I try to change
the data type on any column in a table. I know that SQL Server will rebuild
the whole table when its changing the data type, so if I had multiple columns
that I wanted to change data types for, this is the only way I have been able
to do it using TSQL:
ALTER TABLE TEST
ALTER COLUMN A (char(4))
ALTER TABLE TEST
ALTER COLUMN B (INT)
ALTER TABLE TEST
ALTER COLUMN C (FLOAT)
Does the above mean that SQL Server has to rebuild my table 3 different
times? How could I rewrite this code so that SQL Server can convert all my
columns with one rebuild? Thanks guys!
well! u dont have that flexibility
u can add multiple columns using ALTER
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Shishir Viriyala" wrote:

> Hi Guys,
> I have always wondered what SQL Server actually does when I try to change
> the data type on any column in a table. I know that SQL Server will rebuild
> the whole table when its changing the data type, so if I had multiple columns
> that I wanted to change data types for, this is the only way I have been able
> to do it using TSQL:
> ALTER TABLE TEST
> ALTER COLUMN A (char(4))
> ALTER TABLE TEST
> ALTER COLUMN B (INT)
> ALTER TABLE TEST
> ALTER COLUMN C (FLOAT)
> Does the above mean that SQL Server has to rebuild my table 3 different
> times? How could I rewrite this code so that SQL Server can convert all my
> columns with one rebuild? Thanks guys!
>
|||Okay, how about doing it in Enterprise Manager's Design Table section? I can
see all the columns with their data types at once... if I changed multiple
data types there, does SQL Server rebuild the table for each column, or does
do everything in one shot? Appreciate the help!
"Chandra" wrote:
[vbcol=seagreen]
> well! u dont have that flexibility
> u can add multiple columns using ALTER
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
>
> "Shishir Viriyala" wrote:
|||it rebuilds for each column.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Shishir Viriyala" wrote:
[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
|||Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
to execute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...[vbcol=seagreen]
> Okay, how about doing it in Enterprise Manager's Design Table section? I can
> see all the columns with their data types at once... if I changed multiple
> data types there, does SQL Server rebuild the table for each column, or does
> do everything in one shot? Appreciate the help!
> "Chandra" wrote:
|||Aha! Thanks for the tip Tibor! I have included what the change script was
showing me. The two columns for which I changed data types were CASEID and
DX. Note that both those columns are being converted in one statement.
Using EP is definitely the way to go!
Thanks to you and Chandra for the input......
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_ip_adm_dx
(
CASEID int NULL,
DX varchar(5) NULL,
DXNO int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.ip_adm_dx)
EXEC('INSERT INTO dbo.Tmp_ip_adm_dx (CASEID, DX, DXNO)
SELECT CONVERT(int, CASEID), CONVERT(varchar(5), DX), DXNO FROM
dbo.ip_adm_dx TABLOCKX')
GO
DROP TABLE dbo.ip_adm_dx
GO
EXECUTE sp_rename N'dbo.Tmp_ip_adm_dx', N'ip_adm_dx', 'OBJECT'
GO
COMMIT
"Tibor Karaszi" wrote:

> Check and see :-). Press the "save change script" button and you will see the SQL that EM is about
> to execute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in message
> news:F183A811-5BBF-4BA0-9E6D-BE671FDB8837@.microsoft.com...
>
|||On Fri, 5 Aug 2005 08:15:38 -0700, Shishir Viriyala wrote:

>Okay, how about doing it in Enterprise Manager's Design Table section? I can
>see all the columns with their data types at once... if I changed multiple
>data types there, does SQL Server rebuild the table for each column, or does
>do everything in one shot? Appreciate the help!
Hi Shishir,
Neither. Enterprise Manager will simply create a new table, copy over
the data, then drop the old table. If the table is involved in any
foreign key relationships, it will drop and recreate them as well. And
if the table has any triggers, they too will be recreated.
Copying the data over to a new table might be the best strategy in your
specific case, but in general, the methods chosen by EM to apply changes
tend to be overkill. (And very slow on large tables!)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Question on case

I am trying to run a query with a case statement in it. The column has some null values and I want to replace the null with a character string.
ex:
case my_column when null then 'char_string' else my_column end 'column'

I run it and the nulls are still in my_column. Does case not work on null values this way?
Thanks.Originally posted by exdter
I am trying to run a query with a case statement in it. The column has some null values and I want to replace the null with a character string.
ex:
case my_column when null then 'char_string' else my_column end 'column'

I run it and the nulls are still in my_column. Does case not work on null values this way?
Thanks.

Try this:

case when my_column is null then 'char_string' else my_column end 'column'|||Can I use an isnull here? Doesn't matter. Your solution worked. Thanks alot.|||Use isull(mycolumn,'CharString')|||Thats what I thought too but I used Snails method already.
Thanks.|||I tried it this way too and it works fine and is much neater.
Thanks.|||coalesce(mycolumn,'charstring') is also much neater and it uses standard sql

;)|||Thanks r937!

Question on attribute with more than one column

Hi,all experts here,

Thank you very much for your kind attention.

I am having a question on attribute with more than one column in a cube. What is the benefitial to have a attribute with more than one column? I am confused for this conception. And could please any experts here shed me any light on that.

Thanks a lot in advance for your help and I am looking forward to hearing from you.

With best regards,

Yours sincerely,

I'm not sure if this is the question that you are asking, but, here's a stab at the answer...

Often multiple columns in a relational table can be used to uniquely identify each member in an attribute. For example - months are repeated in years. If you have a Month column in your table that lists all the months by their names - Jan, Feb and so on - you won't be able to distinguish between months belonging to year 2003 and months belonging to year 2004. Now if you have another column (say Year) that lists the year, then, you could have a single attribute that has a composite key made up of the columns Year and Month.

|||

Hi, Bala,

Thanks a lot.

With best regards,

Yours sincerely,

Question on attribute with more than one column

Hi,all experts here,

Thank you very much for your kind attention.

I am having a question on attribute with more than one column in a cube. What is the benefitial to have a attribute with more than one column? I am confused for this conception. And could please any experts here shed me any light on that.

Thanks a lot in advance for your help and I am looking forward to hearing from you.

With best regards,

Yours sincerely,

I'm not sure if this is the question that you are asking, but, here's a stab at the answer...

Often multiple columns in a relational table can be used to uniquely identify each member in an attribute. For example - months are repeated in years. If you have a Month column in your table that lists all the months by their names - Jan, Feb and so on - you won't be able to distinguish between months belonging to year 2003 and months belonging to year 2004. Now if you have another column (say Year) that lists the year, then, you could have a single attribute that has a composite key made up of the columns Year and Month.

|||

Hi, Bala,

Thanks a lot.

With best regards,

Yours sincerely,