Are Blank String and String with only Space characters equal to each other? They should not be!!

I have a table called Table_1

Table_1
--
a varchar(10),
b varchar(20)

and Column a is the Primary Key.

INSERT INTO Table_1
(a ,b)
VALUES
('' , 'BLANK STRING')

INSERT INTO Table_1
(a ,b)
VALUES
(' ' ,'4 SPACES')

When i execute this Insertions, it inserts the First Record but on the Second one it gives Violation of Uniqueness

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 9
Violation of PRIMARY KEY constraint 'PK_Table_1'. Cannot insert duplicate key in object 'dbo.Table_1'.
The statement has been terminated.

My Questions is that; HOW can a Blank String or a String with 4(or any number) space charcters in it can be EQUAL? In my opinion they should not be regarded as the same! Is there a solution to this issue? That's not the case in Oracle Database.

Thanks for your help!

Regards,
Cem

According to ANSI standards (or is it SQL Server's implementation of ANSI...), anyway, for sorting purposes, leading blanks are ignored. (And Primary Keys are 'sorted'...)

Try these two statements, the first will provide the 'right' answer, and the second will provide the 'real' answer.

SELECT len( (replicate( ' ', 4 )) )
SELECT datalength( (replicate( ' ', 4 )) )

|||

You should check the setting of SET ANSI_PADDING. If you want behavior such as you find in ORACLE, you will need SET ANSI_PADDING ON. That way trailing blanks will not be trimmed from your VARCHAR insert statements.

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

Dan

Archiving Large table

Hi,

I am having problems with archiving. Any help will be greatly appreciated.

I have a table with 12 million records, and it needs to be archived to another table.

Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards.

Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!!

Does anyone have a better idea of doing this? I am really stuck...RE: I am having problems with archiving. Any help will be greatly appreciated. I have a table with 12 million records, and it needs to be archived to another table. Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards. Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!! Does anyone have a better idea of doing this? I am really stuck...

Q1 [It used to take 1 second to process 1000 records. Now, it takes 2 minutes to 14 minutes. Why?]

A1 There may be many different issues, (insufficient information to suggest a reasonably good guess and / or answer).

Q2 [Does anyone have a better, i.e.(FASTER?) idea of doing this?]

A2 Archiving may be accomplished efficiently. What is "Better" really depends on existing overall constraints and designs, available resources, and the details of the circumstances. An example that should be fairly quick (but not overly 'user friendly') would be Archiving a Test table in a Demo DB to an ArchiveDB database table named ArchiveTest.

Demo..Test To ArchiveDB..ArchiveTest

Use Demo
Go

INSERT INTO
[ArchiveDB].[dbo].[ArchiveTest]
([Parent], [Child])
SELECT
[Parent], [Child]
FROM
[Demo].[dbo].[Test]
GO

Alter Database Demo
Set Restricted_User
With
RollBack Immediate
Go

Alter Database Demo
Set Single_User
With
RollBack Immediate
Go

Alter Database Demo
Set Recovery Simple
With
RollBack Immediate
Go

-- drop and recreate, or truncate, delete, etc.
Drop TABLE [Test]
Go
CREATE TABLE [Test] (
[Parent] [varchar] (50) NOT NULL ,
[Child] [varchar] (50) NOT NULL)

Alter Database Demo
Set Recovery Full
With
RollBack Immediate
Go

Alter Database Demo
Set Multi_User
With
RollBack Immediate
Go|||how many indexs do you have on this table? Are any of them clustered?

I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.|||Originally posted by Paul Young
how many indexs do you have on this table? Are any of them clustered?

I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.

Thank you for your replies,

Actually, there is only primary index with identity on. That's it.
The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table.

Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table.

Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.|||RE: Thank you for your replies, Actually, there is only primary index with identity on. That's it. The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table. Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table. Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.

Q1 [I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse.]
A1 You are probably not updating your indexes at a suitable interval (to ensure optimal performance).

Q2 What can I do to keep the old record + new records sorted at all times?
A2 Cluster both TABLES on the desired column.|||my first inclination is that you have a corrupted index. the overall sorting should not change (aside from changes in data) due to inserting, updateing or deleting data.

During a one week prieod I rebuilt all my index once if not twice on very dynamic tables. Are you doing this?

If your primary index is clustered, you are reordering some part of your data everytime you insert, update or delete. This can lead to slow performance at times. If you must have this index then you just live with it, if you don't need it then change to non-clustered.|||Thank you, Paul Young.

Actually, I never rebuilt indexes on any of the tables. My bad...

What do I have to do to rebuild indexes? I tried DBCC DBREINDEX, and it didn't improve the performance of the archiving.

Can you guide me step-by-step what has to be done?

Thank you again.|||Generally I use maintinance plans to rebuild indexes and statistics along with other things however to answer your question DBCC DBREINDEX will do the trick.

Even if you haven't EVER rebuilt your index(s) they still should produce a result set correctly sorted. Again My hunch is that you have a corrupt index. To fix this you will need to drop the index an re-create it. You can do this while other are using the system but I would NOT advise it.|||One more thing, once you rebuild your index you probably want to update statistics so the correct optimization plans will be used.|||Thank you, Paul.

I tried to rebuild the index using 'DROP EXISTING'. It took about 5 minutes, and I opened the table, and it still looks messy.

But now, the index seems to be functioning faster. The problem is that, I don't use the primary key as query condition. Usually, my query condition is the 'CreationTime' which gets filled with default values getdate().

Basically, I query all the data created during a time period.

Should I create another index on CreationTime?

Thank you,|||If this table is used in an OLTP environment you want to keep the number of indexes to a minimum because evryting you inset/update/delete a row you also have to update ALL indexes. In your case you only have one index so adding one more shouldn't cause you a noticable slowdown and will GREATLY improve the prformance of your select.

before adding any indexes drop your select statement into Query Analyzer, turn on Show Execution Plan, Show Server Trace and Show Client Statistics and execute your select. Look at the "Execution Plan" tab and you will get a diagram representing what your select is doing.

Next click on Index Tunning Wizard and let SQL server suggest indexes to be built. Concider the suggestions and implament whatever you tinks looks good. Now rerun your select and look at the differences on the "Execution Plan" tab.

All of this is covered in Books Online, an excelent source of info once you know what to look up.|||Q1 Usually, my query condition is the 'CreationTime' Should I create another index on CreationTime?

A1 If you are looking for good performance, Yes. Generally, one wants a (well maintained) index available for the query parser to take advantage of for any column that is frequently queried.

Archive data suggestion

I have a table contains huge rows of data. Performance issue raised. I am
thinking archive some data so that the table will not be that big. The most
convience way is move it to another table. The problem is: will this solve
my performance problem? or I need to move it to another database to reduce
the database size?

Regards,
TrueNoTaking a chunk of your data out of a table will certainly improve
performance on queries against that table, if users have to access data
that contains data from both the old and new tables performance will
suffer. THink of it this way, if users are looking for a needle in a
haystack, decreasing the size of the haystack will decrease the length
of time to find the needle.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||EK9 (a@.a.com) writes:
> I have a table contains huge rows of data. Performance issue raised. I
> am thinking archive some data so that the table will not be that big.
> The most convience way is move it to another table. The problem is: will
> this solve my performance problem? or I need to move it to another
> database to reduce the database size?

Whether you put the archive table in the same or another database
does not affect performance for queries. It could reduce time for
backup and restore though.

However, it is far from certain than archiving data is the best way.
Maybe you need to review which indexes you have on the table.

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

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

Archive data before deletion

Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
ChrisThe two-database approach is a good way of doing this. Instead of using the
Deleted flag, you can now issue actual delete statements to remove old
records, but not before you design an ON DELETE trigger to propagate the
deleted rows to the archive database.
So, yes - you do need all those queries... :) This is what being a database
designer is all about.
Lookup CREATE TRIGGER in Books Online. Designing these triggers is easy -
simply use the 'deleted' table.
Example:
insert archive_db.dbo.table1
(...columns...)
select ...columns...
from deleted
Don't forget to include appropriate error-handling, so no delete goes
unnoticed.
ML|||I have an example here: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<devccon@.gmx.de> wrote in message
news:1122542261.075469.237440@.f14g2000cwb.googlegroups.com...
Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
Chris|||Thanks to you all for the directions. I'm still struggling with the
trigger but it shouldn't be that of a problem.
I really hoped there would be some other way, but, hey anything that
does the job is good...
Thx again,
Chris
devccon@.gmx.de wrote:
> Hi all,
> how can I copy all dependant child records into duplicate tables before
> deleting them.
> The situation is that I have a master table "customer" with 20 other
> tables that depend on this master table.
> Foreign keys are all set up correctly and cascading delete is enabled.
> Now when a customer wants to cancel his subscription, I don't want to
> delete all referenced data immediatly without saving, because I need
> them for possible future references, like billing addr. etc.
> Currently I'm setting a "Deleted" flag so that in any query this
> customer doesn't show up.
> What would be the best approach to archive all dependent data before
> deleting the parent and the child records.
> I'm thinking of duplicate tables and/or a duplicate database.
> I've also played around with triggers but can't get the automatic
> insert of the child records working.
> Do I really have to do something like this for every child table?
> Insert into dupAddr (select * from addr where customerid = 1)
> Insert into dupTrx (select * from trx where customerid = 1)
> Insert into dupCustomer (select * from customer where customerid = 1)
> etc.
> I'm using SQL2000/W2K3.
> Any help would be appreciated.
> thx in advance,
> Chris

Architectural question

Hello all, I have a general architecture question.

We have a system that essentially does multiple processes/operations
against a single table.

The objective is to increase performance (i.e. amount of throughput)
and to reduce latency. I can sacrifice accuracy/consistency if
necessary. Batches of about 100-1000 records at a time hit the table
at a time, are read and sent to another app for processing, are
retrieved and the results are updated to the same table, which is then
read again by yet another process.

In essence, whenever work is done against the information in that
table, the working process will update a status column in the table.
Unfortunately, this leads to a lot of contention as multiple procs are
attempting to update data sets and stepping on each other. The system
generated a lot of deadlocks until various forced nolock, rowlock, and
deadlock hints were added to the code. Now, of course, I'm seeing a
lot of server resources being burned up by locking efforts, and
duration is increased due to blocking on occasion, not to mention
various headaches and timeouts due to using these locking hints.

To fix, this, I have split up the central table vertically and removed
the need for various processes to update the table, now the system does
more reads and more inserts, but less updates. In essence, it moves
the key from table to table, and whether or not it is in a table
determines its status (and thus what next needs to be done)

So, First question: I believe that inserts take less time than updates,
but I don't know by how much. Can anyone point me towards something
that talks to this? I know, for example, that locks become much less
of a liability, but I'm a little concerned that the writes will cost
more than I save from removing the locking.

