How to from 1*apple,1*orange,1*apple to 2*apple,1*orange

Lets say there is a data table called ShoppingBasket. It has fields like "product", "price", "quantity" and "total" and so on. It is possible to have duplicates, but I think it is better to run a query and remove them. And update quantities. In the end, I plan to calculate total price.

For total price there seems to be a field or property "Formula" in column properties.

As this is really two questions, I take any help.

Leif

We need to see how your data looks like and also how you want it updated..

|||

"We need to see how your data looks like and also how you want it updated.." Here it comes.

I have a table called t_shopping_basket. There the user inserts items from products table "t_Tuote". I use this query:

"INSERT dbo.t_shopping_basket (Product_code, Name,Model,Quantity,Price,Alv) SELECT Tuotekoodi,Name,Model,Toimittajanimi,@.Quantity,Price,Alv FROM dbo.t_Tuote WHERE Product_code=@.Product_code", conn) ".

I have included also a gridView of "shopping basket" Its query is like:

"UPDATE [t_shopping_basket] SET [Product_code] = @.Product_code,[Quantity] = @.Quantity,[Name] = @.Name WHERE [Product_code] = @.Product_code"

Nothing prevents users pressing buy several times, so same item can be there in many places. If I leave them there, it has its good positive and negative sides. I probably should put an extra field like "item index" or so into the table then. Or I could leave quantity field out. 3 pieces means 3 rows of something.

Best way to my mind is to delete or prevent duplicates. But then, I have to find those duplicates first, and when I delete a record, I must increase item quantity. This feels like a complicated thing to do, especially with query.

How have others done this? What could my query look like?

Regards

Leif

How to free up the space ...

Hi
DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move
> the data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>Hi,
The update using rtrim was not needed here as SQL Server does not store
spaces after the data. If you insert 'Pink Floyd ' SQL Server only
stores 'Pink Floyd'. Changing to varchar was enough.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Atenza" wrote:

> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move th
e
> data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>
>|||Hi all,
I have a table with column A char(1000) and then alter the table to
varchar(1000) and update tableA set A = rtrim(A)
I know a stupid way to free up the space by create a new table and move the
data to the new table. Is there a command to free up the empty space?
Thanks in advance!|||Hi
DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move
> the data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>|||Hi,
The update using rtrim was not needed here as SQL Server does not store
spaces after the data. If you insert 'Pink Floyd ' SQL Server only
stores 'Pink Floyd'. Changing to varchar was enough.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Atenza" wrote:

> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move th
e
> data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>
>|||Hi Ben,
I have done few tests on char and varchar. I found that insert 'Pink Floyd
' is different from ''Pink Floyd' into varchar. SQL Server does not store
spaces after the data applied to SQLSever 2005? coz i am using SQLSever
2000, is this the reason?
In CASE 1, use char(100)
In CASE 2, use varchar(100) by insert 'Pink Floyd '
In CASE 3, use varchar(100) by insert 'Pink Floyd'
In CASE 4, use char(100) and alter to varchar(100)
In CASE 5, use char(100) and move to new table varchar(100)
In CASE 4, for new create data, i think it should be saved spaces. But for
the old data, it seems that spaces cannot be released.
It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or
i have misunderstand something?
Here is my test result:
CASE 1:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
db size 16128KB
CASE 2:
CREATE TABLE dbo.Table2
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table2 values('Pink Floyd ')
while (select count(*) from table2) < 100000
insert into table2 select * from table2
sp_spaceused table2
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table1 131072 4552 KB 4488 KB 8 KB
56 KB
db size 5504KB
CASE 3:
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 values('Pink Floyd')
while (select count(*) from table3) < 100000
insert into table3 select * from table3
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
db size 4096KB
CASE 4:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table1 131072 15240 KB 15200 KB 8 KB
32 KB
CASE 5:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 select rtrim(col1) from table1
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...[vbcol=seagreen]
> Hi,
> The update using rtrim was not needed here as SQL Server does not store
> spaces after the data. If you insert 'Pink Floyd ' SQL Server
> only
> stores 'Pink Floyd'. Changing to varchar was enough.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Atenza" wrote:
>|||> SQL Server does not store
> spaces after the data applied to SQLSever 2005?
For char, SQL Server always store the specified length. It pads the string w
ith spaces.
For varchar, SQL Server by default store trailing spaces. Whether or not to
do this depends on the
setting of ANSI_PADDING when the table/column is *created*.
Rebuilding the indexes on the table should give you back the space. If the t
able doesn't have a
clustered index, then you either have to create on (and possibly drop it), o
r export/import.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.
gbl...
> Hi Ben,
> I have done few tests on char and varchar. I found that insert 'Pink Floyd
' is different from
> ''Pink Floyd' into varchar. SQL Server does not store
> spaces after the data applied to SQLSever 2005? coz i am using SQLSever 20
00, is this the reason?
> In CASE 1, use char(100)
> In CASE 2, use varchar(100) by insert 'Pink Floyd '
> In CASE 3, use varchar(100) by insert 'Pink Floyd'
> In CASE 4, use char(100) and alter to varchar(100)
> In CASE 5, use char(100) and move to new table varchar(100)
> In CASE 4, for new create data, i think it should be saved spaces. But for
the old data, it seems
> that spaces cannot be released.
> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution
or i have misunderstand
> something?
>
> Here is my test result:
> CASE 1:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> db size 16128KB
>
> CASE 2:
> CREATE TABLE dbo.Table2
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table2 values('Pink Floyd ')
> while (select count(*) from table2) < 100000
> insert into table2 select * from table2
> sp_spaceused table2
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
> db size 5504KB
>
> CASE 3:
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 values('Pink Floyd')
> while (select count(*) from table3) < 100000
> insert into table3 select * from table3
> sp_spaceused table3
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
> db size 4096KB
>
> CASE 4:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> COMMIT
> sp_spaceused table1
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>
>
> CASE 5:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 select rtrim(col1) from table1
> sp_spaceused table3
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>
>
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>|||Hi Ben,
I have done few tests on char and varchar. I found that insert 'Pink Floyd
' is different from ''Pink Floyd' into varchar. SQL Server does not store
spaces after the data applied to SQLSever 2005? coz i am using SQLSever
2000, is this the reason?
In CASE 1, use char(100)
In CASE 2, use varchar(100) by insert 'Pink Floyd '
In CASE 3, use varchar(100) by insert 'Pink Floyd'
In CASE 4, use char(100) and alter to varchar(100)
In CASE 5, use char(100) and move to new table varchar(100)
In CASE 4, for new create data, i think it should be saved spaces. But for
the old data, it seems that spaces cannot be released.
It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or
i have misunderstand something?
Here is my test result:
CASE 1:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
db size 16128KB
CASE 2:
CREATE TABLE dbo.Table2
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table2 values('Pink Floyd ')
while (select count(*) from table2) < 100000
insert into table2 select * from table2
sp_spaceused table2
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table1 131072 4552 KB 4488 KB 8 KB
56 KB
db size 5504KB
CASE 3:
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 values('Pink Floyd')
while (select count(*) from table3) < 100000
insert into table3 select * from table3
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
db size 4096KB
CASE 4:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table1 131072 15240 KB 15200 KB 8 KB
32 KB
CASE 5:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 select rtrim(col1) from table1
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
-
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...[vbcol=seagreen]
> Hi,
> The update using rtrim was not needed here as SQL Server does not store
> spaces after the data. If you insert 'Pink Floyd ' SQL Server
> only
> stores 'Pink Floyd'. Changing to varchar was enough.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Atenza" wrote:
>|||> SQL Server does not store
> spaces after the data applied to SQLSever 2005?
For char, SQL Server always store the specified length. It pads the string w
ith spaces.
For varchar, SQL Server by default store trailing spaces. Whether or not to
do this depends on the
setting of ANSI_PADDING when the table/column is *created*.
Rebuilding the indexes on the table should give you back the space. If the t
able doesn't have a
clustered index, then you either have to create on (and possibly drop it), o
r export/import.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.
gbl...
> Hi Ben,
> I have done few tests on char and varchar. I found that insert 'Pink Floyd
' is different from
> ''Pink Floyd' into varchar. SQL Server does not store
> spaces after the data applied to SQLSever 2005? coz i am using SQLSever 20
00, is this the reason?
> In CASE 1, use char(100)
> In CASE 2, use varchar(100) by insert 'Pink Floyd '
> In CASE 3, use varchar(100) by insert 'Pink Floyd'
> In CASE 4, use char(100) and alter to varchar(100)
> In CASE 5, use char(100) and move to new table varchar(100)
> In CASE 4, for new create data, i think it should be saved spaces. But for
the old data, it seems
> that spaces cannot be released.
> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution
or i have misunderstand
> something?
>
> Here is my test result:
> CASE 1:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> db size 16128KB
>
> CASE 2:
> CREATE TABLE dbo.Table2
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table2 values('Pink Floyd ')
> while (select count(*) from table2) < 100000
> insert into table2 select * from table2
> sp_spaceused table2
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
> db size 5504KB
>
> CASE 3:
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 values('Pink Floyd')
> while (select count(*) from table3) < 100000
> insert into table3 select * from table3
> sp_spaceused table3
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
> db size 4096KB
>
> CASE 4:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> COMMIT
> sp_spaceused table1
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>
>
> CASE 5:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 select rtrim(col1) from table1
> sp_spaceused table3
> name rows reserved data index_size unus
ed
> -- -- -- -- --
--
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>
>
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>|||thank you very much!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:utEoaQQkGHA.4672@.TK2MSFTNGP02.phx.gbl...
> Hi
> DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>sql

How to free up the space ...

Hi all,
I have a table with column A char(1000) and then alter the table to
varchar(1000) and update tableA set A = rtrim(A)
I know a stupid way to free up the space by create a new table and move the
data to the new table. Is there a command to free up the empty space?
Thanks in advance!Hi
DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move
> the data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>|||Hi,
The update using rtrim was not needed here as SQL Server does not store
spaces after the data. If you insert 'Pink Floyd ' SQL Server only
stores 'Pink Floyd'. Changing to varchar was enough.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Atenza" wrote:
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move the
> data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>
>|||Hi Ben,
I have done few tests on char and varchar. I found that insert 'Pink Floyd
' is different from ''Pink Floyd' into varchar. SQL Server does not store
spaces after the data applied to SQLSever 2005? coz i am using SQLSever
2000, is this the reason?
In CASE 1, use char(100)
In CASE 2, use varchar(100) by insert 'Pink Floyd '
In CASE 3, use varchar(100) by insert 'Pink Floyd'
In CASE 4, use char(100) and alter to varchar(100)
In CASE 5, use char(100) and move to new table varchar(100)
In CASE 4, for new create data, i think it should be saved spaces. But for
the old data, it seems that spaces cannot be released.
It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or
i have misunderstand something?
Here is my test result:
CASE 1:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
db size 16128KB
CASE 2:
CREATE TABLE dbo.Table2
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table2 values('Pink Floyd ')
while (select count(*) from table2) < 100000
insert into table2 select * from table2
sp_spaceused table2
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 4552 KB 4488 KB 8 KB
56 KB
db size 5504KB
CASE 3:
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 values('Pink Floyd')
while (select count(*) from table3) < 100000
insert into table3 select * from table3
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
db size 4096KB
CASE 4:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 15240 KB 15200 KB 8 KB
32 KB
CASE 5:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 select rtrim(col1) from table1
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
> Hi,
> The update using rtrim was not needed here as SQL Server does not store
> spaces after the data. If you insert 'Pink Floyd ' SQL Server
> only
> stores 'Pink Floyd'. Changing to varchar was enough.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>|||> SQL Server does not store
> spaces after the data applied to SQLSever 2005?
For char, SQL Server always store the specified length. It pads the string with spaces.
For varchar, SQL Server by default store trailing spaces. Whether or not to do this depends on the
setting of ANSI_PADDING when the table/column is *created*.
Rebuilding the indexes on the table should give you back the space. If the table doesn't have a
clustered index, then you either have to create on (and possibly drop it), or export/import.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Ben,
> I have done few tests on char and varchar. I found that insert 'Pink Floyd ' is different from
> ''Pink Floyd' into varchar. SQL Server does not store
> spaces after the data applied to SQLSever 2005? coz i am using SQLSever 2000, is this the reason?
> In CASE 1, use char(100)
> In CASE 2, use varchar(100) by insert 'Pink Floyd '
> In CASE 3, use varchar(100) by insert 'Pink Floyd'
> In CASE 4, use char(100) and alter to varchar(100)
> In CASE 5, use char(100) and move to new table varchar(100)
> In CASE 4, for new create data, i think it should be saved spaces. But for the old data, it seems
> that spaces cannot be released.
> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or i have misunderstand
> something?
>
> Here is my test result:
> CASE 1:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> db size 16128KB
>
> CASE 2:
> CREATE TABLE dbo.Table2
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table2 values('Pink Floyd ')
> while (select count(*) from table2) < 100000
> insert into table2 select * from table2
> sp_spaceused table2
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
> db size 5504KB
>
> CASE 3:
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 values('Pink Floyd')
> while (select count(*) from table3) < 100000
> insert into table3 select * from table3
> sp_spaceused table3
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
> db size 4096KB
>
> CASE 4:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> COMMIT
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>
>
> CASE 5:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 select rtrim(col1) from table1
> sp_spaceused table3
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>
>
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>> Hi,
>> The update using rtrim was not needed here as SQL Server does not store
>> spaces after the data. If you insert 'Pink Floyd ' SQL Server only
>> stores 'Pink Floyd'. Changing to varchar was enough.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>|||thank you very much!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:utEoaQQkGHA.4672@.TK2MSFTNGP02.phx.gbl...
> Hi
> DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>|||thank you very much! it works!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKxJ6QSkGHA.4652@.TK2MSFTNGP05.phx.gbl...
>> SQL Server does not store
>> spaces after the data applied to SQLSever 2005?
> For char, SQL Server always store the specified length. It pads the string
> with spaces.
> For varchar, SQL Server by default store trailing spaces. Whether or not
> to do this depends on the setting of ANSI_PADDING when the table/column is
> *created*.
> Rebuilding the indexes on the table should give you back the space. If the
> table doesn't have a clustered index, then you either have to create on
> (and possibly drop it), or export/import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>> Hi Ben,
>> I have done few tests on char and varchar. I found that insert 'Pink
>> Floyd ' is different from ''Pink Floyd' into varchar. SQL Server does not
>> store
>> spaces after the data applied to SQLSever 2005? coz i am using SQLSever
>> 2000, is this the reason?
>> In CASE 1, use char(100)
>> In CASE 2, use varchar(100) by insert 'Pink Floyd '
>> In CASE 3, use varchar(100) by insert 'Pink Floyd'
>> In CASE 4, use char(100) and alter to varchar(100)
>> In CASE 5, use char(100) and move to new table varchar(100)
>> In CASE 4, for new create data, i think it should be saved spaces. But
>> for the old data, it seems that spaces cannot be released.
>> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution
>> or i have misunderstand something?
>>
>> Here is my test result:
>> CASE 1:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> db size 16128KB
>>
>> CASE 2:
>> CREATE TABLE dbo.Table2
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table2 values('Pink Floyd ')
>> while (select count(*) from table2) < 100000
>> insert into table2 select * from table2
>> sp_spaceused table2
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
>> db size 5504KB
>>
>> CASE 3:
>> CREATE TABLE dbo.Table3
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table3 values('Pink Floyd')
>> while (select count(*) from table3) < 100000
>> insert into table3 select * from table3
>> sp_spaceused table3
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>> db size 4096KB
>>
>> CASE 4:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> BEGIN TRANSACTION
>> SET QUOTED_IDENTIFIER ON
>> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> SET ARITHABORT ON
>> SET NUMERIC_ROUNDABORT OFF
>> SET CONCAT_NULL_YIELDS_NULL ON
>> SET ANSI_NULLS ON
>> SET ANSI_PADDING ON
>> SET ANSI_WARNINGS ON
>> COMMIT
>> BEGIN TRANSACTION
>> CREATE TABLE dbo.Tmp_Table1
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> IF EXISTS(SELECT * FROM dbo.Table1)
>> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
>> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
>> GO
>> DROP TABLE dbo.Table1
>> GO
>> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
>> GO
>> COMMIT
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>>
>>
>> CASE 5:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> CREATE TABLE dbo.Table3
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table3 select rtrim(col1) from table1
>> sp_spaceused table3
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>>
>>
>>
>> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
>> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>> Hi,
>> The update using rtrim was not needed here as SQL Server does not store
>> spaces after the data. If you insert 'Pink Floyd ' SQL Server
>> only
>> stores 'Pink Floyd'. Changing to varchar was enough.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>>
>

