COLUMNS_UPDATED()

SQL Server 2000
BOL says:
The COLUMNS_UPDATED function returns the bits in order from left to right,
with the least significant bit being the leftmost. The leftmost bit
represents the first column in the table; the next bit to the right
represents the second column, and so on.
But in the example, bitmask to check the colums 2,3,4 calculated as 14,
in which rightmost bit is the first column in the table. is there something
wrong here?Yeah. what you are asking makes sense.
Looks like the bit stream is looked at as a string than a binary number, if
thats what you concern is.
Say for a 8 column table the first 4 are updated, then the columns_updated()
will read as
1111
and if 2 and 3 are updated its going to be
011
All it means is that the 0 has a significance to give out the position of
the column being updated or not and we cannot say 011 and 11 are equal in
this context.
and coming to your question,
14 is read is 0111 rather than 1110.
Its using a reverse binary system.. I believe.. But a good point you pointed
out nevertheless.
Lets wait for the other's comments though :)|||prefect a crit :
> SQL Server 2000
> BOL says:
> The COLUMNS_UPDATED function returns the bits in order from left to right,
> with the least significant bit being the leftmost. The leftmost bit
> represents the first column in the table; the next bit to the right
> represents the second column, and so on.
NOT AT ALL !
The bit calculate is based on the ordinal position deliver by
INFORMATION_SCHEMA.COLUMNS
Dmo :
CREATE TABLE T_TEST_BITCOLS_TBC
(COL1 INT,
COL2 INT,
COL3 INT,
COL4 INT,
COL5 INT)
GO
INSERT INTO T_TEST_BITCOLS_TBC VALUES (1, 2, 3, 4, 5)
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL2
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL3
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL5
GO
ALTER TABLE T_TEST_BITCOLS_TBC
ADD COL2 INT
GO
ALTER TABLE T_TEST_BITCOLS_TBC
ADD COL6 INT
GO
INSERT INTO T_TEST_BITCOLS_TBC VALUES (10, 20, 30, 40)
GO
CREATE TABLE T_TRIGGER_COLS_UPDATED_TCU
(TABLE_NAME SYSNAME,
BIT_COLS INT)
GO
CREATE TRIGGER E_U_TCU
ON T_TEST_BITCOLS_TBC
FOR UPDATE
AS
INSERT INTO T_TRIGGER_COLS_UPDATED_TCU
SELECT 'T_TEST_BITCOLS_TBC', COLUMNS_UPDATED()
GO
UPDATE T_TEST_BITCOLS_TBC
SET COL2 = 0
GO
SELECT *
FROM T_TRIGGER_COLS_UPDATED_TCU
TABLE_NAME BIT_COLS
-- --
T_TEST_BITCOLS_TBC 32
SELECT COLUMN_NAME, ORDINAL_POSITION,
POWER(2, ORDINAL_POSITION - 1) AS BIT_COL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'T_TEST_BITCOLS_TBC'
COLUMN_NAME ORDINAL_POSITION BIT_COL
-- -- --
COL1 1 1
COL4 4 8
COL2 6 32 <====
COL6 7 64

> But in the example, bitmask to check the colums 2,3,4 calculated as 14,
> in which rightmost bit is the first column in the table. is there somethi
ng
> wrong here?
YES !
>
A +
--
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||thanks , i guess rightmost bit of every byte corresponds to first one of
the every 8 column regarding the ordinal of column.
surely , bit order of columns is read from system tables.
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:OeCvlFFaGHA.1228@.TK2MSFTNGP02.phx.gbl...
> prefect a crit :
> NOT AT ALL !
>
> The bit calculate is based on the ordinal position deliver by
> INFORMATION_SCHEMA.COLUMNS
> Dmo :
>
> CREATE TABLE T_TEST_BITCOLS_TBC
> (COL1 INT,
> COL2 INT,
> COL3 INT,
> COL4 INT,
> COL5 INT)
> GO
> INSERT INTO T_TEST_BITCOLS_TBC VALUES (1, 2, 3, 4, 5)
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL2
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL3
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL5
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> ADD COL2 INT
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> ADD COL6 INT
> GO
> INSERT INTO T_TEST_BITCOLS_TBC VALUES (10, 20, 30, 40)
> GO
> CREATE TABLE T_TRIGGER_COLS_UPDATED_TCU
> (TABLE_NAME SYSNAME,
> BIT_COLS INT)
> GO
>
> CREATE TRIGGER E_U_TCU
> ON T_TEST_BITCOLS_TBC
> FOR UPDATE
> AS
> INSERT INTO T_TRIGGER_COLS_UPDATED_TCU
> SELECT 'T_TEST_BITCOLS_TBC', COLUMNS_UPDATED()
> GO
> UPDATE T_TEST_BITCOLS_TBC
> SET COL2 = 0
> GO
> SELECT *
> FROM T_TRIGGER_COLS_UPDATED_TCU
> TABLE_NAME BIT_COLS
> -- --
> T_TEST_BITCOLS_TBC 32
>
> SELECT COLUMN_NAME, ORDINAL_POSITION,
> POWER(2, ORDINAL_POSITION - 1) AS BIT_COL
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'T_TEST_BITCOLS_TBC'
> COLUMN_NAME ORDINAL_POSITION BIT_COL
> -- -- --
> COL1 1 1
> COL4 4 8
> COL2 6 32 <====
> COL6 7 64
>
>
>
> YES !
>
> A +
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