Second, It became clear that in moving to this kind of architecture it
will be necessary to delete records from one of the tables in realtime.
I'm shooting for a capacity of 100 million records/day, and one of
the tables needs to be short when comparing to the other table to get
it's status. So, given that the table isn't ever going to be more
than 8 pages long, will it being continually inserted into and then
deleted from matter? Can I expect to see lock contention in this
scenario (i.e, multiple sprocs inserting into, with another sproc
reading from then deleting from the table)?Hi,
I am not sure if you vertically split the tables the original problem
will go away. I think when these types of issues arise you might
consider partitioning the database (which is horizontal). That might
increase performance etc and solve locking problems too.

Amaxen1@.gmail.com wrote:

Quote:

Originally Posted by

Hello all, I have a general architecture question.
>
We have a system that essentially does multiple processes/operations
against a single table.
>
The objective is to increase performance (i.e. amount of throughput)
and to reduce latency. I can sacrifice accuracy/consistency if
necessary. Batches of about 100-1000 records at a time hit the table
at a time, are read and sent to another app for processing, are
retrieved and the results are updated to the same table, which is then
read again by yet another process.
>
>
In essence, whenever work is done against the information in that
table, the working process will update a status column in the table.
Unfortunately, this leads to a lot of contention as multiple procs are
attempting to update data sets and stepping on each other. The system
generated a lot of deadlocks until various forced nolock, rowlock, and
deadlock hints were added to the code. Now, of course, I'm seeing a
lot of server resources being burned up by locking efforts, and
duration is increased due to blocking on occasion, not to mention
various headaches and timeouts due to using these locking hints.
>
>
To fix, this, I have split up the central table vertically and removed
the need for various processes to update the table, now the system does
more reads and more inserts, but less updates. In essence, it moves
the key from table to table, and whether or not it is in a table
determines its status (and thus what next needs to be done)
>
So, First question: I believe that inserts take less time than updates,
but I don't know by how much. Can anyone point me towards something
that talks to this? I know, for example, that locks become much less
of a liability, but I'm a little concerned that the writes will cost
more than I save from removing the locking.
>
Second, It became clear that in moving to this kind of architecture it
will be necessary to delete records from one of the tables in realtime.
I'm shooting for a capacity of 100 million records/day, and one of
the tables needs to be short when comparing to the other table to get
it's status. So, given that the table isn't ever going to be more
than 8 pages long, will it being continually inserted into and then
deleted from matter? Can I expect to see lock contention in this
scenario (i.e, multiple sprocs inserting into, with another sproc
reading from then deleting from the table)?

sql

April CTP superkey oddity

I've set up a table with a superkey composed of a uniqueidentifier column and a varchar column. The uniqueidentifier column is not set to be a row GUID. When I try to add a new row with a duplicate uniqueidentifier value but different varchar value, I am told it violates the IX uniqueness constraint. I can't figure out what's wrong with this since it looks valid to me. Thanks in advance for suggestions.

JoshJust an FYI- I dropped the table and recreated it as I did before and everything went as expected!

Josh

Approximate row count?

Is there any way to use the statistics to get an approximate row count on a
large table without having to do a SELECT COUNT(*)?EXEC sp_spaceused 'table_name'
SELECT MAX(rowcnt) FROM sysindexes WHERE id = OBJECT_ID('table_name')
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Those will be more accurate, of course, if you first issue DBCC
UPDATEUSAGE(0) ...
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Try:
select
rowcnt
from
dbo.sysindexes
where
id = object_id ('MyTable')
and
indid in (0, 1)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
Is there any way to use the statistics to get an approximate row count on a
large table without having to do a SELECT COUNT(*)?|||Hi,
DBCC UPDATEUSAGE command will lock the table.
Use the below command to get the approximate table count:-
sp_spaceused <table_name>
Inacse if you have more descrepency then use the command UPDATEUSAGE which
scans the data pages in the table and makes any
necessary corrections to the sysindexes table regarding the storage space
used by each table
sp_spaceused <table_name>,@.updateusage = 'TRUE'
The above command will locks, so execute when the table access is less,
Thanks
Hari
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Thanks for the quick response. Is there any way to do something similar, but
find out how many rows fit a particular value for an indexed (or nonindexed)
column?
"Aaron Bertrand [SQL Server MVP]" wrote:

> EXEC sp_spaceused 'table_name'
> SELECT MAX(rowcnt) FROM sysindexes WHERE id = OBJECT_ID('table_name')
>
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
>
>|||> Thanks for the quick response. Is there any way to do something similar,
> but
> find out how many rows fit a particular value for an indexed (or
> nonindexed)
> column?
No, values are stored in the table, so you need to query the table.
What is the actual problem? Does SELECT COUNT(*) take too long? How many
rows are there, is there a clustered index, and what kind of hardware is it?
I am currently looking at a table on a system here, with 70,000,000 rows,
and SELECT COUNT(*) comes back in a few seconds. Granted, I use the NOLOCK
hint for ad hoc stuff like this, and it is on enterprise-class hardware
(quad zeon, 16gb ram, emc clariion). But design is definitely going to have
a big impact here.
A|||> The above command will locks, so execute when the table access is less,
Yes Hari, I didn't mean to imply that anyone should run DBCC UPDATEUSAGE
before every query.sql

Appropriate Index

I have a table where data is deleted, inserted and updated. What kind of
index is appropriate for the column
where data is deleted ?
where data is inserted ?
where data is updated ?
Thanks for any input."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:945F0AE5-AB17-4D18-8A35-312F4DB6E16F@.microsoft.com...
> I have a table where data is deleted, inserted and updated. What kind of
> index is appropriate for the column
> where data is deleted ?
> where data is inserted ?
> where data is updated ?
> Thanks for any input.
This depends on many things. If you post some DDL, we can help you out.
Read up on Indexes in the BOL for some tips on which columns are good
candidates for indexes and which ones are not.
Rick Sawtell
MCT, MCSD, MCDBA

Appropriate Index

I have a table where data is deleted, inserted and updated. What kind of
index is appropriate for the column
where data is deleted ?
where data is inserted ?
where data is updated ?
Thanks for any input.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:945F0AE5-AB17-4D18-8A35-312F4DB6E16F@.microsoft.com...
> I have a table where data is deleted, inserted and updated. What kind of
> index is appropriate for the column
> where data is deleted ?
> where data is inserted ?
> where data is updated ?
> Thanks for any input.
This depends on many things. If you post some DDL, we can help you out.
Read up on Indexes in the BOL for some tips on which columns are good
candidates for indexes and which ones are not.
Rick Sawtell
MCT, MCSD, MCDBA

Appropriate Index

I have a table where data is deleted, inserted and updated. What kind of
index is appropriate for the column
where data is deleted ?
where data is inserted ?
where data is updated ?
Thanks for any input."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:945F0AE5-AB17-4D18-8A35-312F4DB6E16F@.microsoft.com...
> I have a table where data is deleted, inserted and updated. What kind of
> index is appropriate for the column
> where data is deleted ?
> where data is inserted ?
> where data is updated ?
> Thanks for any input.
This depends on many things. If you post some DDL, we can help you out.
Read up on Indexes in the BOL for some tips on which columns are good
candidates for indexes and which ones are not.
Rick Sawtell
MCT, MCSD, MCDBA

Appropriate Data Type

I want to save some serialized data in a SQL Server 2005 table. What is the appropriate field type for this? I won't be associating a schema with the data. The data could vary in length--even up to several MB. I'm not sure whether to use text, NVARChar(MAX) or XML.

Brian

