question with trigger

i have a table which has columns scheduledrent,actualrent beside other columns.
the actual rent is based on some calculations but uses scheduledrent for the calculations.

everytime there is a change to scheduledrent, the actualrent should also change. so if i write a trigger saying
if updated(scheduledrent)
do some calculations and change the actualrent..using an update stmt.

would it make the change only for that row or for all the rows in the table? if it does it for all the rows, how do i make it apply only to that row. how should i have my update stmt in such a case. my primarykey is a combination of 4 rows. so can i get all the 4 values into some variables and while i do the update do it on that condition ? or is there any easier way of doing it.

thanks.
D.Inside the trigger code you have access to a logical table named "inserted". You can run your update logic with that table and it will only update the rows that were updated. The logical table "inserted" is available for update triggers and, naturally enough, insert triggers.

The logical table "deleted" is available for delete triggers and shows the records to be deleted. In an update trigger the "deleted" table shows the original values before they are to be changed.|||ok it works if i change it to inserted.
have one q though. i have 10 ppl accessing the db at any time. so if all of them make an update to the db (not to the same record though) each one will have an entry in the logical inserted table. so how would the system differentiate between them, since they all login to sql server through the same asp.net account.

thanks for the tip.|||The inserted logical table will only be for the record(s) that triggered that specific update. So everyone will have their own inserted logical tables and won't be stepping on one another.|||oh so sql server creates an inserted table for each user ?
but how does the sql server differentiate between users ?

lets say userA logged onto mysite and made an update to a row. the row gets inserted into the inserted table. userB also logs in at the same time and makes an update to another row. the new row also gets into the inserted table. now both these users are logged into the sqlserver through the same ASP.NET account. so how does sql server differentiate betwen userA and userB and their updated rows?

thanks again.|||It's a "logical" table meaning that it acts like a table but isn't really one. It is there to allow the trigger code to inspect the state of the old and new records so that the trigger code can take whatever action is appropriate. The logical table inside one trigger event isn't visible or accessible to the logical table in any other trigger event, even for the same user logged on multiple times simultaneously updating different record(s).|||i dont think i have understood completely. do you know any article that xplains in detail how the inserted table actually works...?
thanks anyway. will leave it for now. hope it works. will get back incase i have any prb.

thanks McMurdoStation.sql

Question with SQL String

I have this code below to parse the string ''00120212~pendin~mod pen~ria te~3/6/2007 3:51:49 pm'' into Multiple columns.

Here is the code, but the code is splititng the columns incorrectly.

I need it to appear as

col1 col2 col3 col4 col5
00120212 pendin mod pen ria te 3/6/2007 3:51:49 pm
Can someone pl assist with this code below.

-

DECLARE @.str varchar(8000)
SET @.str = '00120212~pendin~mod pen~ria te~3/6/2007 3:51:49 pm'

DECLARE @.columns TABLE (
col1 varchar(8000)
,col2 varchar(8000)
,col3 varchar(8000)
)

SET @.str = LTrim(RTrim(@.str))

DECLARE @.col1 int
,@.col2 int
,@.col3 int

SET @.col1 = CharIndex('~', @.str, 0)
SET @.col2 = CharIndex('~', @.str, @.col1 + 1)
SET @.col3 = CharIndex('~', @.str, @.col2 + 1)

INSERT INTO @.columns
VALUES (
SubString(@.str, 2, @.col1 - 2)
,SubString(@.str, @.col1 + 3, Len(@.str) - @.col2 - 4)
,SubString(@.str, @.col2 + 3, Len(@.str) - @.col3 - 1)
)

SELECT * FROM @.columns

You may find Jen Suessmeyer's Split function to be very useful for this situation.

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||

Please check out the link below:

http://www.sommarskog.se/arrays-in-sql.html

It contains few TVFs that can be used to split a string based on a delimiter. You can use it along with PIVOT for example to get the individual values easily like:

SELECT p.[1], p.[2], p.[3], p.[4], p.[5]

FROM split_str(@.str, '~') AS t

PIVOT (min(t.value) for t.idx in ([1], [2], [3], [4], [5])) as p

Alternatively, you can do these type of operations easily on the client side and send the values individually. This way you can use SQL for what it is supposed to do.

|||