Column based query

Hello all,
This one evades but I don't think it's possible. I thought I would
send it out the brain trust.
I have 4 columns that are bits. Is it possible to make a query that
will return the columns where the column is true?
Here is an example:
Col1 Col2 Col3 Col4
t t f f
t f f t
Is it possible to return two separate resultsets that gives me col1,
col in the first query
col 1 and col4 in the second?"axwack" <axwack@.gmail.com> wrote in message
news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
> Hello all,
> This one evades but I don't think it's possible. I thought I would
> send it out the brain trust.
> I have 4 columns that are bits. Is it possible to make a query that
> will return the columns where the column is true?
> Here is an example:
> Col1 Col2 Col3 Col4
> t t f f
> t f f t
> Is it possible to return two separate resultsets that gives me col1,
> col in the first query
> col 1 and col4 in the second?
Not sure exactly what you're asking for here, but based on my initial
reading it sounds like you want the names of the columns returned. Does
this do what you want? Note that I'll leave all the naming convention and
ANSI (nee ISO) quibbles for Celko to take up with you:
CREATE TABLE #bits (col1 bit,
col2 bit,
col3 bit,
col4 bit);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 1, 0, 0);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 0, 0, 1);
SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
FROM #bits;
DROP TABLE #bits;|||On Dec 24, 10:41=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> "axwack" <axw...@.gmail.com> wrote in message
> news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
>
>
>
>
>
>
>
> Not sure exactly what you're asking for here, but based on my initial
> reading it sounds like you want the names of the columns returned. =A0Does=[/vbcol
]
[vbcol=seagreen]
> this do what you want? =A0Note that I'll leave all the naming convention a=[/vbcol
]
nd[vbcol=seagreen]
> ANSI (nee ISO) quibbles for Celko to take up with you:
> CREATE TABLE #bits (col1 bit,
> =A0 col2 bit,
> =A0 col3 bit,
> =A0 col4 bit);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 1, 0, 0);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 0, 0, 1);
> SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
> =A0 CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
> =A0 CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
> =A0 CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
> FROM #bits;
> DROP TABLE #bits;
Hi that will work...I thought you could get the database to return the
coumns but this actually suits my needs because it is for gui
generation.|||"axwack" <axwack@.gmail.com> wrote in message
news:c715a12b-8b96-44d5-b14a-f489e774bc48@.j20g2000hsi.googlegroups.com...
> Hi that will work...I thought you could get the database to return the
> coumns but this actually suits my needs because it is for gui
> generation.
Not sure what you mean by "get the database to return the columns", but I
have a strong feeling that you're working here with a table that's not
properly normalized. I'm still not 100% sure on what you're trying to
accomplish, but if you're trying to do what I believe you are try looking at
normalization... you may find a better way to accomplish these tasks.

Column based query

Hello all,
This one evades but I don't think it's possible. I thought I would
send it out the brain trust.
I have 4 columns that are bits. Is it possible to make a query that
will return the columns where the column is true?
Here is an example:
Col1 Col2 Col3 Col4
t t f f
t f f t
Is it possible to return two separate resultsets that gives me col1,
col in the first query
col 1 and col4 in the second?
"axwack" <axwack@.gmail.com> wrote in message
news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
> Hello all,
> This one evades but I don't think it's possible. I thought I would
> send it out the brain trust.
> I have 4 columns that are bits. Is it possible to make a query that
> will return the columns where the column is true?
> Here is an example:
> Col1 Col2 Col3 Col4
> t t f f
> t f f t
> Is it possible to return two separate resultsets that gives me col1,
> col in the first query
> col 1 and col4 in the second?
Not sure exactly what you're asking for here, but based on my initial
reading it sounds like you want the names of the columns returned. Does
this do what you want? Note that I'll leave all the naming convention and
ANSI (nee ISO) quibbles for Celko to take up with you:
CREATE TABLE #bits (col1 bit,
col2 bit,
col3 bit,
col4 bit);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 1, 0, 0);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 0, 0, 1);
SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
FROM #bits;
DROP TABLE #bits;
|||On Dec 24, 10:41Xpm, "Mike C#" <x...@.xyz.com> wrote:
> "axwack" <axw...@.gmail.com> wrote in message
> news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
>
>
>
>
> Not sure exactly what you're asking for here, but based on my initial
> reading it sounds like you want the names of the columns returned. XDoes
> this do what you want? XNote that I'll leave all the naming convention and
> ANSI (nee ISO) quibbles for Celko to take up with you:
> CREATE TABLE #bits (col1 bit,
> X col2 bit,
> X col3 bit,
> X col4 bit);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 1, 0, 0);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 0, 0, 1);
> SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
> X CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
> X CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
> X CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
> FROM #bits;
> DROP TABLE #bits;
Hi that will work...I thought you could get the database to return the
coumns but this actually suits my needs because it is for gui
generation.
|||"axwack" <axwack@.gmail.com> wrote in message
news:c715a12b-8b96-44d5-b14a-f489e774bc48@.j20g2000hsi.googlegroups.com...
> Hi that will work...I thought you could get the database to return the
> coumns but this actually suits my needs because it is for gui
> generation.
Not sure what you mean by "get the database to return the columns", but I
have a strong feeling that you're working here with a table that's not
properly normalized. I'm still not 100% sure on what you're trying to
accomplish, but if you're trying to do what I believe you are try looking at
normalization... you may find a better way to accomplish these tasks.

