Hi,
Will space used by an table be unallocated if the table is truncated or
will it only be marked as unused?
TIA,
Can you explain the difference between unallocated and unused?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns97976A2B37D0Fgurbaohotmailcom@.129.250.171. 66...
> Hi,
> Will space used by an table be unallocated if the table is truncated or
> will it only be marked as unused?
> TIA,
>
|||Hi,
well,
as I have understood space allocation in SQL Server it will allocate 1
extent when needing more space for data (not talking about mixed extents
here). If the new data is only occupying 1 page in the extent the whole
extent is counted as allocated and 7 pages as unused.
Am I far off in my understanding this issue? If so, please explain...
TIA,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
> Can you explain the difference between unallocated and unused?
>
|||Your understanding about pages and extent allocation seems to be correct. When you do TRUNCATE
TABLE, all the extents are deallocated for the table and its indexes. If you do it in a transaction,
the deallocated pages are not available for re-use until COMMIT. But essentially, you end up with
the same situation as if you just had created the table (and indexes) and before you insert the
first row into the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9797852E1C296gurbaohotmailcom@.129.250.171. 68...
> Hi,
> well,
> as I have understood space allocation in SQL Server it will allocate 1
> extent when needing more space for data (not talking about mixed extents
> here). If the new data is only occupying 1 page in the extent the whole
> extent is counted as allocated and 7 pages as unused.
> Am I far off in my understanding this issue? If so, please explain...
> TIA,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
>
Showing posts with label truncated. Show all posts
Showing posts with label truncated. Show all posts
Friday, March 9, 2012
Reuse of space after truncating
Hi,
Will space used by an table be unallocated if the table is truncated or
will it only be marked as unused?
TIA,Can you explain the difference between unallocated and unused?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns97976A2B37D0Fgurbaohotmailcom@.12
9.250.171.66...
> Hi,
> Will space used by an table be unallocated if the table is truncated or
> will it only be marked as unused?
> TIA,
>|||Hi,
well,
as I have understood space allocation in SQL Server it will allocate 1
extent when needing more space for data (not talking about mixed extents
here). If the new data is only occupying 1 page in the extent the whole
extent is counted as allocated and 7 pages as unused.
Am I far off in my understanding this issue? If so, please explain...
TIA,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
> Can you explain the difference between unallocated and unused?
>|||Your understanding about pages and extent allocation seems to be correct. Wh
en you do TRUNCATE
TABLE, all the extents are deallocated for the table and its indexes. If you
do it in a transaction,
the deallocated pages are not available for re-use until COMMIT. But essenti
ally, you end up with
the same situation as if you just had created the table (and indexes) and be
fore you insert the
first row into the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9797852E1C296gurbaohotmailcom@.12
9.250.171.68...
> Hi,
> well,
> as I have understood space allocation in SQL Server it will allocate 1
> extent when needing more space for data (not talking about mixed extents
> here). If the new data is only occupying 1 page in the extent the whole
> extent is counted as allocated and 7 pages as unused.
> Am I far off in my understanding this issue? If so, please explain...
> TIA,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
>
>
Will space used by an table be unallocated if the table is truncated or
will it only be marked as unused?
TIA,Can you explain the difference between unallocated and unused?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns97976A2B37D0Fgurbaohotmailcom@.12
9.250.171.66...
> Hi,
> Will space used by an table be unallocated if the table is truncated or
> will it only be marked as unused?
> TIA,
>|||Hi,
well,
as I have understood space allocation in SQL Server it will allocate 1
extent when needing more space for data (not talking about mixed extents
here). If the new data is only occupying 1 page in the extent the whole
extent is counted as allocated and 7 pages as unused.
Am I far off in my understanding this issue? If so, please explain...
TIA,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
> Can you explain the difference between unallocated and unused?
>|||Your understanding about pages and extent allocation seems to be correct. Wh
en you do TRUNCATE
TABLE, all the extents are deallocated for the table and its indexes. If you
do it in a transaction,
the deallocated pages are not available for re-use until COMMIT. But essenti
ally, you end up with
the same situation as if you just had created the table (and indexes) and be
fore you insert the
first row into the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9797852E1C296gurbaohotmailcom@.12
9.250.171.68...
> Hi,
> well,
> as I have understood space allocation in SQL Server it will allocate 1
> extent when needing more space for data (not talking about mixed extents
> here). If the new data is only occupying 1 page in the extent the whole
> extent is counted as allocated and 7 pages as unused.
> Am I far off in my understanding this issue? If so, please explain...
> TIA,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
>
>
Reuse of space after truncating
Hi,
Will space used by an table be unallocated if the table is truncated or
will it only be marked as unused?
TIA,Can you explain the difference between unallocated and unused?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns97976A2B37D0Fgurbaohotmailcom@.129.250.171.66...
> Hi,
> Will space used by an table be unallocated if the table is truncated or
> will it only be marked as unused?
> TIA,
>|||Hi,
well,
as I have understood space allocation in SQL Server it will allocate 1
extent when needing more space for data (not talking about mixed extents
here). If the new data is only occupying 1 page in the extent the whole
extent is counted as allocated and 7 pages as unused.
Am I far off in my understanding this issue? If so, please explain...
TIA,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
> Can you explain the difference between unallocated and unused?
>|||Your understanding about pages and extent allocation seems to be correct. When you do TRUNCATE
TABLE, all the extents are deallocated for the table and its indexes. If you do it in a transaction,
the deallocated pages are not available for re-use until COMMIT. But essentially, you end up with
the same situation as if you just had created the table (and indexes) and before you insert the
first row into the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9797852E1C296gurbaohotmailcom@.129.250.171.68...
> Hi,
> well,
> as I have understood space allocation in SQL Server it will allocate 1
> extent when needing more space for data (not talking about mixed extents
> here). If the new data is only occupying 1 page in the extent the whole
> extent is counted as allocated and 7 pages as unused.
> Am I far off in my understanding this issue? If so, please explain...
> TIA,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
>> Can you explain the difference between unallocated and unused?
>
Will space used by an table be unallocated if the table is truncated or
will it only be marked as unused?
TIA,Can you explain the difference between unallocated and unused?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns97976A2B37D0Fgurbaohotmailcom@.129.250.171.66...
> Hi,
> Will space used by an table be unallocated if the table is truncated or
> will it only be marked as unused?
> TIA,
>|||Hi,
well,
as I have understood space allocation in SQL Server it will allocate 1
extent when needing more space for data (not talking about mixed extents
here). If the new data is only occupying 1 page in the extent the whole
extent is counted as allocated and 7 pages as unused.
Am I far off in my understanding this issue? If so, please explain...
TIA,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
> Can you explain the difference between unallocated and unused?
>|||Your understanding about pages and extent allocation seems to be correct. When you do TRUNCATE
TABLE, all the extents are deallocated for the table and its indexes. If you do it in a transaction,
the deallocated pages are not available for re-use until COMMIT. But essentially, you end up with
the same situation as if you just had created the table (and indexes) and before you insert the
first row into the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns9797852E1C296gurbaohotmailcom@.129.250.171.68...
> Hi,
> well,
> as I have understood space allocation in SQL Server it will allocate 1
> extent when needing more space for data (not talking about mixed extents
> here). If the new data is only occupying 1 page in the extent the whole
> extent is counted as allocated and 7 pages as unused.
> Am I far off in my understanding this issue? If so, please explain...
> TIA,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:OuSKkGKVGHA.196@.TK2MSFTNGP10.phx.gbl:
>> Can you explain the difference between unallocated and unused?
>
Tuesday, February 21, 2012
Returning fields that have changed since last import
I have a few tables that update everyday with fresh data.
The contents of the archive table are truncated before the import starts, the current data is then copied to the archive table and then truncated ready for the fresh data.
so I have two tables, one with current data, one with yesterdays data. I need to write queries to show if any data has changed between the two.
CREATE TABLE [TblBond] (
[issuer] [varchar] (50) NOT NULL ,
[maturity_date] [datetime] NOT NULL ,
[coupon] [numeric](30, 10) NOT NULL ,
[currency] [char] (3) NOT NULL ,
[bond_type_code] [varchar] (12) NOT NULL ,
[sec_id] [int] NOT NULL ,
[otr] [int] NOT NULL ,
[identification_str] [varchar] (60) NULL ,
[description] [varchar] (30) NULL ,
[settlement_date] [datetime] NULL ,
[buy_sell] [varchar] (4) NOT NULL ,
[trade_amount] [numeric](38, 10) NOT NULL ,
[accrued_interest] [numeric](38, 10) NOT NULL ,
[remark] [varchar] (255) NOT NULL ,
[counterparty] [varchar] (20) NOT NULL ,
[booking_entity] [varchar] (20) NOT NULL ,
[keyword] [varchar] (255) NOT NULL ,
[trans_id] [int] NOT NULL ,
[trade_id] [int] NOT NULL ,
[trade_status] [varchar] (12) NULL ,
CONSTRAINT [PK_TblBondData] PRIMARY KEY CLUSTERED
(
[trade_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Trade_id is the PK, the archive table is the same structure but called TblBond_archive
I have got a query that works, but is very lengthy and will need amending if the columns change at all (and as im dealing with traders, im sure they will add/remove things!)
query is like dis :
SELECT
TblBond.trade_id,
'Issuer' AS Field_Changed,
cast(TblBond_Archive.Issuer as varchar) AS Old_Value,
cast(TblBond.Issuer as varchar) AS New_Value
FROM TblBond
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
WHERE TblBond.Issuer<>[tblbond_archive].[Issuer]
UNION
...... and goes onto next field. for another 19 columns!!
There must be a more eloquent way of coding this? anyone any ideas? im using SQL server 2000, so i do have some schema tables i can call on if needed.
Any ideas would be great as I have about 10 tables using similiar principals and tis a pain to code it all!!
RegardsThe by far easiest and fastest way for doing this would be the use of a timestamp column in your live table and an appropriate datetime in the archive table. It gets updated automatically on every update. You can get the updated records with a simple:
SELECT blablabla
FROM TblBond
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
WHERE TblBond.updated_timestamp > TblBond_Archive.updated_datetime|||Thanks Apel, in an ideal world this is what i would have done in the first place, but alas, this data is actually sourced from a rather orrible sybase system, which I can't make any DDL changes to, as all the sybase dev team where let go a few months back!!
so no go on the timestamp column! all I can do is import the tables from sybase into my sql server for the purposes of reporting.
Also that method wouldn't enable to keep an audit trail of what changes have happened to each trade. the way i described allows me to run that big mother query everytime data is imported and copy results to an audit table. Which stores the PK, fieldname, old value , new value , and the datetime the change was detected. I also tacked on 2 other union select queries to pick up new or deleted trades.
I ended up writing a wee bit of vba code (which i ran in MS access) to help me create the SQL code for the big query! worked nicely. but still think there must be a more eloquent way to code this.
The contents of the archive table are truncated before the import starts, the current data is then copied to the archive table and then truncated ready for the fresh data.
so I have two tables, one with current data, one with yesterdays data. I need to write queries to show if any data has changed between the two.
CREATE TABLE [TblBond] (
[issuer] [varchar] (50) NOT NULL ,
[maturity_date] [datetime] NOT NULL ,
[coupon] [numeric](30, 10) NOT NULL ,
[currency] [char] (3) NOT NULL ,
[bond_type_code] [varchar] (12) NOT NULL ,
[sec_id] [int] NOT NULL ,
[otr] [int] NOT NULL ,
[identification_str] [varchar] (60) NULL ,
[description] [varchar] (30) NULL ,
[settlement_date] [datetime] NULL ,
[buy_sell] [varchar] (4) NOT NULL ,
[trade_amount] [numeric](38, 10) NOT NULL ,
[accrued_interest] [numeric](38, 10) NOT NULL ,
[remark] [varchar] (255) NOT NULL ,
[counterparty] [varchar] (20) NOT NULL ,
[booking_entity] [varchar] (20) NOT NULL ,
[keyword] [varchar] (255) NOT NULL ,
[trans_id] [int] NOT NULL ,
[trade_id] [int] NOT NULL ,
[trade_status] [varchar] (12) NULL ,
CONSTRAINT [PK_TblBondData] PRIMARY KEY CLUSTERED
(
[trade_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Trade_id is the PK, the archive table is the same structure but called TblBond_archive
I have got a query that works, but is very lengthy and will need amending if the columns change at all (and as im dealing with traders, im sure they will add/remove things!)
query is like dis :
SELECT
TblBond.trade_id,
'Issuer' AS Field_Changed,
cast(TblBond_Archive.Issuer as varchar) AS Old_Value,
cast(TblBond.Issuer as varchar) AS New_Value
FROM TblBond
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
WHERE TblBond.Issuer<>[tblbond_archive].[Issuer]
UNION
...... and goes onto next field. for another 19 columns!!
There must be a more eloquent way of coding this? anyone any ideas? im using SQL server 2000, so i do have some schema tables i can call on if needed.
Any ideas would be great as I have about 10 tables using similiar principals and tis a pain to code it all!!
RegardsThe by far easiest and fastest way for doing this would be the use of a timestamp column in your live table and an appropriate datetime in the archive table. It gets updated automatically on every update. You can get the updated records with a simple:
SELECT blablabla
FROM TblBond
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
WHERE TblBond.updated_timestamp > TblBond_Archive.updated_datetime|||Thanks Apel, in an ideal world this is what i would have done in the first place, but alas, this data is actually sourced from a rather orrible sybase system, which I can't make any DDL changes to, as all the sybase dev team where let go a few months back!!
so no go on the timestamp column! all I can do is import the tables from sybase into my sql server for the purposes of reporting.
Also that method wouldn't enable to keep an audit trail of what changes have happened to each trade. the way i described allows me to run that big mother query everytime data is imported and copy results to an audit table. Which stores the PK, fieldname, old value , new value , and the datetime the change was detected. I also tacked on 2 other union select queries to pick up new or deleted trades.
I ended up writing a wee bit of vba code (which i ran in MS access) to help me create the SQL code for the big query! worked nicely. but still think there must be a more eloquent way to code this.
Subscribe to:
Posts (Atom)