How to format numbers in SQL Query

any body have an idea abou how to wirte a function for formating a numeric field.
Ex: In my table the TotalAmout is a numeric field. if i use (Select TotalAmount from Table1) then query will return numbers like

Totalamount
-------
12232.88
23233.22
23559.99
32434.99

but i want he result like comma separated format
like

12,232.88
23,233.22
23,559.99
32,434.99Create the below function and use it as said below.

/*This function is only for thousand separator for numbers with length 5 or 4*/
CREATE FUNCTION DBO.SEPARATETHOUSANDNUM
(
@.STRVALUE VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @.STRRETURNVALUE VARCHAR(8000)
SELECT @.STRRETURNVALUE = CASE LEN(@.STRVALUE)
WHEN 5 THEN LEFT(@.STRVALUE,2)+ ','+ RIGHT(@.STRVALUE,3)
WHEN 4 THEN LEFT(@.STRVALUE,1)+ ','+ RIGHT(@.STRVALUE,3)
ELSE @.STRVALUE END
RETURN @.STRRETURNVALUE
END

SELECT DBO.SEPAREATENUMBERS(23565) AS CHANGEDCOLUMN

gives 23,565

SELECT DBO.SEPAREATENUMBERS(2365) AS CHANGEDCOLUMN

gives 2,365

So use
Select DBO.SEPARATETHOUSANDNUM(TotalAmount) from Table1

Quote:

Originally Posted by sukeshchand

any body have an idea abou how to wirte a function for formating a numeric field.
Ex: In my table the TotalAmout is a numeric field. if i use (Select TotalAmount from Table1) then query will return numbers like

Totalamount
-------
12232.88
23233.22
23559.99
32434.99

but i want he result like comma separated format
like

12,232.88
23,233.22
23,559.99
32,434.99

|||I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(momey,TotalAmount),1) from BillMaser|||

Quote:

Originally Posted by sukeshchand

I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(momey,TotalAmount),1) from BillMaser

Excellent! Thanks for posting the solution!|||

Quote:

Originally Posted by sukeshchand

I got an another easy solution for that and no need for any functions

like this

select convert(varchar(50),convert(money,TotalAmount),1) from BillMaser

i'm using this query, it works, but my problem now is that this query automatically rounds to 2 decimal places.

ex.
1200.114 = 1,200.11

is there a query that formats the result but does not round the decimals?|||

Quote:

Originally Posted by mjv

i'm using this query, it works, but my problem now is that this query automatically rounds to 2 decimal places.

ex.
1200.114 = 1,200.11

is there a query that formats the result but does not round the decimals?

try adding precision on your convert function.

actually, although this is feasible in the database/back-end, i believe this can be better be handled in the front-end.

How to format leave detail into tabular/pivot format?

Hello Expert!

I need help to I translate this data...

Table "LeaveDetail"

StaffNo | StartDate | EndDate | LeaveType |
1 | 23/04/2006 | 26/04/2006 | AL |
2 | 24/04/2006 | 25/04/2006 | MC |
3 | 26/04/2006 | 27/04/2006 | EL |
1 | 30/04/2006 | 02/05/2006 | EL |

Into this format...

|Apr|Apr|Apr|Apr|Apr|Apr|Apr|Apr|May|May|May|May|
StaffNo |23 |24 |25 |26 |27 |28 |29 |30 |01 |02 |03 |04..

1 |AL |AL |AL |AL | | ... |EL |EL |EL |
2 | |MC |MC | | |
3 | | | |EL |EL |

Parameter:
Date From e.g. 23/04/2006 to 23/05/2006

Using only query statement...

Is this possible?

TIA

Regards.

WOuld be very heavy query, do you have a calendar table to join to ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||You could do it in a SELECT statement or using PIVOT operator in SQL Server 2005. But there is no dynamic aspect for the query i.e., the names of the columns (Apr 23, Apr 24) should be hard-coded in the SELECT statement unless you generate the column names at run-time and use dynamic SQL to execute the query. So your options are fairly limited. On the other hand, this type of pivot operation is a breeze to do on the client side. Any reporting tool wll handle this without a problem. So if it is a one-time affair then you can write a SELECT statement to get the expected results. Otherwise you will have to use a solution that is easy to maintain and extend based on what I described above.|||

Hmm..

No I don' have, appreciate if you can guide me on that

|||

I forgot to mention I’m using MS SQL Server 7

I found similar solution in MS Access. All done with 1 Table for days + query to convert to pivot/crosstab format, no coding in client side and no formatting in reporting tool needed

e.g.

TblDay e.g.: 1,2,3,4....31

The query:

PARAMETERS [Enter Month] Text ( 255 ), [Enter Year] Text ( 255 );

TRANSFORM First(LeaveDetail.LeaveType) AS FirstOfLeaveType

SELECT EmpNo
FROM tblDays, LeaveDetail
WHERE DateSerial([Enter Year],[Enter Month],[Day])) Between [LeaveDetail].[dStartDate] And [LeaveDetail].[dEndDate]
GROUP BY EmpNo
ORDER BY EmpNo

PIVOT tblDays.Day In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

The result will look like this:

StaffNo |1 |2 |3 |4 |5 |6 |7 |8 |9 |10 |11 |12..

1 |AL |AL |AL |AL | | ... |EL |EL |EL |

