Chunked Delete?

I can't truncate a table because I'd lose the Identity columns that
are referenced in other tables, but I do need to delete all 2 million
rows in there.
Is there a way to recusrively delete rows in say 1,000-row chunks to
keep the transactions small and DB load lighter?
Thanks.See if this thread helps:
http://groups-beta.google.com/group...
a0488274c6e93
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"xenophon" <xenophon@.online.nospam> wrote in message
news:8qhga11qephoebnh1afec88usol8rulg44@.
4ax.com...
I can't truncate a table because I'd lose the Identity columns that
are referenced in other tables, but I do need to delete all 2 million
rows in there.
Is there a way to recusrively delete rows in say 1,000-row chunks to
keep the transactions small and DB load lighter?
Thanks.sqlsql

Choosing field from same row based on an aggregate.

I have a table with two columns. Let's call them Value and Hour. Looks like this:

Value Hour

4 9:00

3 11:00

6 2:00

2 12:00

I want to be able to make a total line and give the Max(Value) and the time it happened. What would be the function to get the Hour value based on the Max(Value). Just for example, for this one it would be Max(Value) = 6 and it's Hour would be 2:00.

Hi,

what about getting this right back from the database system with a query ?

SELECT Hour,MAX(Value)
From SomeTable
Group by Hour

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I'm actually getting my data from a Custom Data Processing extension. I would need a way to get it with a function straight out of the resultant dataset.|||

Can't you make a calculated field with the expression = Max(Value)?

That can be put in the footer of the table (which will display '6' in this example). To get the hour belonging to the value with an expression ...I'm not so sure how to do that ... maybe with a switch expression, based on the textbox where you show the MAX(Value)?

choosing a the column that is not null

I have two columns in a table. For every record, one column contains a value and the other one is null. Which one is filled and which one is empty differs per record. In my query I only want to retrieve the column that contains the value, but not the other one. Is it possible to do this with a standard tsql function? Other solutions are also welcome.REfer IS [NOT] NULL from BOL.|||I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.|||Originally posted by jora
I tried to use "is null", "not null" and stuff like that, but it did not get the desired results. Meanwhile I found the functions "ISNULL()" and "NULLIF()". As I can see it now, these provide the correct results.

You might want to take a look at the function COALESCE() which is designed to solve the problem you have.

The function is also part of the ANSI standard and is supported by many RDBMS.

choice of column for clustered index

I often founds 2 recommendations that sounds contradicting to me,
regarding what types of columns should be chosen for a clustered index
(in OLTP environment with lots of inserts):
1)avoid identity column, it will cause inserts to be slowed since they
will compete the same disk area at the end of the table (hot spot).
Instead, use a column whose new value can be at any part of the table
2)use sequential column (like identity one), so that new inserts only
happen at the end of the table and don't cause row migrations (when a
row inserted forces the next rows to move to a new page)
Can anyone give me a more sounding judgement of each of these 2
choices? I'm curious to know in which scenario, which choice is better
than the other, and what the cures are. Correct me if I'm wrong, I feel
that "row migration" is more fearful than "hot spot"
thanks,
TamUnless you're writing a million new rows a day, I doubt the hot spot concern
is valid on today's hardware. Obviously there will be a threshold but for
most applications I can envision this should be a minimal concern.
I have witnessed cases where fragmentation and page splitting, on the other
hand, has caused abysmal performance.
I can't really think of a situation where you'd rather jab new data in the
middle of a page than tack it on the end, unless you were just stuffing
every single transaction on a very busy system into an audit table that you
purge regularly and that you're rarely going to query, in which case, who
cares which way you go.
A
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1125521936.674430.273880@.g49g2000cwa.googlegroups.com...
>I often founds 2 recommendations that sounds contradicting to me,
> regarding what types of columns should be chosen for a clustered index
> (in OLTP environment with lots of inserts):
> 1)avoid identity column, it will cause inserts to be slowed since they
> will compete the same disk area at the end of the table (hot spot).
> Instead, use a column whose new value can be at any part of the table
> 2)use sequential column (like identity one), so that new inserts only
> happen at the end of the table and don't cause row migrations (when a
> row inserted forces the next rows to move to a new page)
> Can anyone give me a more sounding judgement of each of these 2
> choices? I'm curious to know in which scenario, which choice is better
> than the other, and what the cures are. Correct me if I'm wrong, I feel
> that "row migration" is more fearful than "hot spot"
> thanks,
> Tam
>|||See if this helps:
Tips on Optimizing SQL Server Clustered Indexes
http://www.sql-server-performance.c...red_indexes.asp
AMB
"Tam Vu" wrote:

