OpenXML Identity Insert: second

Showing posts with label second. Show all posts

Friday, March 23, 2012

Optimize SQL

Hello All!
Is there a way to write this SQL without a UNION, because where i have to
set one field to null to match the number of field of the second sql :(
SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
AgenciaID, 'Caixa' AS Tipo
FROM Caixa
UNION
SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
Agencia.ID AS AgenciaID, 'Banco' AS Tipo
FROM Agencia JOIN
BANCO ON Banco.ID = Agencia.ID
Thank you all!!!
Bruno NThere probably are other solutions but if you want the same columns in
there then you will presumably have to populate the AgenciaID column
with NULLs anyway. I'm not sure what it is you want to do differently.
You should consider using UNION ALL instead of UNION unless it's
required to eliminate duplicates. UNION ALL will typically perform
better.
If you need more help, please post some more info as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Nope, unless you want to return 2 separate result sets. Then you can do
without the Tipo column as well.
There's nothing wrong really with setting one column to NULL if it doesn't
exist in that part of the unioned set.
Jacco Schalkwijk
SQL Server MVP
"Bruno N" <nylren@.hotmail.com> wrote in message
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>|||Thanks guys!!
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>

Monday, March 19, 2012

optimising a table with lots of boolean fields

I have an application that reads a monitoring devices that produces 200 digital outputs every second and I would like to store them in a table. This table would get quite big fairly quickly as ultimately I would like to monitor over a hundred of these devices.

I would like to construct queries against each of the individual digital channels or combinations of them.

M first thought is to set up a table with 200 separate columns (plus others for date stamp, device ID etc) however, I am concerned that a table with 200 boolean (1-bit) fields would be an enormous waste of space if each field takes maybe one to four bytes on the hard disk to store a single bit. However, this would have the advantage of make the SQL queries more natural.

The other alternative is to create a single 200 bit field and use lots of ANDing and ORing to isolate bits to do my queries. This would make my SQL code less readable and may also cause nore hassle in the future if the inputs changed, but it would make the file size smaller.

In essence I am asking (hoping) the following : If I create a table with 200 boolean fields, does SQL server express automatically optimise the storage to make it more compact? This means that the server can mess around at the bit level and leave my higher level SQL code looking cleaner and more logical.

hi,

SQL Server can pad up to 8 bit columns together to save space, but this is another concern in your problem..

I'd actually go for a more normalized model.. you can break the 200 bit columns in a separated entity referincing the same transaction... this not only is more "elegant" and correct, but solves problem where you have to add/modify a device..

something like

SET NOCOUNT ON;

USE tempdb;

CREATE TABLE dbo.Devices (

Id int NOT NULL PRIMARY KEY,

Description varchar(10) NOT NULL

);

CREATE TABLE dbo.DeviceTran (

Id int NOT NULL IDENTITY PRIMARY KEY , -- for sake of simplicity

otherData varchar(10) NULL,

TimeRecorded datetime DEFAULT GETDATE()

);

CREATE TABLE dbo.DeviceTranOutput (

Id int NOT NULL IDENTITY PRIMARY KEY , -- for sake of simplicity

IdTran int NOT NULL

CONSTRAINT fk_DeviceTran_DeviceTranOutput

FOREIGN KEY

REFERENCES dbo.DeviceTran (Id),

IdDevice int NOT NULL

CONSTRAINT fk_Devices_DeviceTranOutput

FOREIGN KEY

REFERENCES dbo.Devices (Id),

TValue bit NOT NULL DEFAULT 0

);

PRINT 'available devices';

INSERT INTO dbo.Devices VALUES ( 1 , 'PDA' );

INSERT INTO dbo.Devices VALUES ( 2 , 'PBAX' );

INSERT INTO dbo.Devices VALUES ( 3 , 'PC' );

INSERT INTO dbo.Devices VALUES ( 4 , 'xxx' );

SELECT * FROM dbo.Devices;

PRINT '--';

DECLARE @.i int, @.y int, @.id int;

SET @.i = 1

WHILE @.i < 10 BEGIN

INSERT INTO dbo.DeviceTran VALUES ( 'Data ' + CONVERT(varchar, @.i), DEFAULT);

SELECT @.id = SCOPE_IDENTITY();

SET @.y = 1;

WHILE @.y < 5 BEGIN

INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.y , (@.i + @.y) % 2);

SET @.y = @.y +1;

END

SET @.i = @.i +1

END;

PRINT 'Transaction Report';

SELECT t.Id, t.otherData, t.TimeRecorded, d.Description, o.TValue

