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

After doing some archiving I have found that rather than freeing up the
space straight after the archive run, the database is freeing up space
slowly over a number of days. Can anybody explain this? I am presuming that
there is some background process cleaning up and freeing the space.
Gav> After doing some archiving I have found that rather than freeing up the
> space straight after the archive run, the database is freeing up space
> slowly over a number of days. Can anybody explain this? I am presuming
that
> there is some background process cleaning up and freeing the space.
Are you talking abou SQL Server and Backup command? Bakup is not freeing the
space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands
in Books OnLine if this is what you need.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||No we have a SQL SAP database. At the weekend we archived some SAP data (at
SAP level), the data has been deleted from the database but the free space
has not gone up in one go, it seems to free up a bit at a time each day. I
am not shrinking anything.
example. We delete 2Gb of data from the database. After deleteing the data
it appears as if only 100 MB has been deleted. But each day a further 300MB
free space appears until 2Gb worth of free space has appeared.
Note: Not actual figures just a explanation of what happens.
Gav
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OdLzKK4rDHA.1784@.TK2MSFTNGP09.phx.gbl...
> > After doing some archiving I have found that rather than freeing up the
> > space straight after the archive run, the database is freeing up space
> > slowly over a number of days. Can anybody explain this? I am presuming
> that
> > there is some background process cleaning up and freeing the space.
> Are you talking abou SQL Server and Backup command? Bakup is not freeing
the
> space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands
> in Books OnLine if this is what you need.
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
>|||So you aren't talking about file sizes, but usage inside the files? If so, did you run DBCC
UPDATEUSAGE? Also, could possibly be a fragmentation issue.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bpirmq$a5q$1@.hercules.btinternet.com...
> No we have a SQL SAP database. At the weekend we archived some SAP data (at
> SAP level), the data has been deleted from the database but the free space
> has not gone up in one go, it seems to free up a bit at a time each day. I
> am not shrinking anything.
> example. We delete 2Gb of data from the database. After deleteing the data
> it appears as if only 100 MB has been deleted. But each day a further 300MB
> free space appears until 2Gb worth of free space has appeared.
> Note: Not actual figures just a explanation of what happens.
> Gav
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:OdLzKK4rDHA.1784@.TK2MSFTNGP09.phx.gbl...
> > > After doing some archiving I have found that rather than freeing up the
> > > space straight after the archive run, the database is freeing up space
> > > slowly over a number of days. Can anybody explain this? I am presuming
> > that
> > > there is some background process cleaning up and freeing the space.
> >
> > Are you talking abou SQL Server and Backup command? Bakup is not freeing
> the
> > space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands
> > in Books OnLine if this is what you need.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > Please reply only to the newsgroups.
> >
> >
>|||No I havn't. But since your post I have tested this on a non-production
server and it seems to do the trick. Thanks for your advice. :o)
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OiUJYq6rDHA.1196@.TK2MSFTNGP12.phx.gbl...
> So you aren't talking about file sizes, but usage inside the files? If so,
did you run DBCC
> UPDATEUSAGE? Also, could possibly be a fragmentation issue.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
> news:bpirmq$a5q$1@.hercules.btinternet.com...
> > No we have a SQL SAP database. At the weekend we archived some SAP data
(at
> > SAP level), the data has been deleted from the database but the free
space
> > has not gone up in one go, it seems to free up a bit at a time each day.
I
> > am not shrinking anything.
> >
> > example. We delete 2Gb of data from the database. After deleteing the
data
> > it appears as if only 100 MB has been deleted. But each day a further
300MB
> > free space appears until 2Gb worth of free space has appeared.
> >
> > Note: Not actual figures just a explanation of what happens.
> >
> > Gav
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:OdLzKK4rDHA.1784@.TK2MSFTNGP09.phx.gbl...
> > > > After doing some archiving I have found that rather than freeing up
the
> > > > space straight after the archive run, the database is freeing up
space
> > > > slowly over a number of days. Can anybody explain this? I am
presuming
> > > that
> > > > there is some background process cleaning up and freeing the space.
> > >
> > > Are you talking abou SQL Server and Backup command? Bakup is not
freeing
> > the
> > > space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE
commands
> > > in Books OnLine if this is what you need.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > Please reply only to the newsgroups.
> > >
> > >
> >
> >
>

Anything Like a <br /> Tag?

I am trying to place two fields in one column to save a little space.

=Fields!username.Value & "<br />" & Fields!notes.Value

However, the br tag doesn't work. Does XML permit some sort of text wrapping? If so, how can I accomplish this?

Try VbCrLF.

Thats vb's interpretation of new line character.

|||

Thanks for the quick response. That did it.