This is by code below, Delimiter is ~. I need to split the values, into multilple field , based on the ~ being the delimiter. How do I loop throu this string, until the end of the string, and then split it one - by-one into multiple fileds? PL ADVISE?

Declare @.Str Varchar(1000),@.I Int

set @.str='0001232~PENDING~MOD PENDING~Trad Jane~3/29/2007 5:03:30 PM~0001232~PENDING~MODIFICATION PENDING~ Jane Delder~3/29/2007 5:05:06 PM~0001232~PENDING~Approved~ Mon Savy~3/29/2007 5:05:27 PM~0001232~PEND'
SET @.str = LTrim(RTrim(@.str))

DECLARE @.columns TABLE (
LoanNum varchar(8000)
,ConvertedFromStatus varchar(8000)
,ConvertedToStatus varchar(8000)
,ConvertedName varchar(8000)
,StatusChangedDate text
)

begin
Begin
DECLARE @.col1 int
,@.col2 int
,@.col3 int,
@.col4 int

SET @.col1 = CharIndex('~', @.str, 0)
SET @.col2 = CharIndex('~', @.str, @.col1 + 1)
SET @.col3 = CharIndex('~', @.str, @.col2 + 1)
SET @.col4 = CharIndex('~', @.str, @.col3 + 1)
--print @.col1
--print @.col2
--print @.col3
--print @.col4

INSERT INTO @.columns
VALUES (
Left(@.Str, @.Col1 - 1),
SubString(@.Str, @.Col1 + 1, @.col2 - @.Col1 - 1),
SubString(@.Str, @.Col2 + 1, @.col3 - @.Col2 - 1),
SubString(@.Str, @.Col3 + 1, @.col4 - @.Col3 - 1),
SubString(@.Str, @.Col4 + 1, @.col4 )
)
End

--Select @.I = 0

End
SELECT * FROM @.columns

|||Does the Split function I previously posted for you not work properly?

Question when subtracting two queries

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.Problem resolved. Thinking was off.

Quote:

Originally Posted by parkc

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.

|||

Quote:

Originally Posted by parkc

I want to return the difference number of records.

Here are the queries:

--subtracting columns with columns and descriptions (columns - columns and descriptions)
--difference of 30 records
--243
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on t.object_id = c.object_id
and not exists
(
--213
select
h.name as 'DataBase Name'
,t.name as 'Table Name'
,c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t
on s.major_id = t.object_id
inner join sys.schemas h
on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
)

I'm not getting anything back. I should be getting back 30 records that have null descriptions.

Please help.

TRY below query and check if that works...

select * from
(
select h.name as 'DataBase Name',t.name as 'Table Name',c.name as 'Column Name'
from sys.tables t
inner join sys.schemas h on h.schema_id = t.schema_id
inner join sys.columns c on t.object_id = c.object_id
) x
left outer join
(
--213
select h.name as 'DataBase Name',t.name as 'Table Name',c.name as 'Column Name'
from sys.extended_properties s
inner join sys.tables t on s.major_id = t.object_id
inner join sys.schemas h on h.schema_id = t.schema_id
inner join sys.columns c
on s.major_id = c.object_id
and s.minor_id = c.column_id
) y on x.[DataBase Name] = y.[DataBase Name]
where y.[DataBase Name] is null

question on the primary key

Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?
I take it you mean that the 4 column together comprises the PK (i.e., a composite PK, you can only
have one PK but it can be over several columns).
Technically, it doesn't matter. However, when humans read database schemas, it seems natural to have
the PK as the left-most columns. I.e., it is an esthetic issue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in message
news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> Let say I have 10 columns and 4 of them is a primary keys.
> The question is where should I place these 4 columns (with
> primary keys)? Is it at the most left?
|||Hi,Tibor
Did the OP mean also sorting columns within PK? I mean if you have primary
key on A,B,C,D ,does it matter a place of the column?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I take it you mean that the 4 column together comprises the PK (i.e., a
composite PK, you can only
> have one PK but it can be over several columns).
> Technically, it doesn't matter. However, when humans read database
schemas, it seems natural to have
> the PK as the left-most columns. I.e., it is an esthetic issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message
> news:084e01c48362$1c886130$3a01280a@.phx.gbl...
>
|||The first column in the composite index (key) should be the one, you most
often query on.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message news:084e01c48362$1c886130$3a01280a@.phx.gbl...
Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?
|||Good point Uri, I read the question as "between all columns in the table". Dan posted a good reply
if the question is "order within the PK columns".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:u$ThY62gEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi,Tibor
> Did the OP mean also sorting columns within PK? I mean if you have primary
> key on A,B,C,D ,does it matter a place of the column?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> composite PK, you can only
> schemas, it seems natural to have
> message
>

question on the primary key

Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?I take it you mean that the 4 column together comprises the PK (i.e., a comp
osite PK, you can only
have one PK but it can be over several columns).
Technically, it doesn't matter. However, when humans read database schemas,
it seems natural to have
the PK as the left-most columns. I.e., it is an esthetic issue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in mess
age
news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> Let say I have 10 columns and 4 of them is a primary keys.
> The question is where should I place these 4 columns (with
> primary keys)? Is it at the most left?|||Hi,Tibor
Did the OP mean also sorting columns within PK? I mean if you have primary
key on A,B,C,D ,does it matter a place of the column?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I take it you mean that the 4 column together comprises the PK (i.e., a
composite PK, you can only
> have one PK but it can be over several columns).
> Technically, it doesn't matter. However, when humans read database
schemas, it seems natural to have
> the PK as the left-most columns. I.e., it is an esthetic issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message
> news:084e01c48362$1c886130$3a01280a@.phx.gbl...
>|||The first column in the composite index (key) should be the one, you most
often query on.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message news:084e01c48362$1c886130$3a01280a@.phx.gbl...
Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?|||Good point Uri, I read the question as "between all columns in the table". D
an posted a good reply
if the question is "order within the PK columns".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:u$ThY62gEHA.636@.TK2MSFTNGP12.phx.gbl..
.
> Hi,Tibor
> Did the OP mean also sorting columns within PK? I mean if you have primar
y
> key on A,B,C,D ,does it matter a place of the column?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> composite PK, you can only
> schemas, it seems natural to have
> message
>

question on the primary key

Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?I take it you mean that the 4 column together comprises the PK (i.e., a composite PK, you can only
have one PK but it can be over several columns).
Technically, it doesn't matter. However, when humans read database schemas, it seems natural to have
the PK as the left-most columns. I.e., it is an esthetic issue.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in message
news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> Let say I have 10 columns and 4 of them is a primary keys.
> The question is where should I place these 4 columns (with
> primary keys)? Is it at the most left?|||Hi,Tibor
Did the OP mean also sorting columns within PK? I mean if you have primary
key on A,B,C,D ,does it matter a place of the column?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I take it you mean that the 4 column together comprises the PK (i.e., a
composite PK, you can only
> have one PK but it can be over several columns).
> Technically, it doesn't matter. However, when humans read database
schemas, it seems natural to have
> the PK as the left-most columns. I.e., it is an esthetic issue.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message
> news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> > Let say I have 10 columns and 4 of them is a primary keys.
> > The question is where should I place these 4 columns (with
> > primary keys)? Is it at the most left?
>|||The first column in the composite index (key) should be the one, you most
often query on.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
message news:084e01c48362$1c886130$3a01280a@.phx.gbl...
Let say I have 10 columns and 4 of them is a primary keys.
The question is where should I place these 4 columns (with
primary keys)? Is it at the most left?|||Good point Uri, I read the question as "between all columns in the table". Dan posted a good reply
if the question is "order within the PK columns".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:u$ThY62gEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi,Tibor
> Did the OP mean also sorting columns within PK? I mean if you have primary
> key on A,B,C,D ,does it matter a place of the column?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eRjvV02gEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > I take it you mean that the 4 column together comprises the PK (i.e., a
> composite PK, you can only
> > have one PK but it can be over several columns).
> >
> > Technically, it doesn't matter. However, when humans read database
> schemas, it seems natural to have
> > the PK as the left-most columns. I.e., it is an esthetic issue.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "mrizal@.padusoft.com.my" <anonymous@.discussions.microsoft.com> wrote in
> message
> > news:084e01c48362$1c886130$3a01280a@.phx.gbl...
> > > Let say I have 10 columns and 4 of them is a primary keys.
> > > The question is where should I place these 4 columns (with
> > > primary keys)? Is it at the most left?
> >
> >
>