Brian,

A couple of SQL Server 2005 MSDN aritcles talk about when to use XML and some of the best practices. Some relevant ones are "XML Best Practices for Microsoft SQL Server 2005"; "XML Options in Microsoft SQL Server 2005"; "Performance Optimizations for the XML Data Type in SQL Server 2005".

In short XML is very useful 1) when you want schema validation (may not apply to your case); 2) when you want to query into the XML data or update granular parts of it. If your data is in XML format but your application merely uses the database to store and retrieve the data, an (n)varchar (max) column might suffice.

As a side note: Use varchar and nvarchar datatypes instead of text and ntext as the latter are in deprecation path (Deprecated Database Engine Features in SQL Server 2005)

Thanks

Babu

Approach Help to load Data from Flatfiles into relational table where Data is coming as spaces i

Hi ,

My Input is a flat file source and it has spaces in few columns in the data . These columns are linked to another table as a foreign key and when i try loading them in a relational structure Foreigh key violation is occuring , is there a standard method to replace these spaces .

what approach should i take so that data gets loaded in a relational structure.

for example

Name Age Salary Address

dsds 23 fghghgh

Salary description level

2345 nnncncn 4

here salary is used in this example , the datatype is char in real scenario

what approach should i take to load the data in with cleansing the spaces in ssis

Assumption: You don't have any control over the structure of the destination tables, but you can add records.

To maintain your foreign key constraint, you will need to create a "salary" record that you can use where salary is not defined. EG:

Salary Description Level

9999 Not Assigned 1

Then you can use a Derived Column expression to create a new column in your dataset that uses an If expression and then write that derived column to your destination table

Like this:

Code Snippet

[Salary]=="" ? 9999 : [Salary]

This is not an optimal solution (even though it is very practical.) Ideally you should restructure your tables so:

Assumption: You can change the structure of your destination tables

In this case, you need to take the Salary column out of your destination table, and put it into another table. The relationship is that employees can have zero or one salary code. So you can create a table relating employees to salary codes with two columns (EmployeeNumber, SalaryCode) and a primary key on EmployeeCode.

(This is a drastic simplification, of course. In a real-world scenario you would probably want to track historical information too.)

|||

Thanks for the Quick reply i tried doing as you said using derived transformation and using replace function but it doesnot seem to capture the space as value in the replace expression in ssis.

Replace(salary," ",9999) it does not work it says that there might be a possibility of divide by zero error. what expression should i use here to replace the values

|||

first of all, since this is a char value, you should put quotes around the 9999.

Seond, I didn't say to use the REPLACE function, though I guess that would work, but you run the risk of having a valid id messed up by replacing a leading or trailing space with a 9999.

Also, if your Salary code was " " (eight spaces) then the REPLACE function will put in:

"99999999999999999999999999999999" which migh be too big for your destination field.

So, try using TRIM first to get ris of spaces, then ?: to replace blank with 9999.

Put this into the Expression column of Derived Value step:

Code Snippet

TRIM([SALARY])=="" ? "9999" : TRIM([Salary])

sql

approach for sharing data between servers

hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
atte,
HernnDoing cross-server joins can be quite slow. Consider using replication and
replicate the two Person tables to SQL01 and SQL02. Then, do the joins
locally.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
atte,
Hernn|||in that case
i will have a problem
with the (unique) ID of the table
how can i sincronize the ID ?
i think i will need a kind of semaphor
to lock a resource (table)
that holds the "NEXT-ID" ...
atte,
Hernn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribi en el mensaje
news:O8vHpbfxGHA.4764@.TK2MSFTNGP02.phx.gbl...
| Doing cross-server joins can be quite slow. Consider using replication
and
| replicate the two Person tables to SQL01 and SQL02. Then, do the joins
| locally.
|
| --
| Tom
|
| ----
| Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
| SQL Server MVP
| Toronto, ON Canada
| .
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
| hi
| i have two "Person" tables
| one in server SQL01
| and the other in server SQL02
|
| i will integrate these two tables
| in a single "Person" table in other server SQL03
|
| then, i think
| all the "inner/left joins" to "Person" table
| won't work because "Person" table was moved
|
| how can i "join" to the "Person" table
| that is in other server '
|
| in other words, wich ways do i have
| for accessing a table that is not
| in the same server than the stored proc ?
| what is the efficience hit ?
|
| thanks
|
| --
| atte,
| Hernn
|
|

approach for sharing data between servers

hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
--
atte,
HernánDoing cross-server joins can be quite slow. Consider using replication and
replicate the two Person tables to SQL01 and SQL02. Then, do the joins
locally.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
--
atte,
Hernán|||in that case
i will have a problem
with the (unique) ID of the table
how can i sincronize the ID ?
i think i will need a kind of semaphor
to lock a resource (table)
that holds the "NEXT-ID" ...
--
atte,
Hernán
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribió en el mensaje
news:O8vHpbfxGHA.4764@.TK2MSFTNGP02.phx.gbl...
| Doing cross-server joins can be quite slow. Consider using replication
and
| replicate the two Person tables to SQL01 and SQL02. Then, do the joins
| locally.
|
| --
| Tom
|
| ----
| Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
| SQL Server MVP
| Toronto, ON Canada
| .
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
| hi
| i have two "Person" tables
| one in server SQL01
| and the other in server SQL02
|
| i will integrate these two tables
| in a single "Person" table in other server SQL03
|
| then, i think
| all the "inner/left joins" to "Person" table
| won't work because "Person" table was moved
|
| how can i "join" to the "Person" table
| that is in other server '
|
| in other words, wich ways do i have
| for accessing a table that is not
| in the same server than the stored proc ?
| what is the efficience hit ?
|
| thanks
|
| --
| atte,
| Hernán
|
|

Appointment Finder Query...TSQL Expert Required!

We are designing a booking system and have a list of appointments in a single table with the start and end time of the appointment.

Is there any way to write a query to return the available time gaps, I'm thinking here that we would need to generate a temporary table in the query and return the data from this table that doesn't join with data in the appointment table but I'm guessing and was wondering whether there's an easier way..

The table definition is

CREATE TABLE [dbo].[SALON_Appointment] (
[AppointmentID] [int] IDENTITY (1, 1) NOT NULL ,
[Subject] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
[StylistID] [int] NOT NULL ,
[Recurrency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstStartTime] [datetime] NOT NULL ,
[FirstEndTime] [datetime] NOT NULL ,
[UserID] [int] NOT NULL
) ON [PRIMARY]
GO

Thanks in advance

Hi

You don't say what version of SQL Server you are using and there are a number of pieces of information/constraints missing.

These question include:

What is the length of the working day - does midnight to 07:00 counts as free time? Does the system need to allow for breaks such as lunch time? You could book them as dummy appointments (probably need an appointment type field to allow them to be stripped out under certain circumstances). What is the minimum length for an appointment - is 09:30 to 09:35 a valid gap? What days do you need this for: All Days; Working Days; Days for which appointments exist.|||

I assume that you wana find the gap based on stylistID.

Code Snippet

SELECT A1.StylistID,

DATEDIFF(mi,A1.EndTime, A2.StartTime) AS Gap

FROM Appointment A1 JOIN Appointment A2 ON (A1.StylistID=A2.StylistID)

WHERE (A1.EndTime=

(SELECT MAX(A3.EndTime)

FROM Appointment A3

WHERE (A3.StylistID = A1.StylistID)

AND (A3.EndTime <= A2.StartTime)))

Hope this helps,

Paraclete

|||Hi,

Thanks for the reply.
I think I've worked a solution out which is similar to your method. I create a temporary and fill it with psuedo appointments, I then do a select which uses a sub query with NOT EXISTS to select items from the pseudo table that don't exist in the real appointment table and hence returns a list of available slots.

It looks like a little less code than your method but I've not tested it fully so I'm not sure whether its 100% yet.

Applying CASE in Stored Procedures

I'm creating a stored procedures, and pass in few parameters, which is @.keys, @.fields, @.types and @.likes, whereby I will select from certain table based on the @.types, where the @.fields equal to @.keys, I also wanted to implement the LIKE to the statement as well, but I found out some problem, so ends up my stored procedure looks like this:

ALTER PROCEDURE searchBooks @.keys varchar(50), @.fields varchar(50)

AS
SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

This statement run well, but I would like to specify the table to select with parameter instead of specified it, but I found it won't works, not even let me save the stored procedure.

Similar to the statements above, I would like another version of it where I using LIKE at the WHERE clause using CASE, I did something like this:

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END) LIKE @.keys

But it said that there was syntax error near the @.keys, do not how to fix it, I tried. Thanks in advanced.

I'm using SQL Server 2000, connecting with Visual Studio.NET 2003

Moving to the Transact-SQL forum, which is best suited to this type of questions.|||

What is the value of @.keys?

If you hard-code the values do you get results?

|||I pass in value to the @.key, I want the key to be the column name in the table, and compare it with the value, my statement is just a simple Select statement, but I want to write in the better way that allow programmer to specify which column to check, so I do not need to write so many statement to select different table.|||Using dynamic column names is not possible, you would probably have to use dynamic SQL to compose the string first and then execute it using either EXEC or sp_executesql.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Well, the CASE in the WHERE does works

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