FROM dbo.DeviceTranOutput o

JOIN dbo.Devices d

ON d.Id = o.IdDevice

JOIN dbo.DeviceTran t

ON t.Id = o.IdTran;

DROP TABLE dbo.DeviceTranOutput, dbo.DeviceTran, dbo.Devices;

resulting in something likeTransaction Report
Id otherData TimeRecorded Description TValue
-- - -- --
1 Data 1 2006-07-17 18:23:01.717 PDA 0
1 Data 1 2006-07-17 18:23:01.717 PBAX 1
1 Data 1 2006-07-17 18:23:01.717 PC 0
1 Data 1 2006-07-17 18:23:01.717 xxx 1
2 Data 2 2006-07-17 18:23:01.717 PDA 1
2 Data 2 2006-07-17 18:23:01.717 PBAX 0
2 Data 2 2006-07-17 18:23:01.717 PC 1
2 Data 2 2006-07-17 18:23:01.717 xxx 0
3 Data 3 2006-07-17 18:23:01.717 PDA 0
3 Data 3 2006-07-17 18:23:01.717 PBAX 1
-- result abrdiged..

storage will be better used, you have some penality in both reporting and inserting, as JOIN operations are involved, but you get far better design you can expand/modify with no worries about modifications in the device pattern...

regards

|||Thanks for that.

I'll probably adopt your suggestion.

In order to save space, I am considering only storing the ocasions when the digital values change (rather than every time step).
Using your method, I don't need to store every channel every time so that is another reason in favour of it.

It does lead to another question though..

Taking your example: say a DeviceTran record is generate every timestep for the benefit of some analogue channel 'X' that is continuously changing.
Take a digital channel called 'Y' that is only recorded every time it changes.
Then, if I do an outer join to gather all the data at every time step, I might get something like this,,,

Id TimeRecorded X Y
-- -- --
1 2006-07-17 18:23:01.000 1.0 false
2 2006-07-17 18:23:02.000 1.1 null
3 2006-07-17 18:23:03.000 1.2 null
4 2006-07-17 18:23:04.000 1.1 true
5 2006-07-17 18:23:05.000 1.4 null
6 2006-07-17 18:23:06.000 1.5 null
7 2006-07-17 18:23:07.000 1.2 false
8 2006-07-17 18:23:08.000 0.9 null
9 2006-07-17 18:23:09.000 0.8 nullFor a particular value of DATETIME I would like to get the most recent
record for Y.
A straight outer join would just show null values for those not present.
Is there a clever way of joining but using the 'last' value based on
datetime or id.

Regards
|||

hi,

I'm not sure I fully understand your requirements..

I think you mean you just avoid to insert repeted values as following..

SET NOCOUNT ON;

USE tempdb;

CREATE TABLE dbo.Devices (

Id int NOT NULL PRIMARY KEY,

Description varchar(10) NOT NULL

);

CREATE TABLE dbo.DeviceTran (

Id int NOT NULL IDENTITY PRIMARY KEY ,

otherData varchar(10) NULL,

TimeRecorded datetime NOT NULL DEFAULT GETDATE()

);

CREATE TABLE dbo.DeviceTranOutput (

Id int NOT NULL IDENTITY PRIMARY KEY ,

IdTran int NOT NULL

CONSTRAINT fk_DeviceTran_DeviceTranOutput

FOREIGN KEY

REFERENCES dbo.DeviceTran (Id),

IdDevice int NOT NULL

CONSTRAINT fk_Devices_DeviceTranOutput

FOREIGN KEY

REFERENCES dbo.Devices (Id),

TValue bit NULL

);

DECLARE @.t datetime, @.id int, @.dev int;

SELECT @.dev = 1, @.t = GETDATE();

INSERT INTO dbo.Devices VALUES ( @.dev , 'PBAX' );

INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);

SELECT @.id = SCOPE_IDENTITY();

INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 0);

WAITFOR DELAY '00:00:01'

SELECT @.t = GETDATE();

INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);

SELECT @.id = SCOPE_IDENTITY();

--INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 0);

WAITFOR DELAY '00:00:01'

SELECT @.t = GETDATE();

INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);

SELECT @.id = SCOPE_IDENTITY();

--INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 0);

WAITFOR DELAY '00:00:01'

SELECT @.t = GETDATE();

INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);

SELECT @.id = SCOPE_IDENTITY();

INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 1);

WAITFOR DELAY '00:00:01'

SELECT @.t = GETDATE();