Column based query

Hello all,
This one evades but I don't think it's possible. I thought I would
send it out the brain trust.
I have 4 columns that are bits. Is it possible to make a query that
will return the columns where the column is true?
Here is an example:
Col1 Col2 Col3 Col4
t t f f
t f f t
Is it possible to return two separate resultsets that gives me col1,
col in the first query
col 1 and col4 in the second?"axwack" <axwack@.gmail.com> wrote in message
news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
> Hello all,
> This one evades but I don't think it's possible. I thought I would
> send it out the brain trust.
> I have 4 columns that are bits. Is it possible to make a query that
> will return the columns where the column is true?
> Here is an example:
> Col1 Col2 Col3 Col4
> t t f f
> t f f t
> Is it possible to return two separate resultsets that gives me col1,
> col in the first query
> col 1 and col4 in the second?
Not sure exactly what you're asking for here, but based on my initial
reading it sounds like you want the names of the columns returned. Does
this do what you want? Note that I'll leave all the naming convention and
ANSI (nee ISO) quibbles for Celko to take up with you:
CREATE TABLE #bits (col1 bit,
col2 bit,
col3 bit,
col4 bit);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 1, 0, 0);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 0, 0, 1);
SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
FROM #bits;
DROP TABLE #bits;|||On Dec 24, 10:41=A0pm, "Mike C#" <x...@.xyz.com> wrote:
> "axwack" <axw...@.gmail.com> wrote in message
> news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
>
> > Hello all,
> > This one evades but I don't think it's possible. I thought I would
> > send it out the brain trust.
> > I have 4 columns that are bits. Is it possible to make a query that
> > will return the columns where the column is true?
> > Here is an example:
> > Col1 =A0 =A0Col2 =A0 Col3 Col4
> > t =A0 =A0 =A0 =A0 =A0 =A0t =A0 =A0 =A0 =A0 =A0 =A0f =A0 =A0 =A0 f
> > t =A0 =A0 =A0 =A0 =A0 =A0f =A0 =A0 =A0 =A0 =A0 =A0f =A0 =A0 =A0 t
> > Is it possible to return two separate resultsets that gives me col1,
> > col in the first query
> > col 1 and col4 in the second?
> Not sure exactly what you're asking for here, but based on my initial
> reading it sounds like you want the names of the columns returned. =A0Does=
> this do what you want? =A0Note that I'll leave all the naming convention a=nd
> ANSI (nee ISO) quibbles for Celko to take up with you:
> CREATE TABLE #bits (col1 bit,
> =A0 col2 bit,
> =A0 col3 bit,
> =A0 col4 bit);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 1, 0, 0);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 0, 0, 1);
> SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
> =A0 CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
> =A0 CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
> =A0 CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
> FROM #bits;
> DROP TABLE #bits;
Hi that will work...I thought you could get the database to return the
coumns but this actually suits my needs because it is for gui
generation.|||"axwack" <axwack@.gmail.com> wrote in message
news:c715a12b-8b96-44d5-b14a-f489e774bc48@.j20g2000hsi.googlegroups.com...
> Hi that will work...I thought you could get the database to return the
> coumns but this actually suits my needs because it is for gui
> generation.
Not sure what you mean by "get the database to return the columns", but I
have a strong feeling that you're working here with a table that's not
properly normalized. I'm still not 100% sure on what you're trying to
accomplish, but if you're trying to do what I believe you are try looking at
normalization... you may find a better way to accomplish these tasks.