but if I try to use the LIKE instead of '=' , it did alert me said got syntax error near @.keys, I couldn't get it fix.. adding CASE in the FROM was not possible though, thanks for the link, I will do some reading there.

|||

This works fine in 2005, but consider not doing this. The plan for this query will always be horrendous and will have to touch/lock every row in the books table. I would suggest you look at the following paper:

http://www.sommarskog.se/dyn-search.html

It has a wealth of possibilities for handing this type of situation. There are a few ways to do this, but no matter what I wouldn't just give the user the chance to search for one or the other. I would personally use full dynamic SQL to do something like this, especially in SQL Server 2005 where you can get around the security issues of dynamic SQL. Then the plan can be adjusted on calls that don't use the same filters.

All of this is nicely covered in that article.

|||

Hi,

In my sql server 2000 (Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) ) the LIKE statement works fine..

Pls check all the columns (Author, Publisher, Title, ISBN) datatype, if it is different convert all the columns to varchar/nvarchar,

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Convert(NVarchar,Author)
WHEN 'Publisher' THEN Convert(NVarchar,Publisher)
WHEN 'Title' THEN Convert(NVarchar,Title)
WHEN 'ISBN' THEN Convert(NVarchar,ISBN)
END) LIKE @.keys

Applying business rules to the database

Hi Experts,
My system stores events into a large table. I would like to apply some
rules on that table and possibly take action if the rules were satisfied.
For example if a certain event arrives 2 times in 60 seconds then perform an
action. What is the best way to apply rules to data in the database? If it
cannot be done with the database itself a recommendation of a third party
tool will be great. However please not that I cannot use any GUI. I need
to declare the rules and get the result of the rule into my application
which can be in C++ or C#
Thanks for all the help,
AviHave you looked at streaming databases? Google up BitStream.|||You could use triggers in SQL but if you need to invoke external code
against the database then it's better to put this in a middle-tier
application. In your middle-tier code, poll the table regularly for the
changes based on a date-time stamp column.
David Portas
SQL Server MVP
--

Applying aggregate function to whole table

Here is my problem and I suspect this is a simple question for the
experts.
My query attempts to get the names of players that played in a game
during the past x minutes and then the average scores of that player
for ALL games played by that player.
The query I have right now looks something like this (its from an
Access query but you get the point):
SELECT players.name, count(game.gameno) as gamesplayed,
Avg(game_players.score) AS avgscore
FROM players INNER JOIN (game INNER JOIN game_players ON game.gameno =
game_players.gameno) ON players.playerno = game_players.playerno
WHERE (((game.date_played) Between DateAdd('n',-100000,Now()) And
Now()))
As you see the Avg function will only return the average scores in the
games played that meet the "WHERE" condition. How can I get the
average score to be the Average for ALL scores for that player?
Thanks in advance.youretoast@.gmail.com,
This "select" statement is not related to sql server. There is not function
"now" in T-SQL, so I do not know if this can help. The idea is calculating
the avg for all rows and just counting the ones that meet the predicate.
SELECT
players.name,
count(
case when game.date_played Between DateAdd('n',-100000,Now()) And Now()
then game.gameno end
) as gamesplayed,
Avg(game_players.score) AS avgscore
FROM
players
INNER JOIN
(
game
INNER JOIN
game_players
ON game.gameno = game_players.gameno
) ON players.playerno = game_players.playerno
group by
players.name
AMB
"youretoast@.gmail.com" wrote:

> Here is my problem and I suspect this is a simple question for the
> experts.
> My query attempts to get the names of players that played in a game
> during the past x minutes and then the average scores of that player
> for ALL games played by that player.
> The query I have right now looks something like this (its from an
> Access query but you get the point):
> SELECT players.name, count(game.gameno) as gamesplayed,
> Avg(game_players.score) AS avgscore
> FROM players INNER JOIN (game INNER JOIN game_players ON game.gameno =
> game_players.gameno) ON players.playerno = game_players.playerno
> WHERE (((game.date_played) Between DateAdd('n',-100000,Now()) And
> Now()))
> As you see the Avg function will only return the average scores in the
> games played that meet the "WHERE" condition. How can I get the
> average score to be the Average for ALL scores for that player?
> Thanks in advance.
>

Apply ROWCOUNT on one Table in UNION

