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

Are Bit Flags Bad?

Is the process of using integer data types to represent multiple values via the use of bit flags bad practice? It seems to go against the rules of normalization in a single field can represent multiple values. On the other hand that since these values can be tested for via bitwise operations, that it's not entirely bad.

Any insight would be appreciated.

ThanksI've used integers to store multiple bit states on occasion and found it very usefull.

For instance, you can have multiple boolean security states, and if you think of them as separate boolean values then separate bit fields would seem appropriate. But is you think of them as a single value describing the overall state of the security setting, then you aren't really violating any principles by storing them as a single field.

At a practical level, SQL Server will combine up to eight bit fields and store them internally as a single byte anyway.

Normalization is good practice because it leads to efficient and functional design. But if you avoid an instance where violating it would actually improve an application, then you are letting the cart lead the horse. Normalization is principle, not dogma.|||there is a multitude of examples where M$ itself used...smallint...as a datatype for this type of scenarios. i think int would be an overkill, while following the trend would save you storage space while doing the job.

are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980s
Hi,
Before i suggest u any thing just check the maintinance plan log file.
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killer

are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980sHi,
Before i suggest u any thing just check the maintinance plan log file.
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killersql

are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980sHi,
Before i suggest u any thing just check the maintinance plan log file.
--
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killer

are all SQL 2000 Server Wizard included in SQL 2000 Server Standard Edition

Hi,
right now we use the SQL 2000 Server Developer Edition to
make tests, but we would like to buy SQL 2000 Server. Does
the SQL 2000 Server STandard Edition include all the
WIZARDS that are also included with the Developer or
Enterprise Edition (when I highlight a database in
Enterprise Manager, go to "Tools" and "Wizards").
Can anyone help?
Thanks for reply.
BodoAll the wizards are included in Standard Edition as well. The differences
between Standard and Enterprise Edition are mostly in the high-end
performance and availabilty areas, like clustered servers, indexed views
etc.
For an overview see the topic "Features Supported by the Editions of SQL
Server 2000" in Books Online.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"bodo" <bodobecker@.hotmail.com> wrote in message
news:0b6501c36d6b$b90a15c0$a601280a@.phx.gbl...
> Hi,
> right now we use the SQL 2000 Server Developer Edition to
> make tests, but we would like to buy SQL 2000 Server. Does
> the SQL 2000 Server STandard Edition include all the
> WIZARDS that are also included with the Developer or
> Enterprise Edition (when I highlight a database in
> Enterprise Manager, go to "Tools" and "Wizards").
> Can anyone help?
> Thanks for reply.
> Bodo|||Hello Bodo !
Differences could be seen here:
http://www.microsoft.com/sql/evaluation/features/choosing.asp
HTH, Jens Süßmeyer.|||Hi Jacco,
thanks for your help!
Brgds Bodo
>--Original Message--
>All the wizards are included in Standard Edition as well.
The differences
>between Standard and Enterprise Edition are mostly in the
high-end
>performance and availabilty areas, like clustered
servers, indexed views
>etc.
>For an overview see the topic "Features Supported by the
Editions of SQL
>Server 2000" in Books Online.
>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.
>
>"bodo" <bodobecker@.hotmail.com> wrote in message
>news:0b6501c36d6b$b90a15c0$a601280a@.phx.gbl...
>> Hi,
>> right now we use the SQL 2000 Server Developer Edition
to
>> make tests, but we would like to buy SQL 2000 Server.
Does
>> the SQL 2000 Server STandard Edition include all the
>> WIZARDS that are also included with the Developer or
>> Enterprise Edition (when I highlight a database in
>> Enterprise Manager, go to "Tools" and "Wizards").
>> Can anyone help?
>> Thanks for reply.
>> Bodo
>
>.
>

Are all indexes created on subscriber ?

Using trans replication and I believe by default all the clustered and non
clustered indexes are copied to the subscriber .. Can someone confirm ?
Hassan,
the settings on the snapshot properties tab of the article properties
determines which indexes are copied over. If any are created afterward
initialization, they'll not be replicated, so sp_addscriptexec can be used
for these.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)