Showing posts with label instead. Show all posts
Showing posts with label instead. Show all posts

Tuesday, March 20, 2012

bigint field vs int

i am creating an application which the number of records will be beyond 100000 so i need to set the primary keys to bigint instead of int

i need to know about the performance and difference

will it affect the performance much and second will i have to change any code like delete,update

A standard Int ranges from -2,147,483,648 through 2,147,483,647 so you can use them to store values much larger than 100000.

If you do use bigints, you would need to use Int64 in your code

|||

HI,

There will be some performance difference in using int and bigint. If you use bigint the performance is slower in an 32 bit processer. But in case of a 64 bit processor the performance should not be a problem

|||

Int will give you up to 2,147,483,647 while Bigint gives up to 9,223,372,036,854,775,807. The only difference in your stored procedure is that you will need to change the parameter declarations from INT to BIGINT.

>i need to know about the performance and difference
Obviously more working memory will be required for BIGINT instead of INT (8 bytes instead of 4 for for each value), however SQL's will still work very efficiently provided there is enough RAM. If you only have enough RAM for INT, changing to BIGINT will put you at a disadvantage - so make sure you have enough RAM.

sql

Sunday, February 12, 2012

Best way to force a varchar column to have no whitespace

I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.

Regards,
Terrencemetaperl wrote:

Quote:

Originally Posted by

I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.


Use an update trigger to rewrite it with any whitespace stripped out.|||One method is with a CHECK constraint that checks for the list of your
prohibited characters. You may need to scrub data before adding the
constraint.

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) +
']%')
WITH CHECK
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"metaperl" <metaperl@.gmail.comwrote in message
news:1159777716.594197.268970@.e3g2000cwe.googlegro ups.com...

Quote:

Originally Posted by

>I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.
>
Regards,
Terrence
>

|||On Mon, 02 Oct 2006 12:26:16 GMT, Dan Guzman wrote:

Quote:

Originally Posted by

>One method is with a CHECK constraint that checks for the list of your
>prohibited characters. You may need to scrub data before adding the
>constraint.
>
>ALTER TABLE MyTable WITH CHECK
>ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) +
>']%')
WITH CHECK
>GO


Hi Dan,

I think you wanted to include NOT there:

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn NOT LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) +
CHAR(9) + ']%')
WITH CHECK
GO

BTW, nice catch on the various variations of white space - I was about
to propose CHECK (MyColumn NOT LIKE '% %') when I saw your reply.

--
Hugo Kornelis, SQL Server MVP|||I think you wanted to include NOT there:

Yes, thanks for the catch, Hugo. I also had an extraneous NO CHECK and '^'
in the expression. The corrected version:

ALTER TABLE Table1 WITH CHECK
ADD CONSTRAINT CK_Table1_Col1
CHECK (Col1 NOT LIKE '%[' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) + ']%')
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALIDwrote in message
news:hh33i2p3mo572838m7c7hiiqdj2q8ckber@.4ax.com...

Quote:

Originally Posted by

On Mon, 02 Oct 2006 12:26:16 GMT, Dan Guzman wrote:
>

Quote:

Originally Posted by

>>One method is with a CHECK constraint that checks for the list of your
>>prohibited characters. You may need to scrub data before adding the
>>constraint.
>>
>>ALTER TABLE MyTable WITH CHECK
>>ADD CONSTRAINT CK_MyTable_MyColumn
> CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9)
>+
>>']%')
> WITH CHECK
>>GO


>
Hi Dan,
>
I think you wanted to include NOT there:
>
ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn NOT LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) +
CHAR(9) + ']%')
WITH CHECK
GO
>
BTW, nice catch on the various variations of white space - I was about
to propose CHECK (MyColumn NOT LIKE '% %') when I saw your reply.
>
--
Hugo Kornelis, SQL Server MVP

|||CONSTRAINT no_white_space
CHECK ( LEW(foo) = LEN (REPLACE (foo, ' ', '')))

You can then nest calls to REPLACE() for tabs,newlines, etc. easily.