I have these two SELECTs in my SPROC... how can I merge the two tables
together so that my result is one table? UNION won't work as the
ROWCOUNTs are different, I can't use TOP as it's a sproc and you'll
notice I'm randomly sorting the top table and not the bottom. Thanks
for any help.
SET ROWCOUNT @.iNumRows
SELECT a.*
FROM tbl_1
WHERE badge = 0
ORDER BY NewID()
SET ROWCOUNT 100 PERCENT
SELECT a.*
FROM tbl_1
WHERE badge = @.iFilter
~Greg"Bac2Day1" <Bac2Day1@.gmail.com> wrote in message
news:1136411031.683766.39540@.g49g2000cwa.googlegroups.com...
>I have these two SELECTs in my SPROC... how can I merge the two tables
> together so that my result is one table? UNION won't work as the
> ROWCOUNTs are different, I can't use TOP as it's a sproc and you'll
> notice I'm randomly sorting the top table and not the bottom. Thanks
> for any help.
> SET ROWCOUNT @.iNumRows
> SELECT a.*
> FROM tbl_1
> WHERE badge = 0
> ORDER BY NewID()
> SET ROWCOUNT 100 PERCENT
> SELECT a.*
> FROM tbl_1
> WHERE badge = @.iFilter
>
> ~Greg
>
Please always state what version of SQL Server you are using. In SQL Server
2005 the following is possible:
DECLARE @.iNumRows INTEGER, @.iFilter INTEGER
SELECT x, badge
FROM
(SELECT TOP (@.inumrows) x, badge
FROM tbl_1 AS a
WHERE badge = 0
ORDER BY NEWID()
UNION ALL
SELECT x, badge
FROM tbl_1 AS a
WHERE badge = @.iFilter
) AS T ;
To do that in 2000 you would have to use dynamic SQL to construct the TOP
query because TOP doesn't otherwise allow a parameter for the number of
rows.
One alternative might be to insert the first result into a table variable
(use SET ROWCOUNT) and then UNION it with the second result. That would make
me slightly uneasy however, because the effect of ORDER BY in an INSERT
SELECT statement is buggy or at least poorly defined under some conditions.
Failing that I think I would opt for the dynamic query solution or combine
the result sets in the calling code rather than in the proc.
David Portas
SQL Server MVP
--|||>> In SQL Server 2005 the following is possible:
Is this a documented construct? To allow ORDER BY in the first SELECT seems
like an oversight, left unchecked in the parser.
Anith

Apply ROWCOUNT on one Table in UNION

I have these two SELECTs in my SPROC... how can I merge the two tables
together so that my result is one table? UNION won't work as the
ROWCOUNTs are different, I can't use TOP as it's a sproc and you'll
notice I'm randomly sorting the top table and not the bottom. Thanks
for any help.
SET ROWCOUNT @.iNumRows
SELECT a.*
FROM tbl_1
WHERE badge = 0
ORDER BY NewID()
SET ROWCOUNT 100 PERCENT
SELECT a.*
FROM tbl_1
WHERE badge = @.iFilter
~Greg
"Bac2Day1" <Bac2Day1@.gmail.com> wrote in message
news:1136411031.683766.39540@.g49g2000cwa.googlegro ups.com...
>I have these two SELECTs in my SPROC... how can I merge the two tables
> together so that my result is one table? UNION won't work as the
> ROWCOUNTs are different, I can't use TOP as it's a sproc and you'll
> notice I'm randomly sorting the top table and not the bottom. Thanks
> for any help.
> SET ROWCOUNT @.iNumRows
> SELECT a.*
> FROM tbl_1
> WHERE badge = 0
> ORDER BY NewID()
> SET ROWCOUNT 100 PERCENT
> SELECT a.*
> FROM tbl_1
> WHERE badge = @.iFilter
>
> ~Greg
>
Please always state what version of SQL Server you are using. In SQL Server
2005 the following is possible:
DECLARE @.iNumRows INTEGER, @.iFilter INTEGER
SELECT x, badge
FROM
(SELECT TOP (@.inumrows) x, badge
FROM tbl_1 AS a
WHERE badge = 0
ORDER BY NEWID()
UNION ALL
SELECT x, badge
FROM tbl_1 AS a
WHERE badge = @.iFilter
) AS T ;
To do that in 2000 you would have to use dynamic SQL to construct the TOP
query because TOP doesn't otherwise allow a parameter for the number of
rows.
One alternative might be to insert the first result into a table variable
(use SET ROWCOUNT) and then UNION it with the second result. That would make
me slightly uneasy however, because the effect of ORDER BY in an INSERT
SELECT statement is buggy or at least poorly defined under some conditions.
Failing that I think I would opt for the dynamic query solution or combine
the result sets in the calling code rather than in the proc.
David Portas
SQL Server MVP
|||>> In SQL Server 2005 the following is possible:
Is this a documented construct? To allow ORDER BY in the first SELECT seems
like an oversight, left unchecked in the parser.
Anith