I know there are no such functions for tabular format in SQL Server 7, so I’m here to find similar solution that producing result as I have mention earlier which not depend on client or reporting tool

Is this possible?

|||

this thing looks like a cross tab queries.

maybe some examples from here will help.

http://www.sqlmag.com/Article/ArticleID/15608/15608.html

pls see the zip files

here some more

http://www.sqlteam.com/item.asp?ItemID=2955

you have do it thru dynamic query

|||

Thanks, I'll try

Regards

how to format in to dd/mm/yyyy ?

hi all,

i have table field name call

Start_date varchar(16)

when i select data from that filed values it gives me

Eg:

select Start_date from Customer

20011224 00:00:0
20011004 00:00:0

but i want to convert this data in to dd/mm/yyyy format ?

like ! 24/12/2001

04/10/2001

how do i do this task ?

regards

sujithf

create table #format (

start_Date_time varchar(16)

)

insert into #format values('20011224 00:00:0')

select convert(varchar(16), cast(start_date_time as datetime), 103) from #format

--103 is a British/French date format "dd/mm/yyyy"

|||

thanks very much.....

regards

sujithf

How to format in SQL

Hi All,

I have a serial number field in table. Field type is integer. It is just stored as 1,2,3,12,13, etc.

It is showing as 00001,00002,00003,00012,00013 in interface. C# string format is very easy to changed the format.

But when i export to excel there is a problem. Let me know how to format string in SQL and export to excel.

Thanks

Aung

Hi Aung,

You may want to try this

select right('00000'+cast(serialno as varchar(5)),5) from table

|||

select right('0000'+cast(serialno as varchar(5)),5) from table

should work.

Or you can use:

SELECTRIGHT('0000'+CONVERT(VARCHAR(5),serialno),5) FROM yourTable

|||

Thanks BRO...

This is what I want.

How to format cell according to different data type

Hi,
I have a table cell which could accomodate date,currency,numeric at run
time,how do I set up individual format for each type? I don't know vb
so I'd appreciated for any help.
ThanksGetting the difference between curency and numeric is a challenge, for the
rest you can do a nested iif expression for the format property eg:
=iif(isdate( Fields!Yourfield.Value),"dd MMM
yyyy",isnumeric(Fields!Yourfield.Value),"N","C")
"ottawa111" wrote:
> Hi,
> I have a table cell which could accomodate date,currency,numeric at run
> time,how do I set up individual format for each type? I don't know vb
> so I'd appreciated for any help.
> Thanks
>|||Thanks a lot

How to format cell / data apprearecnce under a table

Hello All,

I uploaded custtable under the database, the data looks fine except that the name that apprears has a lot of distance e.g

it should be :

firstname lastname however the format appears very strange:

firstname lastname

firstname lastname

fistname lastname

Same is the case with the address, I need to adjust or format the apperance that appears on the cell. Is there a way/ sql statement to format the data under the table so that the apprearence looks okay.

I will really appreciate any sort of help on this one.

Thanks,

Rashi

Check the positioning properties of the grid/cell.

You could try trimming leading 'space' characters in the SELECT query, e.g., ltrim( FirstName ), ltrim( LastName ).

How to form a list ?

Hi,

I have a table like this:

CatalogID CatalogName RootID
--
1 Microsoft 0
2 Macromedia 0
3 Office 1
4 Flash 2
5 MSN 1
6 Dreamweaver 2
7 Firework 2
8 Visual Studio 1

I want to form a list about company and product using SQL language.

Example:

Microsoft
Office
MSN
Visual Studio
Macromedia
Flash
Dreamweaver
Firework

How can I do ? Thank you.

there are many solution to this problem

you can do a self join or a union

here's a union example

create table test
(
catalogid int,
catalogname varchar(20),
rootid int
)
truncate table test

insert test select 1,'Microsoft', 0
insert test select 2,'Macromedia', 0
insert test select 3,'Office',1
insert test select 4,'Flash',2
insert test select 5,'MSN',1
insert test select 6,'Dreamweaver', 2
insert test select 7,'Firework',2
insert teSt select 8,'Visual Studio',1

SELECT COMPANY, PRODUCT FROM
(
select CATALOGID, ROOTID, CATALOGNAME AS COMPANY,NULL AS PRODUCT FROM TEST WHERE ROOTID=0
UNION
select ROOTID, CATALOGID, NULL AS COMPANY,CATALOGNAME AS PRODUCT FROM TEST WHERE ROOTID<>0
)AS X
ORDER BY CATALOGID,ROOTID

|||

Thanks.

Just as you do it , it will display two columns.

Company Product
Microsoft NULL
NULL Windows
NULL Office
NULL MSN
Macromedia NULL
NULL Flash
NULL Dreamweaver
NULL Firework

But I want to display these in one column, because I want to use "CPList" table as a DropDownList Control's DataSource in my web application.

And in front of these products' name is serval spaces.

CPList
Microsoft
Office
MSN
Visual Studio
Macromedia
Flash
Dreamweaver
Firework

Can you help again? Thank you very much.

|||

edited

this should do the trick

SELECT catalogname FROM
(
select CATALOGID, ROOTID, CATALOGNAME FROM TEST WHERE ROOTID=0
UNION
select ROOTID, CATALOGID,' ' +CATALOGNAME FROM TEST WHERE ROOTID<>0
)AS X
ORDER BY CATALOGID,ROOTID

|||i edited the post. that should worksql

How to force View to change automatically when table schema changes?

I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.

And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?Is your view an indexed view?|||Originally posted by sbaru
Is your view an indexed view?

Nope, it's not an indexed view, the view joins a few tables so it's not eligible for that.

Another strange thing is that if I select Open View the results is still from the old view, but if I do Design View the output from running the SQL is correct (not the same as what I saw when I did Open View). It's only when I deliberately change a field or two in the view in Design mode will the Open View gives me the correct output. That's why I am thinking the View is still using the old, compiled execution plan until I changed something in the View. That is a pain though since I am constanly change table schema.|||You should run :

--First option
EXEC sp_refreshview @.ViewName

--Secnod option

CREATE PROCEDURE REFRESH_ALL_VIEWS
AS
DECLARE @.ViewName varchar(100)
DECLARE curViews CURSOR FOR select name from sysobjects where xtype='V'
OPEN curViews
FETCH NEXT FROM curViews INTO @.ViewName
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @.ViewName
FETCH NEXT FROM curViews INTO @.ViewName
END
CLOSE curViews
DEALLOCATE curViews

GO|||Beautiful! Thank you!

How to force unique entries in a linking table?

I have a table 'Group2Operation' that stores many to many relations
between the 'Group' table and the 'Operation' table (each group is has
permission to perform one or more of the available operations)

PROBLEM
=======
I need to prevent duplicate entries being created. e.g. lets say that
in the 'Group2Operation' table a record links the 'editor' group to
the 'publish' operation. Should I prevent an administrator creating a
duplicate of that record? (Otherwise deleting that permission will
have to be done twice or more for it to be effective)

SOLUTION?
=========
So far I've done this with a trigger:

CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2Operation
FOR INSERT, UPDATE
AS UPDATE Group2Operation
SET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTag

The 'NoDuplicate' unique index column in the Group2Operation table
stores a concatenation of the unique group and operation identifiers.
So when an attempt is made to create a record, the trigger is fired.
If there is a duplicate, this will mean a duplicate entry in the
'NoDuplicate' column. As a result, the INSERT or UPDATE will fail and
the duplication will be prevented.

WHAT DO YOU THINK?
==================
What do you think? Am I going about this in the right way? Is a
trigger a good way to do this or should I rely on application logic to
prevent duplicates?

Any help appreciated by this db novice.
John GristConsider creating a PRIMARY KEY or UNIQUE constraint on the Group2Operation
table. This will ensure duplicate entries cannot be inserted. There is no
need to concatenate values or use a separate table since you can specify a
composite key:

ALTER TABLE Group2Operation
ADD CONSTRAINT PK_Group2Operation
PRIMARY KEY (GroupID, OperationTag)

BTW, it's a good practice to post DDL (create table) when posting questions
to this forum.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"grist2mill" <grist2mill@.excite.com> wrote in message
news:46e240e.0409030059.79f2f36d@.posting.google.co m...
>I have a table 'Group2Operation' that stores many to many relations
> between the 'Group' table and the 'Operation' table (each group is has
> permission to perform one or more of the available operations)
> PROBLEM
> =======
> I need to prevent duplicate entries being created. e.g. lets say that
> in the 'Group2Operation' table a record links the 'editor' group to
> the 'publish' operation. Should I prevent an administrator creating a
> duplicate of that record? (Otherwise deleting that permission will
> have to be done twice or more for it to be effective)
> SOLUTION?
> =========
> So far I've done this with a trigger:
> CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2Operation
> FOR INSERT, UPDATE
> AS UPDATE Group2Operation
> SET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTag
> The 'NoDuplicate' unique index column in the Group2Operation table
> stores a concatenation of the unique group and operation identifiers.
> So when an attempt is made to create a record, the trigger is fired.
> If there is a duplicate, this will mean a duplicate entry in the
> 'NoDuplicate' column. As a result, the INSERT or UPDATE will fail and
> the duplication will be prevented.
> WHAT DO YOU THINK?
> ==================
> What do you think? Am I going about this in the right way? Is a
> trigger a good way to do this or should I rely on application logic to
> prevent duplicates?
> Any help appreciated by this db novice.
> John Grist|||That looks much better. I though there ought to be a better way.
Many thanks for your help
John

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<uh_Zc.14996$lu3.12768@.newssvr24.news.prodigy.com>...
> Consider creating a PRIMARY KEY or UNIQUE constraint on the Group2Operation
> table. This will ensure duplicate entries cannot be inserted. There is no
> need to concatenate values or use a separate table since you can specify a
> composite key:
> ALTER TABLE Group2Operation
> ADD CONSTRAINT PK_Group2Operation
> PRIMARY KEY (GroupID, OperationTag)
> BTW, it's a good practice to post DDL (create table) when posting questions
> to this forum.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "grist2mill" <grist2mill@.excite.com> wrote in message
> news:46e240e.0409030059.79f2f36d@.posting.google.co m...
> >I have a table 'Group2Operation' that stores many to many relations
> > between the 'Group' table and the 'Operation' table (each group is has
> > permission to perform one or more of the available operations)
> > PROBLEM
> > =======
> > I need to prevent duplicate entries being created. e.g. lets say that
> > in the 'Group2Operation' table a record links the 'editor' group to
> > the 'publish' operation. Should I prevent an administrator creating a
> > duplicate of that record? (Otherwise deleting that permission will
> > have to be done twice or more for it to be effective)
> > SOLUTION?
> > =========
> > So far I've done this with a trigger:
> > CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2Operation
> > FOR INSERT, UPDATE
> > AS UPDATE Group2Operation
> > SET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTag
> > The 'NoDuplicate' unique index column in the Group2Operation table
> > stores a concatenation of the unique group and operation identifiers.
> > So when an attempt is made to create a record, the trigger is fired.
> > If there is a duplicate, this will mean a duplicate entry in the
> > 'NoDuplicate' column. As a result, the INSERT or UPDATE will fail and
> > the duplication will be prevented.
> > WHAT DO YOU THINK?
> > ==================
> > What do you think? Am I going about this in the right way? Is a
> > trigger a good way to do this or should I rely on application logic to
> > prevent duplicates?
> > Any help appreciated by this db novice.
> > John Grist

How to force immediate recompile of triggers and detect errors?

I need a way to programmatically (via JDBC) find out which triggers for a table may not compile properly, so that I can disable the bad triggers.

I can do this fine in Oracle but cannot figure out if there's a way to do this in SqlServer. (In Oracle I'd just "alter trigger... compile" and select from user_errors.)