> I often founds 2 recommendations that sounds contradicting to me,
> regarding what types of columns should be chosen for a clustered index
> (in OLTP environment with lots of inserts):
> 1)avoid identity column, it will cause inserts to be slowed since they
> will compete the same disk area at the end of the table (hot spot).
> Instead, use a column whose new value can be at any part of the table
> 2)use sequential column (like identity one), so that new inserts only
> happen at the end of the table and don't cause row migrations (when a
> row inserted forces the next rows to move to a new page)
> Can anyone give me a more sounding judgement of each of these 2
> choices? I'm curious to know in which scenario, which choice is better
> than the other, and what the cures are. Correct me if I'm wrong, I feel
> that "row migration" is more fearful than "hot spot"
> thanks,
> Tam
>|||> Unless you're writing a million new rows a day,
Wow, did I really say "day" there? Eep. In my experience, a hot spot comes
at a far greater volume than that. Then again, I have been spoiled with
must faster hardware than I had access to in college. :-)

checksums and data types

I'm creating a checksum column in a table that is to be calculated over several columns within the table.

One of the columns to be included in the checksum formula has a data type ntext.

But on trying to complete this new table design (or similarly using alter table in QA) - both return an error stating that the data type is invalid for the checksum function.

This happens for both ntext and text data types.

Can anyone tell me if there is a way round this without having to change the data type - or the valid data types that can be used for the checksum funciton?

Also reasons why would be helpful!

Thanksplease ignore - http://www.dbforums.com/t989557.html shows this not to be possible...

nevermind|||you might want to try something like so:

SELECT checksum(col1,col2,CAST(CAST(col3 as varchar(1)) as int))
FROM testTable

I am not sure if this totally works. Text and Ntext are meant to hold large amounts of text data like notes field in a customer service application. There is no implicit data conversion between int and ntext\text in sql server because that is just one of the rules and it would'nt make much since do so. To tell the truth it sounds like your problem is a design issue. However you can explicitly convert data types as shown above but please keep in mind if you try to cast character data in col3 above to an int, you will recieve an error. So you might have to add an IsNumeric in there as well.|||CHECKSUM works with non-numeric data, so there is no need to recast as INT in your formula.

Though I'm still not sure that is going to give him what he needs...|||Why are you doing this? To enforce data integrity upon INSERT/UPDATE? Or to support some business rule? Either way you're already using a database, so the answer should be in design, not checksum-based tricks.|||hey trotsky!!
calm down.|||I'd love to hear the reason behind this... I can't for the life of me figure out why you might want/need to do it. I'm also with rdjabarov, and think that this smells very strongly of a high GQ (geek quotient) workaround for a case of poor relational design!

-PatP|||that's twice that you have agreed with RDjabarov.
hmmmmmmm is the feud over?

:D|||Feud? Did I miss a meeting?

-PatP|||must have been the coma. when i first go here you guys would go at it like turtles and bunnies.

checks or constraints?

My table has two columns called offer_date and availability_date

and I want to put a constraint that availability_date should

always be earlier than offer date and if values are inserted into

the table it should return DBMS_server_output:

"offer_date cannot be earlier than availability_date"

Name Null? Type
PROPERTY_ID VARCHAR2(10)
TYPE VARCHAR2(10)
ASKING_PRICE NUMBER(10,2)
SELLING_PRICE NUMBER(10,2)
OFFER_DATE DATE
AVAILABILITY_DATE DATEYou will most likely have to implement using a TRIGGER since you have a specific message you want to display. However, since this is a non-dbms specific area, and not all DBMSs will allow you to implement a trigger, I will not recommend any specific trigger syntax.|||An alternative could be to store the difference of the two instead of the availability_date (possibly with a view on top of this, returning the two dates).
Then you could use a simple check constraint ( >= 0) on that difference.|||I'm 99.9% certain you are using Oracle, right?

You can create a check constraint like this:
alter table x add constraint dates_chk check (offer_date >= availability_date);
However, that will not give you the specific error message you mentioned, it will give you:

ORA-02290: check constraint (MYSCHEMA.DATES_CHK) violated

It is easy to trap that error message in an application, see what constraint was violated, and present a better message. I would not advocate using a trigger instead merely to allow a bespoke message. (Aside: it would be nice if Oracle allowed you to define bespoke error messages for each constraint, wouldn't it!)

I note you said this: "it should return DBMS_server_output [the message]". I presume you mean DBMS_OUTPUT.PUT_LINE? If so, that is wrong: never use DBMS_OUTPUT to handle error messages, it is only suitable for simple debugging etc. If you were writing a trigger you should call raise_application_error like this:
raise_application_error(-20001,'offer_date cannot be earlier than availability_date');

Checking the datatype of a columns

Hello,

I have the following sql statement

Code Snippet

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

I am converting a nvarchar to a datetime on one of the columns in the table. However, I don't want to execute this if the conversion has already executed.

I was thinking of having a if statement that if the column is not a datetime then alter the column.

I am unsure how to write the if statement to check for the data type of that column.

Many thanks for any help,

Steve

If you're intention is to ultimately convert the datatype, what's preventing you from initially setting the datatype as datetime?

I know this doesn't answer your question, but I'm curious.

Adamus

|||Hello,

The table was created initially with a nvarchar. This is a live database and the dates that have already been entered have to be formated in order for the alter column will work.

If the customer runs this script more than once, I don't want to have to execute the alter statement again.

Many thanks,

Steve
|||

There's really not a problem if that statement executes EVEN if the datatype has been previously changed.

However, if you need to:

Code Snippet

IF NOT EXISTS
( SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE ( TABLE_NAME = 'OtherCall'
AND COLUMN_NAME = 'Date'
AND DATA_TYPE = 'datetime'
)
)
BEGIN
UPDATE OtherCall

SET [Date] = convert( nvarchar(50), convert( datetime, [Date], 103), 111)

ALTER TABLE OtherCall ALTER COLUMN [Date] datetime

END

|||

Try the below SQL Statement, this is my version of solution there may be other ways to

' Returns row if the column is already converted to the required data type

IF EXISTS (select c.name, c.xtype from sysobjects o, syscolumns c where o.id = c.id and o.name = tablename and c.xtype= 61(xtype value of datatime datatype, can be found in systypes table in master database) and c.name =column name)
BEGIN
// The column is already converted to datatime format. In your case this step will be blank
END
ELSE

BEGIN

// The column is not in datatime datatype so we need to convert it. Below is your code for conversion

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)

ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

END

checking if a particular DATE LIES IN CURRENT QUARTER

I have a table where their are columns as
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name = "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name = "SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
Thanks
Use a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>

checking if a particular DATE LIES IN CURRENT QUARTER

I have a table where their are columns as
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name
= "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name =
"SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
ThanksUse a calendar table.
http://www.aspfaq.com/2519
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>

checking if a particular DATE LIES IN CURRENT QUARTER

I have a table where their are columns as
Name,
Date,
Payment
Row_id (PK)
Now 1 name can has as many records and
each record for that name can be identified by the min Row_ID
I want to do the following
1. Check if the firstPayment date was within the Current Quarter where Name = "SAMAY"
2. Check if the LastPaymentdate was within the Current Quarter where Name = "SAMAY"
3. Check if the firstPayment date was in the last Quarter but
LastPayment was within the Current Quarter where Name = "SAMAY"
Please advice
ThanksUse a calendar table.
http://www.aspfaq.com/2519
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:ACCB6395-78AA-4FC4-8C34-D6F65F3620EB@.microsoft.com...
> I have a table where their are columns as
> Name,
> Date,
> Payment
> Row_id (PK)
> Now 1 name can has as many records and
> each record for that name can be identified by the min Row_ID
> I want to do the following
> 1. Check if the firstPayment date was within the Current Quarter where
Name = "SAMAY"
> 2. Check if the LastPaymentdate was within the Current Quarter where Name
= "SAMAY"
> 3. Check if the firstPayment date was in the last Quarter but
> LastPayment was within the Current Quarter where Name = "SAMAY"
> Please advice
> Thanks
>

