I've got a column that is as type int(length=4).
This column is getting changed to smallint. Obviously, some values in
the column will not fit into smallint type. I've been told that as part
of moving data over, I should ignore top two bytes and carry forward
the bottom two bytes - so that it will fit into smallint.
e.g. values in the column is:
TableA
--
Col1
4235623
Pls can someone help/point me in the right direction as to how I could
do it via TSQL.
Thanks,
SandiyanYou can use Bitwise And:
UPDATE YourTable
SET col = col & 65535
ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
I'm slightly puzzled as to why this would ever make sense though. Are
you really storing bitmapped values in an INTEGER column?
David Portas
SQL Server MVP
--|||Thanks and appreciate your help. I knew there must have been an easier
solution!...
The way I got it to work was (a bit long winded!):
cast(substring (cast(ColA as binary(4)), 3, 2) as smallint)
regards
Sandiyan.
David Portas wrote:
> You can use Bitwise And:
> UPDATE YourTable
> SET col = col & 65535
> ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
> I'm slightly puzzled as to why this would ever make sense though. Are
> you really storing bitmapped values in an INTEGER column?
> --
> David Portas
> SQL Server MVP
> --|||David,
This doesn't quite do it. & results of 0x8000 and higher will
fail, because the result, 0x0000' is a positive int, and
smallint cannot hold positive integers greater than 0x00007FFF.
create table T (i int)
go
insert into T values (2000000000)
go
update T set i = i & 65535
go
alter table T alter column i smallint
go
drop table T
Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 37888.
The statement has been terminated.
I think SUBSTRING is a good idea (not that I understand
why the OP wants to throw data away), but here is one solution
similar to yours:
update T set i = (i+32768) & 65535 - 32768
SK
David Portas wrote:
>You can use Bitwise And:
>UPDATE YourTable
> SET col = col & 65535
>ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
>I'm slightly puzzled as to why this would ever make sense though. Are
>you really storing bitmapped values in an INTEGER column?
>
>|||Thanks Steve. A good catch.
David Portas
SQL Server MVP
--|||Why do you think that this will work? Why does this make an sense to
you?
Showing posts with label byte. Show all posts
Showing posts with label byte. Show all posts
Thursday, February 16, 2012
byte manipulation for int
I've got a column that is as type int(length=4).
This column is getting changed to smallint. Obviously, some values in
the column will not fit into smallint type. I've been told that as part
of moving data over, I should ignore top two bytes and carry forward
the bottom two bytes - so that it will fit into smallint.
e.g. values in the column is:
TableA
Col1
4235623
Pls can someone help/point me in the right direction as to how I could
do it via TSQL.
Thanks,
Sandiyan
You can use Bitwise And:
UPDATE YourTable
SET col = col & 65535
ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
I'm slightly puzzled as to why this would ever make sense though. Are
you really storing bitmapped values in an INTEGER column?
David Portas
SQL Server MVP
|||Thanks and appreciate your help. I knew there must have been an easier
solution!...
The way I got it to work was (a bit long winded!):
cast(substring (cast(ColA as binary(4)), 3, 2) as smallint)
regards
Sandiyan.
David Portas wrote:
> You can use Bitwise And:
> UPDATE YourTable
> SET col = col & 65535
> ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
> I'm slightly puzzled as to why this would ever make sense though. Are
> you really storing bitmapped values in an INTEGER column?
> --
> David Portas
> SQL Server MVP
> --
|||David,
This doesn't quite do it. & results of 0x8000 and higher will
fail, because the result, 0x0000? is a positive int, and
smallint cannot hold positive integers greater than 0x00007FFF.
create table T (i int)
go
insert into T values (2000000000)
go
update T set i = i & 65535
go
alter table T alter column i smallint
go
drop table T
Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 37888.
The statement has been terminated.
I think SUBSTRING is a good idea (not that I understand
why the OP wants to throw data away), but here is one solution
similar to yours:
update T set i = (i+32768) & 65535 - 32768
SK
David Portas wrote:
>You can use Bitwise And:
>UPDATE YourTable
> SET col = col & 65535
>ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
>I'm slightly puzzled as to why this would ever make sense though. Are
>you really storing bitmapped values in an INTEGER column?
>
>
|||Thanks Steve. A good catch.
David Portas
SQL Server MVP
|||Why do you think that this will work? Why does this make an sense to
you?
This column is getting changed to smallint. Obviously, some values in
the column will not fit into smallint type. I've been told that as part
of moving data over, I should ignore top two bytes and carry forward
the bottom two bytes - so that it will fit into smallint.
e.g. values in the column is:
TableA
Col1
4235623
Pls can someone help/point me in the right direction as to how I could
do it via TSQL.
Thanks,
Sandiyan
You can use Bitwise And:
UPDATE YourTable
SET col = col & 65535
ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
I'm slightly puzzled as to why this would ever make sense though. Are
you really storing bitmapped values in an INTEGER column?
David Portas
SQL Server MVP
|||Thanks and appreciate your help. I knew there must have been an easier
solution!...
The way I got it to work was (a bit long winded!):
cast(substring (cast(ColA as binary(4)), 3, 2) as smallint)
regards
Sandiyan.
David Portas wrote:
> You can use Bitwise And:
> UPDATE YourTable
> SET col = col & 65535
> ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
> I'm slightly puzzled as to why this would ever make sense though. Are
> you really storing bitmapped values in an INTEGER column?
> --
> David Portas
> SQL Server MVP
> --
|||David,
This doesn't quite do it. & results of 0x8000 and higher will
fail, because the result, 0x0000? is a positive int, and
smallint cannot hold positive integers greater than 0x00007FFF.
create table T (i int)
go
insert into T values (2000000000)
go
update T set i = i & 65535
go
alter table T alter column i smallint
go
drop table T
Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 37888.
The statement has been terminated.
I think SUBSTRING is a good idea (not that I understand
why the OP wants to throw data away), but here is one solution
similar to yours:
update T set i = (i+32768) & 65535 - 32768
SK
David Portas wrote:
>You can use Bitwise And:
>UPDATE YourTable
> SET col = col & 65535
>ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
>I'm slightly puzzled as to why this would ever make sense though. Are
>you really storing bitmapped values in an INTEGER column?
>
>
|||Thanks Steve. A good catch.
David Portas
SQL Server MVP
|||Why do you think that this will work? Why does this make an sense to
you?
byte manipulation for int
I've got a column that is as type int(length=4).
This column is getting changed to smallint. Obviously, some values in
the column will not fit into smallint type. I've been told that as part
of moving data over, I should ignore top two bytes and carry forward
the bottom two bytes - so that it will fit into smallint.
e.g. values in the column is:
TableA
--
Col1
4235623
Pls can someone help/point me in the right direction as to how I could
do it via TSQL.
Thanks,
SandiyanYou can use Bitwise And:
UPDATE YourTable
SET col = col & 65535
ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
I'm slightly puzzled as to why this would ever make sense though. Are
you really storing bitmapped values in an INTEGER column?
David Portas
SQL Server MVP
--|||Thanks and appreciate your help. I knew there must have been an easier
solution!...
The way I got it to work was (a bit long winded!):
cast(substring (cast(ColA as binary(4)), 3, 2) as smallint)
regards
Sandiyan.
David Portas wrote:
> You can use Bitwise And:
> UPDATE YourTable
> SET col = col & 65535
> ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
> I'm slightly puzzled as to why this would ever make sense though. Are
> you really storing bitmapped values in an INTEGER column?
> --
> David Portas
> SQL Server MVP
> --|||David,
This doesn't quite do it. & results of 0x8000 and higher will
fail, because the result, 0x0000' is a positive int, and
smallint cannot hold positive integers greater than 0x00007FFF.
create table T (i int)
go
insert into T values (2000000000)
go
update T set i = i & 65535
go
alter table T alter column i smallint
go
drop table T
Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 37888.
The statement has been terminated.
I think SUBSTRING is a good idea (not that I understand
why the OP wants to throw data away), but here is one solution
similar to yours:
update T set i = (i+32768) & 65535 - 32768
SK
David Portas wrote:
>You can use Bitwise And:
>UPDATE YourTable
> SET col = col & 65535
>ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
>I'm slightly puzzled as to why this would ever make sense though. Are
>you really storing bitmapped values in an INTEGER column?
>
>|||Thanks Steve. A good catch.
David Portas
SQL Server MVP
--|||Why do you think that this will work? Why does this make an sense to
you?
This column is getting changed to smallint. Obviously, some values in
the column will not fit into smallint type. I've been told that as part
of moving data over, I should ignore top two bytes and carry forward
the bottom two bytes - so that it will fit into smallint.
e.g. values in the column is:
TableA
--
Col1
4235623
Pls can someone help/point me in the right direction as to how I could
do it via TSQL.
Thanks,
SandiyanYou can use Bitwise And:
UPDATE YourTable
SET col = col & 65535
ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
I'm slightly puzzled as to why this would ever make sense though. Are
you really storing bitmapped values in an INTEGER column?
David Portas
SQL Server MVP
--|||Thanks and appreciate your help. I knew there must have been an easier
solution!...
The way I got it to work was (a bit long winded!):
cast(substring (cast(ColA as binary(4)), 3, 2) as smallint)
regards
Sandiyan.
David Portas wrote:
> You can use Bitwise And:
> UPDATE YourTable
> SET col = col & 65535
> ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
> I'm slightly puzzled as to why this would ever make sense though. Are
> you really storing bitmapped values in an INTEGER column?
> --
> David Portas
> SQL Server MVP
> --|||David,
This doesn't quite do it. & results of 0x8000 and higher will
fail, because the result, 0x0000' is a positive int, and
smallint cannot hold positive integers greater than 0x00007FFF.
create table T (i int)
go
insert into T values (2000000000)
go
update T set i = i & 65535
go
alter table T alter column i smallint
go
drop table T
Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 37888.
The statement has been terminated.
I think SUBSTRING is a good idea (not that I understand
why the OP wants to throw data away), but here is one solution
similar to yours:
update T set i = (i+32768) & 65535 - 32768
SK
David Portas wrote:
>You can use Bitwise And:
>UPDATE YourTable
> SET col = col & 65535
>ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
>I'm slightly puzzled as to why this would ever make sense though. Are
>you really storing bitmapped values in an INTEGER column?
>
>|||Thanks Steve. A good catch.
David Portas
SQL Server MVP
--|||Why do you think that this will work? Why does this make an sense to
you?
byte manipulation for int
I've got a column that is as type int(length=4).
This column is getting changed to smallint. Obviously, some values in
the column will not fit into smallint type. I've been told that as part
of moving data over, I should ignore top two bytes and carry forward
the bottom two bytes - so that it will fit into smallint.
e.g. values in the column is:
TableA
--
Col1
4235623
Pls can someone help/point me in the right direction as to how I could
do it via TSQL.
Thanks,
SandiyanYou can use Bitwise And:
UPDATE YourTable
SET col = col & 65535
ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
I'm slightly puzzled as to why this would ever make sense though. Are
you really storing bitmapped values in an INTEGER column?
--
David Portas
SQL Server MVP
--|||Thanks and appreciate your help. I knew there must have been an easier
solution!...
The way I got it to work was (a bit long winded!):
cast(substring (cast(ColA as binary(4)), 3, 2) as smallint)
regards
Sandiyan.
David Portas wrote:
> You can use Bitwise And:
> UPDATE YourTable
> SET col = col & 65535
> ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
> I'm slightly puzzled as to why this would ever make sense though. Are
> you really storing bitmapped values in an INTEGER column?
> --
> David Portas
> SQL Server MVP
> --|||David,
This doesn't quite do it. & results of 0x8000 and higher will
fail, because the result, 0x0000' is a positive int, and
smallint cannot hold positive integers greater than 0x00007FFF.
create table T (i int)
go
insert into T values (2000000000)
go
update T set i = i & 65535
go
alter table T alter column i smallint
go
drop table T
Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 37888.
The statement has been terminated.
I think SUBSTRING is a good idea (not that I understand
why the OP wants to throw data away), but here is one solution
similar to yours:
update T set i = (i+32768) & 65535 - 32768
SK
David Portas wrote:
>You can use Bitwise And:
>UPDATE YourTable
> SET col = col & 65535
>ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
>I'm slightly puzzled as to why this would ever make sense though. Are
>you really storing bitmapped values in an INTEGER column?
>
>|||Thanks Steve. A good catch.
--
David Portas
SQL Server MVP
--|||Why do you think that this will work? Why does this make an sense to
you?
This column is getting changed to smallint. Obviously, some values in
the column will not fit into smallint type. I've been told that as part
of moving data over, I should ignore top two bytes and carry forward
the bottom two bytes - so that it will fit into smallint.
e.g. values in the column is:
TableA
--
Col1
4235623
Pls can someone help/point me in the right direction as to how I could
do it via TSQL.
Thanks,
SandiyanYou can use Bitwise And:
UPDATE YourTable
SET col = col & 65535
ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
I'm slightly puzzled as to why this would ever make sense though. Are
you really storing bitmapped values in an INTEGER column?
--
David Portas
SQL Server MVP
--|||Thanks and appreciate your help. I knew there must have been an easier
solution!...
The way I got it to work was (a bit long winded!):
cast(substring (cast(ColA as binary(4)), 3, 2) as smallint)
regards
Sandiyan.
David Portas wrote:
> You can use Bitwise And:
> UPDATE YourTable
> SET col = col & 65535
> ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
> I'm slightly puzzled as to why this would ever make sense though. Are
> you really storing bitmapped values in an INTEGER column?
> --
> David Portas
> SQL Server MVP
> --|||David,
This doesn't quite do it. & results of 0x8000 and higher will
fail, because the result, 0x0000' is a positive int, and
smallint cannot hold positive integers greater than 0x00007FFF.
create table T (i int)
go
insert into T values (2000000000)
go
update T set i = i & 65535
go
alter table T alter column i smallint
go
drop table T
Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 37888.
The statement has been terminated.
I think SUBSTRING is a good idea (not that I understand
why the OP wants to throw data away), but here is one solution
similar to yours:
update T set i = (i+32768) & 65535 - 32768
SK
David Portas wrote:
>You can use Bitwise And:
>UPDATE YourTable
> SET col = col & 65535
>ALTER TABLE YourTable ALTER COLUMN col SMALLINT NOT NULL
>I'm slightly puzzled as to why this would ever make sense though. Are
>you really storing bitmapped values in an INTEGER column?
>
>|||Thanks Steve. A good catch.
--
David Portas
SQL Server MVP
--|||Why do you think that this will work? Why does this make an sense to
you?
byte by byte level replication versus sql server replication
Hi
Can anyone give me a helpful link or information on the pros and cons on the
"byte by byte level replication" versus "sql server transaction replication".
Requirement:
I need upto second replication to a geographically remote site, so I was
considering sql server transactional replication versus byte by byte data
level replication between the geographically remote servers.
What are the pros and cons?
Any kind of information would be of help.
Thanks
For software solutions - Byte by byte is not good for write intensive
operations - I find it doesn't scale well.
For hardware solutions look at products like EMC's SRDF. Its very expensive.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:D3925A66-6ED5-4795-AE50-B5BA56F6D7B4@.microsoft.com...
> Hi
> Can anyone give me a helpful link or information on the pros and cons on
the
> "byte by byte level replication" versus "sql server transaction
replication".
> Requirement:
> I need upto second replication to a geographically remote site, so I was
> considering sql server transactional replication versus byte by byte data
> level replication between the geographically remote servers.
> What are the pros and cons?
> Any kind of information would be of help.
> Thanks
>
>
|||Hi
When you say software solutions, what does it mean? It has to have a
software on a hardware box even EMC SRDF is a remote replication software
solution.
Is the Data Replication Manager (DRM) from HP on XP hardware comparable to
EMC's SRDF?
can you please clarify the hardware and software solutions difference.
Thanks
"Hilary Cotter" wrote:
> For software solutions - Byte by byte is not good for write intensive
> operations - I find it doesn't scale well.
> For hardware solutions look at products like EMC's SRDF. Its very expensive.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Pari" <Pari@.discussions.microsoft.com> wrote in message
> news:D3925A66-6ED5-4795-AE50-B5BA56F6D7B4@.microsoft.com...
> the
> replication".
>
>
|||A software solution which does a byte by byte copy is DoubleTake. I believe
it has a driver which sits between your os and the disk array and monitors
byte activity, then it copies deltas to the destination server.
Data Replication Manager sounds similar to EMC's SRDF, but I am not sure if
either can run on XP.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:46724538-E7A1-46FC-A6BF-A786DF7B4508@.microsoft.com...[vbcol=seagreen]
> Hi
> When you say software solutions, what does it mean? It has to have a
> software on a hardware box even EMC SRDF is a remote replication software
> solution.
> Is the Data Replication Manager (DRM) from HP on XP hardware comparable to
> EMC's SRDF?
> can you please clarify the hardware and software solutions difference.
> Thanks
> "Hilary Cotter" wrote:
expensive.[vbcol=seagreen]
on[vbcol=seagreen]
was[vbcol=seagreen]
data[vbcol=seagreen]
Can anyone give me a helpful link or information on the pros and cons on the
"byte by byte level replication" versus "sql server transaction replication".
Requirement:
I need upto second replication to a geographically remote site, so I was
considering sql server transactional replication versus byte by byte data
level replication between the geographically remote servers.
What are the pros and cons?
Any kind of information would be of help.
Thanks
For software solutions - Byte by byte is not good for write intensive
operations - I find it doesn't scale well.
For hardware solutions look at products like EMC's SRDF. Its very expensive.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:D3925A66-6ED5-4795-AE50-B5BA56F6D7B4@.microsoft.com...
> Hi
> Can anyone give me a helpful link or information on the pros and cons on
the
> "byte by byte level replication" versus "sql server transaction
replication".
> Requirement:
> I need upto second replication to a geographically remote site, so I was
> considering sql server transactional replication versus byte by byte data
> level replication between the geographically remote servers.
> What are the pros and cons?
> Any kind of information would be of help.
> Thanks
>
>
|||Hi
When you say software solutions, what does it mean? It has to have a
software on a hardware box even EMC SRDF is a remote replication software
solution.
Is the Data Replication Manager (DRM) from HP on XP hardware comparable to
EMC's SRDF?
can you please clarify the hardware and software solutions difference.
Thanks
"Hilary Cotter" wrote:
> For software solutions - Byte by byte is not good for write intensive
> operations - I find it doesn't scale well.
> For hardware solutions look at products like EMC's SRDF. Its very expensive.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Pari" <Pari@.discussions.microsoft.com> wrote in message
> news:D3925A66-6ED5-4795-AE50-B5BA56F6D7B4@.microsoft.com...
> the
> replication".
>
>
|||A software solution which does a byte by byte copy is DoubleTake. I believe
it has a driver which sits between your os and the disk array and monitors
byte activity, then it copies deltas to the destination server.
Data Replication Manager sounds similar to EMC's SRDF, but I am not sure if
either can run on XP.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:46724538-E7A1-46FC-A6BF-A786DF7B4508@.microsoft.com...[vbcol=seagreen]
> Hi
> When you say software solutions, what does it mean? It has to have a
> software on a hardware box even EMC SRDF is a remote replication software
> solution.
> Is the Data Replication Manager (DRM) from HP on XP hardware comparable to
> EMC's SRDF?
> can you please clarify the hardware and software solutions difference.
> Thanks
> "Hilary Cotter" wrote:
expensive.[vbcol=seagreen]
on[vbcol=seagreen]
was[vbcol=seagreen]
data[vbcol=seagreen]
Subscribe to:
Posts (Atom)