I know how to find the triggers that exist on a table, and I know how to enable/disable individual triggers. I know about sp_recompile, but all that does is flag the trigger for recompile at the next execution.

I need to verify whether the trigger is valid without having to actually invoke it. For example, if there's a bad Update trigger, I don't want to actually execute an update on the table.

One example of what I'm dealing with is this... We have Table A and Table B. There is an update trigger on Table B that references column A.col1. Then we alter Table A to drop col1. Later we have to update Table B. At this point the update will fail because of the bad trigger. I want to find and disable the trigger before executing the update on Table B. If there are other triggers on Table B that are valid, I want to leave them alone.Shouldn't this be part of your QA process, not part of your application?

-PatP|||No, it's a database configuration application, and there may be triggers that we don't own and didn't create that we have to disable if they're going to cause a problem in our db config process. If we disable any triggers, we'd report the situation and the customer would be expected to fix the triggers before going live again.sql

how to force a table scan in query

I have a query thats using a particular index and I would like to force the
query to do a table scan instead. How can i do so ?
Heres a sample query
select * from tableA where col1= 5Geeze why would you want to?!
I think your only way would be to drop the index. I don't think you can
force the optimiser not to use an index...
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I have a query thats using a particular index and I would like to force
the
> query to do a table scan instead. How can i do so ?
> Heres a sample query
> select * from tableA where col1= 5
>|||Remove the index.
>--Original Message--
>I have a query thats using a particular index and I would
like to force the
>query to do a table scan instead. How can i do so ?
>Heres a sample query
>select * from tableA where col1= 5
>
>.
>|||Table hint WITH INDEX(0)
BOL: If a clustered index exists, INDEX(0) forces a clustered index scan
and INDEX(1) forces a clustered index scan or seek. If no clustered index
exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an
error.
Russell Fields
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I have a query thats using a particular index and I would like to force
the
> query to do a table scan instead. How can i do so ?
> Heres a sample query
> select * from tableA where col1= 5
>|||There are definitely reasons why you wouldn't want to use an index, but most
of them can be solved by making sure your statistics are updated. If the
optimizer is incorrectly choosing to use a nonclustered index, you can far
far more reads than a simple table scan would take. You might also just want
to run the query without the index for testing and comparison purposes, to
find out how much the index is really saving you, to see if it's worth the
cost of its maintenance.
As Russell pointed out, you can use WITH INDEX(0) to force no index to be
used on a particular table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"London Developer" <dev@.nowhere.com> wrote in message
news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> Geeze why would you want to?!
> I think your only way would be to drop the index. I don't think you can
> force the optimiser not to use an index...
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I have a query thats using a particular index and I would like to force
> the
> > query to do a table scan instead. How can i do so ?
> >
> > Heres a sample query
> >
> > select * from tableA where col1= 5
> >
> >
>|||btw , Kalen, in your latest article in SQL Mag about the optimiser, listing
2 did take an index scan when you mentioned that it would take a table scan
bcos of less reads. My optimiser is not smart enough I guess as yours :-)
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> There are definitely reasons why you wouldn't want to use an index, but
most
> of them can be solved by making sure your statistics are updated. If the
> optimizer is incorrectly choosing to use a nonclustered index, you can far
> far more reads than a simple table scan would take. You might also just
want
> to run the query without the index for testing and comparison purposes, to
> find out how much the index is really saving you, to see if it's worth the
> cost of its maintenance.
> As Russell pointed out, you can use WITH INDEX(0) to force no index to be
> used on a particular table.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "London Developer" <dev@.nowhere.com> wrote in message
> news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > Geeze why would you want to?!
> > I think your only way would be to drop the index. I don't think you can
> > force the optimiser not to use an index...
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I have a query thats using a particular index and I would like to
force
> > the
> > > query to do a table scan instead. How can i do so ?
> > >
> > > Heres a sample query
> > >
> > > select * from tableA where col1= 5
> > >
> > >
> >
> >
>|||Hi Hassan
Can you be more specific? I usually write my articles about 3 months in
advance (I just submitted February's article yesterday), so which is the
latest? October or November?
I'll take a look at it.
Thanks!
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> btw , Kalen, in your latest article in SQL Mag about the optimiser,
listing
> 2 did take an index scan when you mentioned that it would take a table
scan
> bcos of less reads. My optimiser is not smart enough I guess as yours :-)
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > There are definitely reasons why you wouldn't want to use an index, but
> most
> > of them can be solved by making sure your statistics are updated. If the
> > optimizer is incorrectly choosing to use a nonclustered index, you can
far
> > far more reads than a simple table scan would take. You might also just
> want
> > to run the query without the index for testing and comparison purposes,
to
> > find out how much the index is really saving you, to see if it's worth
the
> > cost of its maintenance.
> >
> > As Russell pointed out, you can use WITH INDEX(0) to force no index to
be
> > used on a particular table.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "London Developer" <dev@.nowhere.com> wrote in message
> > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > Geeze why would you want to?!
> > > I think your only way would be to drop the index. I don't think you
can
> > > force the optimiser not to use an index...
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I have a query thats using a particular index and I would like to
> force
> > > the
> > > > query to do a table scan instead. How can i do so ?
> > > >
> > > > Heres a sample query
> > > >
> > > > select * from tableA where col1= 5
> > > >
> > > >
> > >
> > >
> >
> >
>|||I think it was November..the latest issue on the newstand... that had Yukon
on the cover plus the listing that had examples of orderdetails table
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39906&
These lines in particular
"If you run the command SET STATISTICS IO ON, then execute Listing 2's
queries, you'll see that the queries each take 10 logical reads-one for each
page in the table. The first query returns 58 rows. If the optimizer had
decided to use the nonclustered index on Quantity, SQL Server would have had
to perform 58 bookmark lookup operations, a much higher cost than the 10
logical reads of the table scan. The second query returns 33 rows, so it,
too, would have cost more than 10 logical reads if the optimizer had decided
to access the nonclustered index and perform bookmark lookups."
But when executed my queries had around 60 logical reads since it was using
the index with a bookmark lookup
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O19nzqynDHA.2536@.tk2msftngp13.phx.gbl...
> Hi Hassan
> Can you be more specific? I usually write my articles about 3 months in
> advance (I just submitted February's article yesterday), so which is the
> latest? October or November?
> I'll take a look at it.
> Thanks!
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > btw , Kalen, in your latest article in SQL Mag about the optimiser,
> listing
> > 2 did take an index scan when you mentioned that it would take a table
> scan
> > bcos of less reads. My optimiser is not smart enough I guess as yours
:-)
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > > There are definitely reasons why you wouldn't want to use an index,
but
> > most
> > > of them can be solved by making sure your statistics are updated. If
the
> > > optimizer is incorrectly choosing to use a nonclustered index, you can
> far
> > > far more reads than a simple table scan would take. You might also
just
> > want
> > > to run the query without the index for testing and comparison
purposes,
> to
> > > find out how much the index is really saving you, to see if it's worth
> the
> > > cost of its maintenance.
> > >
> > > As Russell pointed out, you can use WITH INDEX(0) to force no index to
> be
> > > used on a particular table.
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "London Developer" <dev@.nowhere.com> wrote in message
> > > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > > Geeze why would you want to?!
> > > > I think your only way would be to drop the index. I don't think you
> can
> > > > force the optimiser not to use an index...
> > > >
> > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > > I have a query thats using a particular index and I would like to
> > force
> > > > the
> > > > > query to do a table scan instead. How can i do so ?
> > > > >
> > > > > Heres a sample query
> > > > >
> > > > > select * from tableA where col1= 5
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, I just got that in the mail today!
I'll take a look and see if I can figure out why you might have gotten
different behavior than I did.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#XajESznDHA.3700@.TK2MSFTNGP11.phx.gbl...
> I think it was November..the latest issue on the newstand... that had
Yukon
> on the cover plus the listing that had examples of orderdetails table
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39906&
> These lines in particular
> "If you run the command SET STATISTICS IO ON, then execute Listing 2's
> queries, you'll see that the queries each take 10 logical reads-one for
each
> page in the table. The first query returns 58 rows. If the optimizer had
> decided to use the nonclustered index on Quantity, SQL Server would have
had
> to perform 58 bookmark lookup operations, a much higher cost than the 10
> logical reads of the table scan. The second query returns 33 rows, so it,
> too, would have cost more than 10 logical reads if the optimizer had
decided
> to access the nonclustered index and perform bookmark lookups."
> But when executed my queries had around 60 logical reads since it was
using
> the index with a bookmark lookup
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:O19nzqynDHA.2536@.tk2msftngp13.phx.gbl...
> > Hi Hassan
> >
> > Can you be more specific? I usually write my articles about 3 months in
> > advance (I just submitted February's article yesterday), so which is the
> > latest? October or November?
> > I'll take a look at it.
> >
> > Thanks!
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:e2MYfNynDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > > btw , Kalen, in your latest article in SQL Mag about the optimiser,
> > listing
> > > 2 did take an index scan when you mentioned that it would take a table
> > scan
> > > bcos of less reads. My optimiser is not smart enough I guess as yours
> :-)
> > >
> > >
> > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > news:usV2PtwnDHA.1004@.TK2MSFTNGP09.phx.gbl...
> > > > There are definitely reasons why you wouldn't want to use an index,
> but
> > > most
> > > > of them can be solved by making sure your statistics are updated. If
> the
> > > > optimizer is incorrectly choosing to use a nonclustered index, you
can
> > far
> > > > far more reads than a simple table scan would take. You might also
> just
> > > want
> > > > to run the query without the index for testing and comparison
> purposes,
> > to
> > > > find out how much the index is really saving you, to see if it's
worth
> > the
> > > > cost of its maintenance.
> > > >
> > > > As Russell pointed out, you can use WITH INDEX(0) to force no index
to
> > be
> > > > used on a particular table.
> > > >
> > > > --
> > > > HTH
> > > > --
> > > > Kalen Delaney
> > > > SQL Server MVP
> > > > www.SolidQualityLearning.com
> > > >
> > > >
> > > > "London Developer" <dev@.nowhere.com> wrote in message
> > > > news:#otRHcwnDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > > > > Geeze why would you want to?!
> > > > > I think your only way would be to drop the index. I don't think
you
> > can
> > > > > force the optimiser not to use an index...
> > > > >
> > > > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > > > news:OcGkZRwnDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > > > I have a query thats using a particular index and I would like
to
> > > force
> > > > > the
> > > > > > query to do a table scan instead. How can i do so ?
> > > > > >
> > > > > > Heres a sample query
> > > > > >
> > > > > > select * from tableA where col1= 5
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

How to force a table into memory.

Hello,
On Sybase ASE, there is a command that will force the table and all its
contents into RAM, so that it can be queried faster. Is there such a
feature on SQL Server 2000?
Thanks
There is no command that will force table data into buffer cache. However,
you can mark a table as pinned so that SQL Server will keep table data in
buffer cache once read into memory. See DBCC PINTABLE in the Books Online
for details.
Note that it is very rare that this option will improve performance.
Microsoft SQL Server's buffer management algorithm will automatically keep
frequently used data in memory and makes the best use of available memory
resources.
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks
|||You could run a query like this:
SELECT COUNT(*) FROM MyTable (index=0)
If your SQL Server has enough memory, then this will cause the entire
table to be loaded into SQL Server's data buffer. If there is no memory
pressure, it will stay in memory.
There is an option called DBCC PINTABLE, but I would definitely advise
against it. It is best to let SQL Server manage the available memory.
Gert-Jan
Frank Rizzo wrote:
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks
|||Just want to add that the act of pinning a table will not actually place the
contents into memory. It just keeps them there after you access them. You
would have to do a select to get the data into cache. But I will also
emphasize what Dan stated in that this usually is not the right thing to do.
And this option is going away in SQL2005.
Andrew J. Kelly SQL MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23JhFDXF8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> There is no command that will force table data into buffer cache.
> However, you can mark a table as pinned so that SQL Server will keep table
> data in buffer cache once read into memory. See DBCC PINTABLE in the
> Books Online for details.
> Note that it is very rare that this option will improve performance.
> Microsoft SQL Server's buffer management algorithm will automatically keep
> frequently used data in memory and makes the best use of available memory
> resources.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Rizzo" <none@.none.com> wrote in message
> news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
>