Checking for PK

Hi,

I want to assign a list columns to be the PK on a table. I don't know what this list of columns would be.How do I go about figuring that?

Sample Data

Col1...Col2...Col3..Col4
1.........2......A.....X
1.........2......B....X
1.........2......A.....Y

As you can see Col1 & Col2 ONLY do not make up the PK. I need to include Col3 as well. My concern is I am not sure if I have a row like..
1.........2......A.....X reapeated lower down the order. So how do I figure the PK out?

Thanksvivek...post your sample so we can cut and paste them...

btw...you have a 4 composite pk you're looking for, based on what you're asking for..

USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 int, Col3 char(1), Col4 char(1)
, PRIMARY KEY (Col1, Col2, Col3, Col4))
GO

INSERT INTO myTable99(Col1,Col2,Col3,Col4)
SELECT 1,2,'A','X' UNION ALL
SELECT 1,2,'B','X' UNION ALL
SELECT 1,2,'A','Y'
GO

--So Good So Far

SELECT * FROM myTable99
GO

-- Oops...No Good

INSERT INTO myTable99(Col1,Col2,Col3,Col4)
SELECT 1,2,'A','X'

DROP TABLE myTable99
GO|||I figured the solution out. I find that my entire row serves as the primary key. So now I want to compare this with a similar table having the row as the PK. I am thinking on how do I go about this right now. Any ideas are welcome.

Thanks.

Checking for existing column

Hi,

I have a table which I want to alter to add the data from another
table.
Now both these tables have a few columns which are same. Now,
I want the system to be such that only the unique columns can be
added to the first table.

How can I accomplish this in SQL?Do something like

select table_name, column_name, ordinal_position, data_type
from information_schema.columns WHERE table_name = 'myTable1'
AND column_name NOT IN (
select table_name, column_name, ordinal_position, data_type
from information_schema.columns WHERE table_name = 'myTable2')

This will give you the columns of 'myTable1' not in 'myTable2'

and you do vica versa to see what columns are in 'myTable2' but not in
'myTable1'

From this combined list , you can create your ALTER TABLE statements.

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

"Shwetabh" <shwetabhgoel@.gmail.com> wrote in message
news:1141205791.768559.167490@.p10g2000cwp.googlegr oups.com...
> Hi,
> I have a table which I want to alter to add the data from another
> table.
> Now both these tables have a few columns which are same. Now,
> I want the system to be such that only the unique columns can be
> added to the first table.
> How can I accomplish this in SQL?

Checking for a columns existence

I need to check if a particular column exists in a table. If it does then I need to use it in a calculation, otherwise I don't. Here is the sql statement I currently have. It complains when I try to use column I01 in the select statement, even though the if condition evaluates to false. Any suggestions?

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE (TABLE_NAME = 'Test') AND column_name = 'I01')
SELECT 0
ELSE
SELECT I01 FROM TestCan you work it out by looking for the column name in the syscolumns database?|||Originally posted by andyabel
Can you work it out by looking for the column name in the syscolumns database?

It's the same problem. I can find whether it exists or not, but how do I use it in the select statement if it does? Apparently, it checks for the syntax of the query, before evaluating the IF condition.|||Maybe there's a setting that tells whether to verify all code clauses before running, or to verify only at run-time?
(Can't find it in BOL, though, but maybe someone else can)|||Can you fool the compiler by using exec()? e.g.:

declare @.string varchar(100)
set @.string='SELECT I01 FROM Names'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE (TABLE_NAME = 'Test') AND column_name = 'I01')
SELECT 0
ELSE
exec (@.string)|||Originally posted by andyabel
Can you fool the compiler by using exec()? e.g.:

declare @.string varchar(100)
set @.string='SELECT I01 FROM Names'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE (TABLE_NAME = 'Test') AND column_name = 'I01')
SELECT 0
ELSE
exec (@.string)