INSERT INTO dbo.DeviceTran VALUES ( 'Data Pbax' , @.t);

SELECT @.id = SCOPE_IDENTITY();

--INSERT INTO dbo.DeviceTranOutput VALUES ( @.id , @.dev , 1);

PRINT 'Transaction Report';

PRINT '';

PRINT 'IdDevice is NULL, you can''t directly reference';

PRINT 'the devices, you have to resort on ''specific'' queries';

SELECT t.Id, t.otherData, t.TimeRecorded, d.Description,

(SELECT TOP 1 o2.TValue

FROM dbo.DeviceTranOutput o2

WHERE o2.IdDevice = d.Id

AND o2.IdTran <= t.Id

ORDER BY o2.Id DESC) AS TValue

FROM dbo.DeviceTranOutput o

JOIN dbo.Devices d ON d.Id = o.IdDevice

RIGHT JOIN dbo.DeviceTran t ON t.Id = o.IdTran

ORDER BY t.TimeRecorded;

PRINT 'as passing a [@.DeviceId] as a parameter';

DECLARE @.DeviceId int;

DECLARE @.DeviceDescription varchar(10);

SELECT @.DeviceId = 1;

SELECT @.DeviceDescription = Description

FROM dbo.Devices

WHERE Id = @.DeviceId;

SELECT t.Id, t.otherData, t.TimeRecorded,

@.DeviceDescription AS [Description],

(SELECT TOP 1 o2.TValue

FROM dbo.DeviceTranOutput o2

WHERE o2.IdDevice = @.DeviceId

AND o2.IdTran <= t.Id

ORDER BY o2.Id DESC) AS TValue

FROM dbo.DeviceTranOutput o

JOIN dbo.Devices d ON d.Id = o.IdDevice

RIGHT JOIN dbo.DeviceTran t ON t.Id = o.IdTran;

DROP TABLE dbo.DeviceTranOutput, dbo.DeviceTran, dbo.Devices;

--<-

Transaction Report
IdDevice is NULL, you can't directly reference

the devices, you have to resort on 'specific' queries

Id otherData TimeRecorded Description TValue

-- - -- --

1 Data Pbax 2006-07-18 22:56:51.810 PBAX 0

2 Data Pbax 2006-07-18 22:56:52.827 NULL NULL

3 Data Pbax 2006-07-18 22:56:53.827 NULL NULL

4 Data Pbax 2006-07-18 22:56:54.827 PBAX 1

5 Data Pbax 2006-07-18 22:56:55.827 NULL NULL

as passing a [@.DeviceId] as a parameter

Id otherData TimeRecorded Description TValue

-- - -- --

1 Data Pbax 2006-07-18 22:56:51.810 PBAX 0

2 Data Pbax 2006-07-18 22:56:52.827 PBAX 0

3 Data Pbax 2006-07-18 22:56:53.827 PBAX 0

4 Data Pbax 2006-07-18 22:56:54.827 PBAX 1

5 Data Pbax 2006-07-18 22:56:55.827 PBAX 1

but my idea is you'll go into troubles both at insert time, as you have to check if the current value is the same as the last one, and later at query time, as you miss some references..
you can scan for an older value in dbo.DeviceTranOutput of a previous transaction, but you miss the IdDevice value... if you query for a specified device then it's allright, as you pass the IdDevice as a parameter, which becames a constant, but a general purpose query to list all transactions (orderd by TimeRecorded and IdDevice) becames heavy, for every row ...
at insert time this is a heavy load as well as instead of just inserting you have to check, and this can cost a lot in real time apps..
considering you're collecting data in quiet real time, I'd go for the quicker (is it english?) way to pump data in, without trigger to filter out repeated values...

more, transactionally, it breaks a rule of atomicity of a row, as it depends on the values of previous rows... it makes all the design trickier, and of corse coding as well.. my $0.02..

regards

|||Thanks for that.
You have understood my requirements exactly.

I was hoping to save disk space by not storing all the values for a timestep that haven't changed. But you are right about breaking the rule of atomicity, because the value of a particular column now depends on its 'last' non-null value. I hadn't thought of it like that.

Whatever the gains I make in disk space I have to seriously consider the penalty in SQL complexity. Trying to extract the 'current' value for just one or maybe a handful of channels can be done (as you have shown above) but a generic query to return values for all channels might be very complex/slow.

I'll go away and think some more on it.

Thanks once again.

Regards

Friday, March 9, 2012

Opinion about design needed (splitting string data)

Hi to everyone,

My problem is, that I'm not so quite sure, which way should I go.