How to force a table into memory.

Hello,
On Sybase ASE, there is a command that will force the table and all its
contents into RAM, so that it can be queried faster. Is there such a
feature on SQL Server 2000?
ThanksThere is no command that will force table data into buffer cache. However,
you can mark a table as pinned so that SQL Server will keep table data in
buffer cache once read into memory. See DBCC PINTABLE in the Books Online
for details.
Note that it is very rare that this option will improve performance.
Microsoft SQL Server's buffer management algorithm will automatically keep
frequently used data in memory and makes the best use of available memory
resources.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||You could run a query like this:
SELECT COUNT(*) FROM MyTable (index=0)
If your SQL Server has enough memory, then this will cause the entire
table to be loaded into SQL Server's data buffer. If there is no memory
pressure, it will stay in memory.
There is an option called DBCC PINTABLE, but I would definitely advise
against it. It is best to let SQL Server manage the available memory.
Gert-Jan
Frank Rizzo wrote:
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||Just want to add that the act of pinning a table will not actually place the
contents into memory. It just keeps them there after you access them. You
would have to do a select to get the data into cache. But I will also
emphasize what Dan stated in that this usually is not the right thing to do.
And this option is going away in SQL2005.
Andrew J. Kelly SQL MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23JhFDXF8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> There is no command that will force table data into buffer cache.
> However, you can mark a table as pinned so that SQL Server will keep table
> data in buffer cache once read into memory. See DBCC PINTABLE in the
> Books Online for details.
> Note that it is very rare that this option will improve performance.
> Microsoft SQL Server's buffer management algorithm will automatically keep
> frequently used data in memory and makes the best use of available memory
> resources.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Rizzo" <none@.none.com> wrote in message
> news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> On Sybase ASE, there is a command that will force the table and all its
>> contents into RAM, so that it can be queried faster. Is there such a
>> feature on SQL Server 2000?
>> Thanks
>

How to force a table into memory.

Hello,
On Sybase ASE, there is a command that will force the table and all its
contents into RAM, so that it can be queried faster. Is there such a
feature on SQL Server 2000?
ThanksThere is no command that will force table data into buffer cache. However,
you can mark a table as pinned so that SQL Server will keep table data in
buffer cache once read into memory. See DBCC PINTABLE in the Books Online
for details.
Note that it is very rare that this option will improve performance.
Microsoft SQL Server's buffer management algorithm will automatically keep
frequently used data in memory and makes the best use of available memory
resources.
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||You could run a query like this:
SELECT COUNT(*) FROM MyTable (index=0)
If your SQL Server has enough memory, then this will cause the entire
table to be loaded into SQL Server's data buffer. If there is no memory
pressure, it will stay in memory.
There is an option called DBCC PINTABLE, but I would definitely advise
against it. It is best to let SQL Server manage the available memory.
Gert-Jan
Frank Rizzo wrote:
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||Just want to add that the act of pinning a table will not actually place the
contents into memory. It just keeps them there after you access them. You
would have to do a select to get the data into cache. But I will also
emphasize what Dan stated in that this usually is not the right thing to do.
And this option is going away in SQL2005.
Andrew J. Kelly SQL MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23JhFDXF8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> There is no command that will force table data into buffer cache.
> However, you can mark a table as pinned so that SQL Server will keep table
> data in buffer cache once read into memory. See DBCC PINTABLE in the
> Books Online for details.
> Note that it is very rare that this option will improve performance.
> Microsoft SQL Server's buffer management algorithm will automatically keep
> frequently used data in memory and makes the best use of available memory
> resources.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Rizzo" <none@.none.com> wrote in message
> news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
>sql

How to force a page break on a multi-column report ?

When I use the PageBreakAtEnd on the table or on a group in the table, all it does is create a new column of the column report.

I'd want it to start a new page, how can I do this ? Should I work around this issue using code ?

Background: What I need to achieve is a report with 2 columns where the list of products in category 1 are listed in the left column and then snake to the 2nd column on the same page, then to column 1 on page 2, column 2 on page 2, etc...

When it comes to category 2, it should start a fresh new page regardless of whether the previous product was rendered in column 1 or column 2.

I get the snaking to work using the "Columns" property of the report Body. However page breaks do not start a new page, they just start a new column.

What if you created an outer group which evaluated the same expression, and put the page break on that group instead? Would that work for you?|||

Hi Alan,

If u want page break after specifi records on report u can try by modifying the(.rdl) file which is XML and set the tag property as specific inches might work for u.For that u have to set the tag value 2 in or any 'x' in where tag name is 'InteractiveHeight'.

(<InteractiveHeight>11in</InteractiveHeight>)

I hope this'll help u for forcing the page break.

Regards,

Vikas

sql

How to flow a table into a second column

s it stands, my table skips the second column on the page and flows
into the first column on the second page, and then the first column on
the third page, ad nauseum. I want the table to fill up both columns on
the page before breaking to the next page. All the docs I've found
state that flowing into multiple columns like this happens
automagically. If so, what's the incantation I'm missing?
This is being attempted on VS2005 Beta 2 on XP.
TIA,
NoelI am having the same problems. I discovered that if you right click on the
report preview and select "print preview" from the pop-up menu, the columns
are magically displayed.
Now how does one publish this report so that the multi-columns display
properly on the web?!!
"Noel Weichbrodt" wrote:
> s it stands, my table skips the second column on the page and flows
> into the first column on the second page, and then the first column on
> the third page, ad nauseum. I want the table to fill up both columns on
> the page before breaking to the next page. All the docs I've found
> state that flowing into multiple columns like this happens
> automagically. If so, what's the incantation I'm missing?
> This is being attempted on VS2005 Beta 2 on XP.
> TIA,
> Noel