Thanks for all of your help guys, I think I got it to work.

Checking columns level dependencies

Hi all,
Need a big favour, I have to collect information like
TableName-> it's all columnNames--> dependent table name of EachColumn(where this column is refrenced)

I have managed to get some info like:-

select so.name as 'Table Name',sc.name as 'Column Name',st.[name], sc.length,sc.isnullable,so.crdate
from syscolumns sc
inner join sysobjects so on so.id=sc.id
inner join systypes st on st.xtype= sc.xtype
where so.xtype='U'
order by so.name

sp_depends and sp_MSdependencies is not returning me same fields..I would start with sysobjects, syscolumns and sysreferences. Try the query and if you get stuck, let us know

Checking / Opinion ?

I am taking these steps in a test environment;
Publishing database
1. Changed identity columns = Not for Replication (Hilary's script)
2. Scripted all Triggers & changed to "Not for Replication" (Is there a method to do this with a script?)
3. Added Primary Keys where not present as follows;
Alter Table x
add pk_col int identity(1,1) NOT FOR REPLICATION
Alter Table x
add constraint x_tbl_repl
primary key(pk_col)
4. Backed up Publishing Database
5. Restored Publishing Database with a new name = Subscribing Database
6. Setup Transactional continuous replication from Publishing Database to Subscribing Database
The reason I did everything in the publishing database is so that it's all set in the Subscribing database.....Make sense?
I am going to have a production oltp database that replicates all transactions to my reporting database. I also want to think of my reporting database as sort of a fall back position. If anything goes wrong in the oltp publishing database, everything resides in the subscribing db.
It is my understanding that I wanted the Identity columns in the subscribing database to just be written to, not incremented, therefore, the necessity of the Not for Replication.
The same goes for triggers in the subscribing database, I don't want them firing when replication occurs, therefore, the Not for Replication.
I don't have any inserts as follows;
Insert into Table A (Table A may have a newly added pk_col )
Select * from Table B (Table B may have a newly added pk_col )
therefore, I should not have any problems with my stored procedures.
Do I have it all covered? Am I on the right track? I really want to get this implemented, and correctly, to get this monkey off my back.
Any comments will certainly be appreciated!
This is not the standard way of deploying subscribers, but it does work. I just tried it.
The problem is when you go to do a resync. When a no-sync subscription needs to be re-initialized you must drop and recreate your subscription. You have the potential of having data consistency problems with your approach.
Also, it sounds like you might be thinking you are implementing bi-directional transactional replication with your method. You aren't. Its not clear from your post if you are or not. But the "fall back position" could be interpreted as this.
If you fail over to your Subscriber you will have to manually resync your Publisher with your Subscriber when it comes back on line.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I am only doing Transactional Replication in one direction. What I want is
to have a production OLTP database that replicates to a reporting database.
The reason I set everything up in the test production database is so that
these databases mirror one another from the start, and hoping I would not
miss anything.
If the schema needs to change in the production database, I intend to make
the same change in the subscriber. We have change management, where the
scripts are sent to me and I execute them over production. I am hoping that
it will just be a matter of running the scripts to create the objects
(views, sp's) in both databases to keep them in sync object wise.
I don't understand what you mean that I may face problems when doing a
resync, what am I doing wrong, or what is wrong with my approach to setting
up a reporting database, how would you approach setting up a reporting
database that is replicated to from the production database?
Am I out in left field making this project more difficult than it needs to
be?
For fall back I was thinking that since all transactions are being
replicated from production, if something went wrong in the production db, we
could just point the users to the subscribing database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:B8A63257-FE63-4673-AD55-9494C453404B@.microsoft.com...
> This is not the standard way of deploying subscribers, but it does work. I
just tried it.
> The problem is when you go to do a resync. When a no-sync subscription
needs to be re-initialized you must drop and recreate your subscription. You
have the potential of having data consistency problems with your approach.
> Also, it sounds like you might be thinking you are implementing
bi-directional transactional replication with your method. You aren't. Its
not clear from your post if you are or not. But the "fall back position"
could be interpreted as this.
> If you fail over to your Subscriber you will have to manually resync your
Publisher with your Subscriber when it comes back on line.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>