The user is inputing by second part application a long string (let's
say 128 characters), which are separated by semiclon.
Example:

A20;BU;AC40;MA50;E;E;IC;GREEN

Now: each from this position, is already defined in any other table, as
a separate record. These are the keys lets say. It means, a have some
properities for A20, BU, aso.

Because this long inputed string, is a property of device (whih also
has a lot of different properities) I could do two different ways of
storing data:

1. By writing, in SP, just encapsulate each of the position separated
by semicolon, and write into a different table with index of device,
and the position in long stirng nearly in this way:

Major device data table
ID AnyData1 AnyData2 ... AnyData3
123 MZD12 XX77 ... any comment text
124 MZD13 XY55 ... any other comment

String data Table
fk_deviceId position value
123 1 A20
123 2 BU
123 3 AC40
....
123 8 GREEN

The device table, contains also a pointer (position), which might
change, to "hglight" specified position.

Then, I can very easly find all necessary data. The problem is, I need
to move the device record data (from other table) very often into other
history table (by each update). That will mean, that I also need to
move all these records from 1 -8 for example to a separate history
table, holding the index for a history device dataset. This is a little
inconvinience in this, and in my opinion, it will use to much storage
data, and by programming, I need always to shift this properities into
history table, whith indexes to a history table of other properities.

2. Table will be build nearly in this way:

Major device data table
ID AnyData1 AnyData2 ... AnyData3 stringProperty pointer
123 MZD12 XX77 ... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3
124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2

By writng into device table, there will be just a additional field for
this string, and I will have a function, which according to specified
pointer, will get me the string part on the fly, while I need it.
This will not require the other table, and will reduce the amout of
data, not a lot ... but always.
This solution, has a inconvinance, that it will be not so fast doing a
search over the part of this strings, while there will be no real index
on this.
If I woould like to search all devices, by which the curent pointer
value is equal GREEN, then I need to use function for getting the
value, and this one will be not indexed, means, by a lot amount of
data, might be slow.

I would like to know Your opinion about booth solutions.
Also, if you might point me the other problems with any of this
solution, I might not have noticed.

With Best Regards

MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

1. By writing, in SP, just encapsulate each of the position separated
by semicolon, and write into a different table with index of device,
and the position in long stirng nearly in this way:
>
Major device data table
ID AnyData1 AnyData2 ... AnyData3
123 MZD12 XX77 ... any comment text
124 MZD13 XY55 ... any other comment
>
String data Table
fk_deviceId position value
123 1 A20
123 2 BU
123 3 AC40
...
123 8 GREEN
>
The device table, contains also a pointer (position), which might
change, to "hglight" specified position.

This is the normal design in this situation.

Quote:

Originally Posted by

This design violates a basic principle in relational design: no repeating
groups.

Every rule is made to break, and I have occasionally put repeating groups in
the database I maintain, but this is a clearcut case: don't even think
about it. This sort of data is very difficult to work with in a
relational database, simply because it's not meant that you should
store data in this way.

Quote:

Originally Posted by

With a sub-table you need to repeat the ID. There will also be a cost
of two bytes for the length of each column. There is also the cost for
the field number, but since you don't have any semi-colon, this is a
net cost of one byte. There is also some overhead for each row. But
all and all, I would say that the overhead is about neglible.

Quote:

Originally Posted by

and by programming, I need always to shift this properities into
history table, whith indexes to a history table of other properities.

Don't really know what you mean here.

For completeness sake I should say that there is a third alternative,
and that is one table, but eight columns. This could also be considered
a repeating group. Then again, if the different fields represents
different attributes, it isn't really an repetition. This solution
is better my opinion than a seprated list, but the pointer you talk
about may be more difficult to implement.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>and by programming, I need always to shift this properities into
>history table, whith indexes to a history table of other properities.

>
Don't really know what you mean here.

Probably something along the lines of: (oversimplified for brevity)

insert into FooHistory select * from CurrentFoo
delete from CurrentFoo|||Then maybe you could create a view for the second table.

Matik wrote:

Quote:

Originally Posted by

Hi to everyone,
>
My problem is, that I'm not so quite sure, which way should I go.
>
The user is inputing by second part application a long string (let's
say 128 characters), which are separated by semiclon.
Example:
>
A20;BU;AC40;MA50;E;E;IC;GREEN
>
Now: each from this position, is already defined in any other table, as
a separate record. These are the keys lets say. It means, a have some
properities for A20, BU, aso.
>
Because this long inputed string, is a property of device (whih also
has a lot of different properities) I could do two different ways of
storing data:
>
1. By writing, in SP, just encapsulate each of the position separated
by semicolon, and write into a different table with index of device,
and the position in long stirng nearly in this way:
>
Major device data table
ID AnyData1 AnyData2 ... AnyData3
123 MZD12 XX77 ... any comment text
124 MZD13 XY55 ... any other comment
>
String data Table
fk_deviceId position value
123 1 A20
123 2 BU
123 3 AC40
...
123 8 GREEN
>
The device table, contains also a pointer (position), which might
change, to "hglight" specified position.
>
Then, I can very easly find all necessary data. The problem is, I need
to move the device record data (from other table) very often into other
history table (by each update). That will mean, that I also need to
move all these records from 1 -8 for example to a separate history
table, holding the index for a history device dataset. This is a little
inconvinience in this, and in my opinion, it will use to much storage
data, and by programming, I need always to shift this properities into
history table, whith indexes to a history table of other properities.
>
2. Table will be build nearly in this way:
>
Major device data table
ID AnyData1 AnyData2 ... AnyData3 stringProperty pointer
123 MZD12 XX77 ... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3
124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2
>
By writng into device table, there will be just a additional field for
this string, and I will have a function, which according to specified
pointer, will get me the string part on the fly, while I need it.
This will not require the other table, and will reduce the amout of
data, not a lot ... but always.
This solution, has a inconvinance, that it will be not so fast doing a
search over the part of this strings, while there will be no real index
on this.
If I woould like to search all devices, by which the curent pointer
value is equal GREEN, then I need to use function for getting the
value, and this one will be not indexed, means, by a lot amount of
data, might be slow.
>
I would like to know Your opinion about booth solutions.
Also, if you might point me the other problems with any of this
solution, I might not have noticed.
>
With Best Regards
>
Matik

|||First of all, thank you for your reply!

Now, some additional explenations maybe:

That was just an example, with 8 positions separated by semicolon as a
one property. The problem is, there number of this is various. That's
why, I couldyn't solve issue with fix number of column.

With shifting data into history, I've ment, that by each change of data
in primary table, whole record should be copied to the history table
(nearly same construction as primary table).
This is than an issue with the second table, storing semicolon
separated field in one column (splitted) in different table. This need
to be shifted then also, to a second historical table.

Of course, I could ommit using 'working' table, and have only history,
with inserts, and having a primary table containing a pointer to last -
newest record as my primary table, to get the newest record.
The problem is, I'm afraid a little of performance, sice there is all
other actions done on the primary table (select, searches aso.)
Having a big historical table, I will still need to get countinous
joins, to get the newest record, and even having a good indexing and
relation set up, it might be slow while table can be big.

This semicolon devided string, as example was shown pretty simmilar,
but it can be also various:

A10;B13;c20;bubu;lala;GREEN;RED
A13;BUBU;GREEN;YELLOW;mama
C25;YELLOW
BLUE;pleple;B13
aso.

The pointer I was talking about, is just a index, to which position in
this semicolon devided string, is curently activated.

Best regards

Matik|||Matik wrote:

Quote:

Originally Posted by

The problem is, I'm afraid a little of performance,

This has "premature optimization" written all over it. Build the
database cleanly first; then, if you /actually/ have performance
issues, then consider how to improve it (but breaking 1NF with "a;b;c"
type columns should still be a last resort).|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

I'm not sure that I see the problem. With a regular design, you would
have two tables for current data, and two tables for historical data.

Quote:

Originally Posted by

Like Ed said, get the design right first, and do performance tuning
when everything else is working. But some basic ideas for performance
are good when designing for performance. For instance no repeating
groups (i.e. semicolon-separated lists.)

Quote:

Originally Posted by

>
Of course, I could ommit using 'working' table, and have only history,
with inserts, and having a primary table containing a pointer to last -
newest record as my primary table, to get the newest record.
The problem is, I'm afraid a little of performance, sice there is all
other actions done on the primary table (select, searches aso.)
Having a big historical table, I will still need to get countinous
joins, to get the newest record, and even having a good indexing and
relation set up, it might be slow while table can be big.
>

The way to optimise is with good indexes and good query design. You say
"it might be slow" so obviously you haven't reached that stage yet. On
the other hand you know for sure that a redundant copy of the data will
have an additional performance cost, both for updates and queries.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

Friday, March 23, 2012

Optimize SQL

Monday, March 19, 2012

optimising a table with lots of boolean fields

Friday, March 9, 2012

Opinion about design needed (splitting string data)

OpenXML Identity Insert

Blog Archive

About Me