How to flag a row and allow max one row flagged in the table.

Hi,

I am new to SQL.

which is the best way to flag a row?

Create a bit column IsFlagged?

Additionally, I want to create a table-level constraint that allows max 1 row flagged true in the whole table. I can't nest a select expression in the CREATE TABLE statement CONSTRAINT clause that counts flagged rows.

So how do I do this?

Appreciate the help.

you can do something like this -

create table FlaggableDomain(ID int unique not null, Name sysname primary key not null)

populated something like this

ID DomainName
1 'person'
2 'place'
3 'thing'

track your flags with this -

create table FlaggedDomain (ItemID int not null, DomainID int not null, primary key (ItemID, DomainID))

to flag a row Person row -

insert into FlaggedDomain (ItemID, DomainId)
select person.id, domain.ID from
person
where name = 'SOME NAME' and
FlaggableDomain.DomainName = 'person'

to clear -

delete from FlaggedDomain where DomainId in
( select idFlaggableDomain
from FlaggableDomain
where DomainName = 'person')

to get flagged person -

select person.*
from person p inner join FlaggedDomain f
on p.id = f.ItemID

|||

Thanks for taking the time Blair,

Let me summarize what I made out of your reply:

1.Create a table of flags(FlaggableDomain)
2. Make a linking table(FlaggedDomain) linking flags with any other table(person), whose rows I want to flag.
3. By having a composite primary key for the linking table(FlaggableDomain) you ensure the person table can only have one row that has the flag 'person'.

This definitely is a solution, and probably the most professional one. However, I now face having to do 2 extra tables whenever I want to implement flagging logic, or have a central depository of many dissimilar flags and always having to join to this depository to be able to use the flags. In the former a table bloat, in the latter clunky code. Not quite like an enumeration in vb/c# which groups constants,hopefully, logically, depending on the author :).

There is one nice feature about your way, that is in a table that can have mixed rows of things/persons/places, more than one type of flag can be stored in the same column. Thus I can make sure max 1 'thing', max 1 'place', and max 1 'person' rows exist in that mixed table. I don't have that need now but it is a neat way.

A not so nice feature: not just any table's rows can be flagged this way. Only tables which have a simple(1 column), int primary key, which is the most common one but there are exceptions and they wouldn't be flaggable this way.

What about table level constraint?
I know I couldn't nest a select statement (and count) inside the constraint expression. Are there any options along those lines?

Forgive me if I am discovering the wheel out loud.

|||

However, I now face having to do 2 extra tables whenever I want to implement flagging logic, or have a central depository of many dissimilar flags and always having to join to this depository to be able to use the flags. In the former a table bloat, in the latter clunky code.

the code is not clunky at all. . . make views from simple select statements.

Always bear in mind that a primary key is a 'tuple' that defines a unique entity within a domain. Whereever possible, it should be a piece of data that you can look at without knowledge of the db and know what it is (natural key) . And as you noted, often times the primary key is compound and creating a foreign key reference would be unwieldly. When this is the case, the approach is to use what is called a unique, non-null "surrogate key" and propagate that value in foreign key references. You might find it useful to use uniqueidentifier fields. UniqueID's are cheap to create and generatate.

Tables are cheap to create too. Joins are cheap too.

Databases are geared to work with them - as long as the indexes are properly defined.

I am not sure if a table constraint is possible. Even if it were, it would take a total table scan to assure that the constraint we enforced.

google "Surrogate Key"

|||

I looked into surrogate keys and will definitely rethink doing composite keys in the future.

Learned a lot from you!

Thanks a bunch.

|||

cheers!

|||

Blair,

Can I milk you for a bit more advice?

Is this type of flagging appropriate to implement data versioning?

say a sports league. A player can be active in just one team, but I want to keep prior team membership in a foreign key table. Only one of those team memberships would be flagged 'current' as we discussed. The rest are just stale versions, but I want to keep that history data.

When the player moves to another team, it would have to be a transaction removing the flag from the old team, and flagging the new one?

Is this the right way of doing versioning?

Carl

|||

I don't know if versioning is the right term. I would consider this a Transaction. Note: i am using this in the natural sense of the word, not database concurrency management.

Legend: Table(KeyFields, SurrogateKeyField, SupplementalField), with foreign keys marked by Field -> Table(KeyField) and I am using integer ID's for brevity. ID data type is irrelevant with the condition that it is easily generated.

First, Active Status on a team would be enforced by Roster(PlayerID, TeamID)

Next, I would have a table TransactionType(TransID, TransName, TransAbbr)

this would be populated by something like:

1, Draft, DR
2, Free-Agent, FA
3, Injured, IR
4, Disabled, DL
5, Trade Out, TO
6, Trade In, TI
7, Released, RE

I would create a PlayerHistory table to track the status changes of players -

PlayerHistory(HistoryID, PlayerID -> Player(PlayerID), TeamID -> Team(TeamID), TransID -> TransactionType(TransID), DateOfEntry , RelatedHistory -> PlayerHistory(HistoryID))

Note the self reference on player history, this would be used for trades - Each trade out has a related trade in. For all other entries in a players history, the related history would be null.

Say team1 traded player 1 to team 2 for player 2 on 1/1/2006 -

1, 1, 1, 5, 1/1/2006, 2
2, 1, 2, 6, 1/1/2006, 1
3, 2, 2, 5, 1/1/2006, 4
4, 2.,1, 6, 1/1/2006, 3

This could get more elaborate - TradeMaster (TradeID, AgreementDocument)

TradeHistory(TradeID, PlayerHistoryID -> PlayerHistory(HistoryID))

This could be used to track the Trade-Outs that were affected by the aggreement. From our example above:

TradeMaster might be:
1, "Team 1 will give Team 2 a first round draft pick next year"

And TradeHistory would simply be:
1, 1
1, 3

You could trigger off of TradeHistory to automatically insert PlayerHistory and update Roster

Now you might be thinking, as you said "Table Bloat." Tables and Disk-Space is cheap. What is expensive is processor time! You want to minimize the amount of clock cycles that need to be executed, both in the client application or the database server. Inserting or changing data that is indexed or constrained is expensive. Get it in the table in a way that minimizes changes to indexed fields. If you have to manipulate data that is indexed or constrained, you want to do it once, if possible.

Finally, it only takes the execution of 3 instructions to retrieve a set of rows based on index. Indexes on bit fields give no benefit.

Other things to google "Normal-Form" and "DKNF" - Note: DKNF is an ideal, strive for it, but it is not always feasible.

Database Theory is as much an art as it is a science. People get PhD's in Database Therory, but there is no "Right" model for a given Domain Set, but there are models that are more proper than others. I liken it to the game othello, "A minute to learn, a lifetime to master."

Oh yeah! Don't forget to eat and sleep! (Yeah, as if you will be able to sleep once you get started thinking about your data model)

Don't get me started on the term "Normal" and it roots in mathematics.

Good luck and have fun.

|||

Thanks Blair,

You answered even my next couple of questions.

Good advice re the sleep bit!

Cheers