Hi all,
I have a table with customer data. Ik would like to query this table for duplicate entries, based on certain criteria.
My query to find these records:
SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1
So far so good. What I would like is to show some other data from the duplicate records. Example: suppose the above query
finds 2 records having the same criteria (postcode, huisnr, huisnrtv). How can I select other data from the
same table (e.g. name, date of birth) without using a cursor (is too expensive)? I thought of using a derived query but
I can't figure out how to do it. My desired result looks something like this:
postcode huisnr huisnrtv name date of birth
----- --- ---- ----
1111AA 13 a Smith 12/3/70
1111AA 13 a Clinton 10/2/72
2222BB 22 Bloomberg 1/8/61
2222BB 22 Bloomberg 9/2/79
2222BB 22 Pataki 2/4/71
etc.
Thanks in advance.
Diederikselect *
from tblRelatie r
join
(
SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1
) x on r.postcode=x.postcode and r.huisnr=x.huisnr and r.huisnrtv=x.huisnrtv|||Vezi daca merge asta:
select tblRelatie.* from tblRelatie join
(SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1) T1
on tblRelatie.postcode=T1.postcode and tblRelatie.huisnr=T1.huisnr and tblRelatie.huisnrtv=T1.huisnrtv
order by tblRelatie.postcode, tblRelatie.huisnr, tblRelatie.huisnrtv
ionut calin
Showing posts with label certain. Show all posts
Showing posts with label certain. Show all posts
Monday, March 26, 2012
Tuesday, March 20, 2012
row index
how can i get the row index using the "SELECT" clause?
say i have a 100-row table, and i select one that meets certain critiria.
how do i get that particular row's row index.
and also, given a row index, how can i select the row of that index?
i am looking for best practice because my table can potentially be huge
(100,000 or more rows).
- thanks.What do you mean by "row index"? SQL Server doesn't expose any internal
row identifiers to the outside world. Rows should be identifiable by a
key which is made up of some subset of columns in the table. You define
keys with a PRIMARY KEY or UNIQUE constraint.
David Portas
SQL Server MVP
--|||How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Harry" wrote:
> how can i get the row index using the "SELECT" clause?
> say i have a 100-row table, and i select one that meets certain critiria.
> how do i get that particular row's row index.
> and also, given a row index, how can i select the row of that index?
> i am looking for best practice because my table can potentially be huge
> (100,000 or more rows).
>
> - thanks.
>
>|||thanks. this worked. however it is much too slow. i have a table of 500 rows
now, and it almost takes a full second to query. any faster solutions?
basically what i want to do is the following:
i have an entity table of say 1000 rows. they have their own primary keys
(int). when i select one enity based on its own key, i want to get the
previous and next entity's key based on the alphabetically order of the
entity name.
for example, here is my table
key name
1 smith
2 calvin
3 williams
4 borg
5 albert
...
now when i select "borg", i want to figure out the key of "albert" and
"calvin" because they are the previous and next entity alphabetically. this
must be a common problem, imagining you want to show the previous and next
record of current record based on alphabetically order of the record name
instead of the numerical order of the record key.
i built a view using the MS KB article. but i had to query that view 4 times
to get both keys. maybe my queries are a bit clumsy, but each query took
about 1 second to come back. that's pretty slow considering i only have
about 500 rows now. is there a way to "cache" a view so that everytime when
you query it, it goes to the cache instead of re-build the table again? or
maybe i will have to maintain a separate key column to record the
alphabetical order of the record name?
thank you very much.
- harry
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:64E3B6A2-E763-418C-A80C-280806E17E0F@.microsoft.com...
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
> "Harry" wrote:
>|||Sorry to answer with another question but why do you want to know the
"next" and "previous" key? Maybe you are paging a result set, in which
case refer to:
http://www.aspfaq.com/show.asp?id=2120
In SQL rows are accessed by key not by any logical "position", which is
why there isn't necessarily a straightforward answer to your question.
Normally keys are sufficient for data access purposes and things like
positional order on screen can be left to the front end or middle tier.
You could access the "next" value in a sequence with a query like
SELECT MIN(name) AS next_name
FROM YourTable
WHERE name > @.current_name
Hope this helps.
David Portas
SQL Server MVP
--|||>
> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
>
this is exactly what i need. sorry i made it sounds so complicated!
thanks!
- harry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110990570.235452.46000@.o13g2000cwo.googlegroups.com...
> Sorry to answer with another question but why do you want to know the
> "next" and "previous" key? Maybe you are paging a result set, in which
> case refer to:
> http://www.aspfaq.com/show.asp?id=2120
> In SQL rows are accessed by key not by any logical "position", which is
> why there isn't necessarily a straightforward answer to your question.
> Normally keys are sufficient for data access purposes and things like
> positional order on screen can be left to the front end or middle tier.
> You could access the "next" value in a sequence with a query like
> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
sorry to keep asking stupid questions. but how can i get the id of this
record out of the same query?
obviously i could record down the "name", and do a look up to find out its
id. but is there an easier way?
thanks again.
- Harry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110990570.235452.46000@.o13g2000cwo.googlegroups.com...
> Sorry to answer with another question but why do you want to know the
> "next" and "previous" key? Maybe you are paging a result set, in which
> case refer to:
> http://www.aspfaq.com/show.asp?id=2120
> In SQL rows are accessed by key not by any logical "position", which is
> why there isn't necessarily a straightforward answer to your question.
> Normally keys are sufficient for data access purposes and things like
> positional order on screen can be left to the front end or middle tier.
> You could access the "next" value in a sequence with a query like
> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Sure, not-ANSI, but there is a way:
SELECT TOP 1 id, name
FROM YourTable
WHERE name > @.current_name
ORDER BY name
BG, SQL Server MVP
www.SolidQualityLearning.com
"Harry" <ninghai@.gmail.com> wrote in message
news:%23%23FOkDlKFHA.572@.tk2msftngp13.phx.gbl...
>
> sorry to keep asking stupid questions. but how can i get the id of this
> record out of the same query?
> obviously i could record down the "name", and do a look up to find out its
> id. but is there an easier way?
> thanks again.
> - Harry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1110990570.235452.46000@.o13g2000cwo.googlegroups.com...
>|||great thanks.
-
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:eB2XFdoKFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Sure, not-ANSI, but there is a way:
> SELECT TOP 1 id, name
> FROM YourTable
> WHERE name > @.current_name
> ORDER BY name
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Harry" <ninghai@.gmail.com> wrote in message
> news:%23%23FOkDlKFHA.572@.tk2msftngp13.phx.gbl...
>
say i have a 100-row table, and i select one that meets certain critiria.
how do i get that particular row's row index.
and also, given a row index, how can i select the row of that index?
i am looking for best practice because my table can potentially be huge
(100,000 or more rows).
- thanks.What do you mean by "row index"? SQL Server doesn't expose any internal
row identifiers to the outside world. Rows should be identifiable by a
key which is made up of some subset of columns in the table. You define
keys with a PRIMARY KEY or UNIQUE constraint.
David Portas
SQL Server MVP
--|||How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Harry" wrote:
> how can i get the row index using the "SELECT" clause?
> say i have a 100-row table, and i select one that meets certain critiria.
> how do i get that particular row's row index.
> and also, given a row index, how can i select the row of that index?
> i am looking for best practice because my table can potentially be huge
> (100,000 or more rows).
>
> - thanks.
>
>|||thanks. this worked. however it is much too slow. i have a table of 500 rows
now, and it almost takes a full second to query. any faster solutions?
basically what i want to do is the following:
i have an entity table of say 1000 rows. they have their own primary keys
(int). when i select one enity based on its own key, i want to get the
previous and next entity's key based on the alphabetically order of the
entity name.
for example, here is my table
key name
1 smith
2 calvin
3 williams
4 borg
5 albert
...
now when i select "borg", i want to figure out the key of "albert" and
"calvin" because they are the previous and next entity alphabetically. this
must be a common problem, imagining you want to show the previous and next
record of current record based on alphabetically order of the record name
instead of the numerical order of the record key.
i built a view using the MS KB article. but i had to query that view 4 times
to get both keys. maybe my queries are a bit clumsy, but each query took
about 1 second to come back. that's pretty slow considering i only have
about 500 rows now. is there a way to "cache" a view so that everytime when
you query it, it goes to the cache instead of re-build the table again? or
maybe i will have to maintain a separate key column to record the
alphabetical order of the record name?
thank you very much.
- harry
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:64E3B6A2-E763-418C-A80C-280806E17E0F@.microsoft.com...
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
> "Harry" wrote:
>|||Sorry to answer with another question but why do you want to know the
"next" and "previous" key? Maybe you are paging a result set, in which
case refer to:
http://www.aspfaq.com/show.asp?id=2120
In SQL rows are accessed by key not by any logical "position", which is
why there isn't necessarily a straightforward answer to your question.
Normally keys are sufficient for data access purposes and things like
positional order on screen can be left to the front end or middle tier.
You could access the "next" value in a sequence with a query like
SELECT MIN(name) AS next_name
FROM YourTable
WHERE name > @.current_name
Hope this helps.
David Portas
SQL Server MVP
--|||>
> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
>
this is exactly what i need. sorry i made it sounds so complicated!
thanks!
- harry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110990570.235452.46000@.o13g2000cwo.googlegroups.com...
> Sorry to answer with another question but why do you want to know the
> "next" and "previous" key? Maybe you are paging a result set, in which
> case refer to:
> http://www.aspfaq.com/show.asp?id=2120
> In SQL rows are accessed by key not by any logical "position", which is
> why there isn't necessarily a straightforward answer to your question.
> Normally keys are sufficient for data access purposes and things like
> positional order on screen can be left to the front end or middle tier.
> You could access the "next" value in a sequence with a query like
> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
sorry to keep asking stupid questions. but how can i get the id of this
record out of the same query?
obviously i could record down the "name", and do a look up to find out its
id. but is there an easier way?
thanks again.
- Harry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110990570.235452.46000@.o13g2000cwo.googlegroups.com...
> Sorry to answer with another question but why do you want to know the
> "next" and "previous" key? Maybe you are paging a result set, in which
> case refer to:
> http://www.aspfaq.com/show.asp?id=2120
> In SQL rows are accessed by key not by any logical "position", which is
> why there isn't necessarily a straightforward answer to your question.
> Normally keys are sufficient for data access purposes and things like
> positional order on screen can be left to the front end or middle tier.
> You could access the "next" value in a sequence with a query like
> SELECT MIN(name) AS next_name
> FROM YourTable
> WHERE name > @.current_name
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Sure, not-ANSI, but there is a way:
SELECT TOP 1 id, name
FROM YourTable
WHERE name > @.current_name
ORDER BY name
BG, SQL Server MVP
www.SolidQualityLearning.com
"Harry" <ninghai@.gmail.com> wrote in message
news:%23%23FOkDlKFHA.572@.tk2msftngp13.phx.gbl...
>
> sorry to keep asking stupid questions. but how can i get the id of this
> record out of the same query?
> obviously i could record down the "name", and do a look up to find out its
> id. but is there an easier way?
> thanks again.
> - Harry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1110990570.235452.46000@.o13g2000cwo.googlegroups.com...
>|||great thanks.
-
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:eB2XFdoKFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Sure, not-ANSI, but there is a way:
> SELECT TOP 1 id, name
> FROM YourTable
> WHERE name > @.current_name
> ORDER BY name
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Harry" <ninghai@.gmail.com> wrote in message
> news:%23%23FOkDlKFHA.572@.tk2msftngp13.phx.gbl...
>
Friday, March 9, 2012
row character-width limit
I recently noticed that SQL Server 2000 has an 8060-character limit on
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!
For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!
|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!
For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!
|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
row character-width limit
I recently noticed that SQL Server 2000 has an 8060-character limit on
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
row character-width limit
I recently noticed that SQL Server 2000 has an 8060-character limit on
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
Saturday, February 25, 2012
Round funtion on entire columns in MSSQL?
Hi,
I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:
update gbkmut
set bdr_hfl = round(bdr_hfl,2)
can anyone help me?
cheers,
steveOn 5 May 2004 02:40:15 -0700, steve wrote:
>Hi,
>I'd like to round all amounts in a certain column to 2 decimals.
>I tried the following query, but eventhough the syntax is correct, it
>doesn't give any result:
>update gbkmut
>set bdr_hfl = round(bdr_hfl,2)
>can anyone help me?
>cheers,
>steve
Hi Steve,
What do you mean with "doesn't give any result"?
If you mean that no rows were returned by the update statement, then
this is expected behaviour. An UPDATE-statement will update the data,
nothing more nothing less. Use SELECT if you want to see anything.
If you mean that after executing the above UPDATE, you still have data
with more than two non-zero digits after the decimal point, I'd ask
you to post more details (table definition in the form of CREATE TABLE
statements, sample data in the form of INSERT statements, expected
output and the output you got) so that others can try if they can
reproduce this apparantly erroneous behaviour.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||stefan.van.den.steen@.exact.be (steve) wrote in message news:<79d8a1b0.0405050140.6f442694@.posting.google.com>...
> Hi,
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)
> can anyone help me?
> cheers,
> steve
At first glance, your UPDATE statement seems to be OK. Can you give
some more information? In particular, what is the data type of the
bdr_hfl column, and can you give some sample values, as well as your
expected result? And what does "doesn't give any result" mean?
Simon|||steve (stefan.van.den.steen@.exact.be) writes:
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)
There is very little information, but I would guess that your column is
of datatype float. Float is an approxamite datatype, which means that
far from all values can be stored exactly in a float. For instance,
try this:
select convert(float, 1.89)
In Query Analyzer this displays as 1.8899999999999999.
In many situations, it is possible to cope with these extra decimals at
the end; you only need some care. If you need exact numbers, you must
use the decimal type instad. The drawback is that with decimal, you
must decide from the beginning which range you handle.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)