Question on SQL tables

Hi everyone,

I′m starting using SQL 2005 on visual studio 2005, and I have a question:

I have a table named employees in which I have 2 columns, one named "last used" which has the smalldatetime data type and the other named "salary" which has the decimal data type.

Now, I want to use a Select statement, to display in a new table 2 new columns, one with the sum of the salaries that correspond to the current month and the other with the sum of the values that belong to the previous month. I mean by this, all the salaries from july should be added up and be displayed in one column and all the ones from june should be added and displayed in the other column.

Can someone help me please?

Thanks in advance

Using this data set

LastUsed Salary 5/1/2007 0:00 150 5/1/2007 0:00 250 5/1/2007 0:00 350 6/1/2007 0:00 100 6/1/2007 0:00 200 6/1/2007 0:00 300 7/1/2007 0:00 50 7/1/2007 0:00 150 7/1/2007 0:00 250

..and this query

select

sum(case month(lastused) when month(getdate()) then salary end) as CurrentMonthSalary

,sum(case month(lastused) when month(getdate()) -1 then salary end) as PrevMonthSalary

from dbo.salary

...I return these results

CurrentMonthSalary PrevMonthSalary 450 600

I believe that's what you are looking for.

Tim

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 Identity columns in 2005.

Howdy all. I used to frequent this forum and hope all has been well with
Paul, Hillary, and any other regulars I may have missed. Anyways, I just set
up Transaction, Immediate Updating replication on two 2005 boxes,
replicating the adventureWorks DB. One table in particular
(Person.AddressType) contains an Identity coulmn, and I did nothing to alter
it in any way. Anyways, once replication was fully configured, I inserted a
row into the table on the Subscriber, and it was assigned Identity value
20009. Questions:
1. It's been a while since I've done anything with Immediate Updating stuff,
but it seems to me that in 2000 I would have had to manually configure the
Identity value on the Subscriber box to use a different range of Identity
values than on the Publisher, right?
2. I looked on the Subscriber table Identity column, expecting the Seed to
be 20009, but it's not, it's 1! How can it be getting the value of 20009 if
the Seed is still set to 1?
TIA, ChrisR
Hi Chris - good to see you're back here!
The automatic identity range management is one of the defaults that has
changed across versions.
The seed and increment is the same behaviour though across versions. This
really refers to the table creation on the publisher. Run DBCC
CHECKIDENT(tablename) at the subscriber to get the new value (or look at the
check constraints on the subscriber's table). I'm guessing that it made
sense that it remains as the original because the new one would at some time
become out of date anyway when a new range is requested, so the easiest
solution was to just take the old table script.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul. But why is it "ignoring" the value of 1 that I see in
Management Studio?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23SpgBYBMHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Hi Chris - good to see you're back here!
> The automatic identity range management is one of the defaults that has
> changed across versions.
> The seed and increment is the same behaviour though across versions. This
> really refers to the table creation on the publisher. Run DBCC
> CHECKIDENT(tablename) at the subscriber to get the new value (or look at
> the check constraints on the subscriber's table). I'm guessing that it
> made sense that it remains as the original because the new one would at
> some time become out of date anyway when a new range is requested, so the
> easiest solution was to just take the old table script.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||The idenity value is being reset on initialization using DBCC CHECKIDENT, so
the defined value is now 'meaningless'.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eWuRb7BMHHA.4244@.TK2MSFTNGP04.phx.gbl...
> The idenity value is being reset on initialization using DBCC CHECKIDENT,
> so the defined value is now 'meaningless'.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Question on Idenity Columns

Is there a way to query any system tables, like sysobjects, for User tables
with Identity columns?
We have a SQL2000 server that we want to replicate. One of my tasks is to
identify all tables with Identity columns that are missing the 'not for
replication' clause.

Thanks,

JoeyDJoeyD (joeydba@.yahoo.com) writes:
> Is there a way to query any system tables, like sysobjects, for User
> tables with Identity columns? We have a SQL2000 server that we want to
> replicate. One of my tasks is to identify all tables with Identity
> columns that are missing the 'not for replication' clause.

SELECT object_name(id), name
FROM syscolumns
WHERE columnproperty(id, name, 'IsIdentity') = 1
AND columnproperty(id, name, 'IsIdNotForRepl') = 1

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

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

JoeyD

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns961D3C9067A9Yazorman@.127.0.0.1...
> JoeyD (joeydba@.yahoo.com) writes:
> > Is there a way to query any system tables, like sysobjects, for User
> > tables with Identity columns? We have a SQL2000 server that we want to
> > replicate. One of my tasks is to identify all tables with Identity
> > columns that are missing the 'not for replication' clause.
> SELECT object_name(id), name
> FROM syscolumns
> WHERE columnproperty(id, name, 'IsIdentity') = 1
> AND columnproperty(id, name, 'IsIdNotForRepl') = 1
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

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 design with Identity columns

Normally when I have a "Many-toMany" or linkage table where the primary key consists of a foreign key from two different tables, I do not bother to make a separate identiy column instead.
Does anyone see a reason why an identity column would be more or less desireable ? For example
Table Person
PK - PersonID
Table Car
PK - CarID
Table PersonCar
PK (PersonID, CarID)
Or would it be better to make an Identity Column such as PersonCarID so then the table would look like the following:
Table PersonCar
PK - (PersonCarID)
FK - PersonID
FK - CarID
Create Unique Constraint on Person and CarID
Any feedback is appreciatedHave a "quick" read of...
http://forums.asp.net/1015568/ShowPost.aspx
|||

mru22 wrote:

Does anyone see a reason why an identity column would be more or less desireable ?

My opinion, which I am not willing to defend to death, mind you, isthat an additional identity column is preferable. I've learned tohate composite keys on tables. I prefer my UPDATE, DELETE, etc.statements to have a single variable in the WHERE condition. Whencommunicating a specific record to another developer, or making a noteto myself, it is a lot easier to be able to refer to a single value asa key. Database purists will certainly scoff, and perhaps evenrise up in outrage. But the additional identity column has servedme well so far.
|||

Another typical counter-identity worry is that as they auto-inc then all the new data 'bottlenecks' at the end of the indexes (when your identity is involved in an index). Although that can work for you depending upon your solution.

|||I'm leaning toward having the additiona identity field. If I do so I think I would make it non-clustered since I would never be doing any joins on i and it would never been used in any queries.
I have heard pros and cons for having the identity remain Clustered but I think it would best serve me to move it to one of the other columns which is less unique. Or maybe only have the PK as a clustered index and no other indexes on the table since there is only 3 columns total and the optimizer may prefer index scans vs. index seek anyway.|||

mru22 wrote:

I would never be doing any joins on i and it would never been used in any queries.

So then what will be using the field for?
|||

It would just be used to have the PK represent a single column vs. multi column. No other reason, that's why I was contemplating using it in the first place. Typically I have not when I have "many to many" tables.
So instead i would just have the two keys, one from each of the parent tables. With that being said, I notice that the OR Mappers seem to work more easily when you define a single column primary key and if I end up going that route then I may consider the single column pk.

|||

mru22 wrote:

With that being said, I notice that the OR Mappersseem to work more easily when you define a single column primary keyand if I end up going that route then I may consider the single columnpk.

In that case, you would indeed be using the field in queries, behindthe scenes. Adding a field which would never be used orreferenced of course would make no sense, that's why I questioned you.:-)
|||

mru22 wrote:

Normally when I have a "Many-toMany" or linkage table where the primary key consists of a foreign key from two different tables, I do not bother to make a separate identiy column instead.
Does anyone see a reason why an identity column would be more or less desireable ?

The fact that you're asking this question shows that you have a total lack of understanding on relational databases. You need to go to back to the fundamentals. Actually, I'm guessing you never started at the fundamentals and just dived right into SQL Server and started programming. That's OK, a lot of people do that.
If you are being paid to program, you have a professional obligation to do it right instead of making it up as you go along. I'm sure you wouldn't be too happy if all your electrician had a multimeter, wire trimmers, and eletrical tape but didn't know his Volts from his Watts.
Go pick up Date's INTRODUCTION TO DATABASE SYSTEMS. You can get an old edition (it's a text book and doesn't change too much) for $5 or so.|||

Sorry but I do have a complete understanding of databases. And no I did not just pick SQL server and start programming. Your insults are not only a waste of posting to this board by providing no true value to the discussion but that's ok because there will always be people like you.
As far as this thread why do you go ahead and explain which one you prefer and why or would you rather reply with another insult ?

|||

tmorton wrote:

mru22 wrote:

With that being said, I notice that the OR Mappers seem to work more easily when you define a single column primary key and if I end up going that route then I may consider the single column pk.

In that case, you would indeed be using the field in queries, behind the scenes. Adding a field which would never be used or referenced of course would make no sense, that's why I questioned you. :-)

I agree completely and again that is how I normally do it. But I have seen others add the identity column so I just wondered if there was some underlying reason for doing so. I think not in this case other than they wanted to have a one column key.|||I suspect it is as simple as wanting a one column key 'cause it makes using things like object dictionaries far easier when you've only got to worry about one value. I know I do that.|||

mru22 wrote:

Sorry but I do have a complete understanding of databases. And no I did not just pick SQL server and start programming. Your insults are not only a waste of posting to this board by providing no true value to the discussion but that's ok because there will always be people like you.
As far as this thread why do you go ahead and explain which one you prefer and why or would you rather reply with another insult ?

A "complete understanding of databases"? Perhaps you mean CODASYL databases? Or XML databases? Certainly not relational.
In the relational world, we don't do things like add artificial pointers to relations. In fact, that's a fundamental concept of relational databases: no pointers. The whole point of relational databases is to use values on their own to key and relate. Not meaningless pointers.
Your sample schema shows you don't get that, and therefore lack an understanding of that fundamental concept. CarID? What on earth is that? The standard that the rest of the world is VIN. It's right there, physically stamped on every car. In multiple places. It cannot be changed.
Ditto for PersonID. First off, the name "Person" is a very poor naming choice for an individual: companies have Employees and Customers/Clients, schools have Faculty and Students. No organization has generic "Persons." When you model what the actual data is, you find that they already have identifiers there for you. Employee_Num or SSN. Student_Num. Etc. No need to use an artificial pointer.
As I stated earlier, you do not understand these fundamental concepts of relational databases. If you did, you would never need to ask such a question. But what's worse is that you *think* you do.
I do not intend to be insulting. Your question is tantamount to someone asking on forums.carpentry.net: "I normally use a hacksaw when I'm building stuff for my customers ... but what tool do you guys think I should use? A hacksaw or a mitre box? I'm trying to pound in a nail ..."|||Alex makes some fair points, however, I'm sure he'll agree that even pure database design will conceed to the practical natures of a solution, for example de-normalised data. What I'm poining out is that there is *never* a abs. correct answer, the only true answer is, "it depends". However, I do think it would be well worth your time to invest in an introduction to relational database design, at least when you take the decision to use an artifical key you'll know the reasons why ;)
|||So you are saying in Relational Databases meaningless keys such as Identity Columns are bad? VIN For example includes characters thus cannot be an integer or numeric data type.
I would not want VIN as the PK. I typically avoid varchar or char datatypes as primary keys. And yes I typically use identity columns as Primary Keys
In your carID example I would Instead put VIN as unique and would add an Identity column as the PK. Furthermore for anytable that had the a reference to the Car table, It would store a smaller more efficient integer as opposed to varchar. I know that storage isn't much of an issue today as say 10-20 years ago but I still find that a more efficient design.
Plus what if the vin number was incorrect and had to be changed to another unique vin number? This way the change would not have to propogate through any tables that referenced car other than the car table. Same with Employee SSN. While that would be numeric by removing dashes I still prefer a meaningless key.
In your person example you indicated taht What kind of name is person ? Well Employee is a person, Student is a person. You would either have a type table to know which is which but what happens is a person can be both an employee and student and you need to differentiate ? You sure are not going to make two records, so either you have many-to-many table between Person and Person Type or you would have Person and then Employee and Student tables where the PK from the Person is also the PK in the Employee and student table. I would do this if there many attributes that they did not share in common otherwise having the many to many with types would make more sense if they shared all other attributes in common.
I understand that many do not like synthetic keys but I often prefer to keep my PK meaningless when Possible. I do understand the arguments against it but do not always agree especially when using non-integer values as the Primary key.

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,