HUGEINT data type? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

HUGEINT data type?

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2010-06-07 : 13:42:37

Is there anything bigger than a BIGINT?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-07 : 13:50:04

No.
But see this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129388

No, you're never too old to Yak'n'Roll if you're too young to die.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-07 : 14:09:09

You can use a decimal type up to 38 digits, which allows almost twice as many digits as a bigint (using decimal(38,0)).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-07 : 14:43:44

WHY do you need something bigger than BIGINT?

N 56°04'39.26"
E 12°55'05.63"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-06-07 : 14:57:18

Must be doing taxes

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2010-06-07 : 17:14:43

quote:
Originally posted by Peso

WHY do you need something bigger than BIGINT?

N 56°04'39.26"
E 12°55'05.63"

This statement is dying because the table in question has 68 fields. Anything greater than 63 for "colorder" causes the problem...

SELECT  @Columns_Updated = ISNULL(@Columns_Updated + ', ', '') + name 
	FROM    syscolumns 
	WHERE   id = @idTable   
	AND     CONVERT(VARBINARY,REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT, 2), colorder - 1)  > 0

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2010-06-07 : 17:17:00

quote:
Originally posted by robvolk

You can use a decimal type up to 38 digits, which allows almost twice as many digits as a bigint (using decimal(38,0)).

Sadly this results in this error...

The data types varbinary and decimal are incompatible in the boolean AND operator

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-06-07 : 17:48:20

INF: Proper Use of the COLUMNS_UPDATED() Function
http://support.microsoft.com/kb/232195

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2010-06-07 : 18:27:08

Do you find COLUMNS_UPDATED() useful?

Because it is the columns included in the SET statement, in the UPDATE statement, rather than columns that have ACTUALLY changed I think its use is rather limited. (Yes you can exclude logic where the columns were definitely not included in the Update statement ... but if you then need to check for which columns actually changed then it becomes a bit immaterial in many scenarios)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-07 : 18:46:37

Since you have more than 32 columns I think there are only a couple of options. I know of two: use the UPDATE() function or do some joins to determine change.

Here is a link the describe those two methods:
http://stackoverflow.com/questions/651524/most-efficient-method-to-detect-column-change-in-ms-sql-server

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2010-06-07 : 19:05:32

I found different SQL that seems to work better. One thing I would like to add to the XML that is constructed in this SQL is the old value and the new value. Anyone know how to go about that?


DECLARE @FldsUpdated XML
DECLARE @ColumnsUpdated VARBINARY(100)


SET @ColumnsUpdated = COLUMNS_UPDATED()

print @ColumnsUpdated

SET @FldsUpdated = 
(
SELECT COLUMN_NAME AS Name
FROM INFORMATION_SCHEMA.COLUMNS Field
WHERE 
TABLE_NAME = 'Courses' AND
sys.fn_IsBitSetInBitmask
(
@ColumnsUpdated, 
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')
) <> 0 
FOR XML AUTO, ROOT('Fields')
)


INSERT INTO AuditLog
(TableName,PrimaryKeyID,ChangedFields)
SELECT 'Courses', Inserted.CourseID, @FldsUpdated
FROM INSERTED

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources