Compute column in view...

I have a SQL table that consists of columns A, B and C. I am trying to construct a view consisting of all columns (i.e. A, B, C) and a computed column. This computed column has the following logic:

If B is blank or null then
NewColumn = A + ' - ' C

else

NewColumn = A + ' - ' + B

I am just wondering how the SQL statement should look like...

SELECT

CASE B

WHEN IS NULL A+C

ELSE A+B

END CASE AS 'myComputeColumns',

COLUMN_A,

COLUMN_B,

COLUMN_C

FROM YOUR_TABLE or VIEWS

|||Thanks for your help...

Composite vs Single Primary Key

Our DBA has chosen to use an autonumber column as the primary key for a linking table consisting of 2 other IDs, I'll call them x and y. Another developer is concerned that since our application only keeps track of IDs x and y, not the autonumber ID, that searches will be less efficient because if x and y were a composite key they would be indexed. Is this correct? If so, couldn't you just create an index for these columns to make it equally efficient?

I believe that to eliminate all composite keys is a requirement for second normal form (correct me if I'm wrong), but does assigning an autonumber primary key to a linking table made up of solely 2 IDs have any benefits?

First of all, it's entirely possible to have the primary key (in the relational database sense, the one you can link tables with) different from the CLUSTERING key (the key for the main index of the table), e.g.:

Create Table T(p int primary key nonclustered, x int, y int);

Create Clustered Index T_i on T(x);

Also, you can always create secondary nonclustered indexes to improve efficiency, please look up indexing strategies articles (e.g. Books On-Line article http://msdn2.microsoft.com/en-us/library/ms189271.aspx), although I think for a linking table a single clustered index should be fine.

Now, for your particular issue I can't think of any benefit to an autonumber primary key except that if a lot of INSERT operations occur, and if the two other ID's are not naturally increasing (on average), then the autonumbering clustering key would keep the index from being fragmented and INSERT's would be quicker since you always insert at the end. Having said that, my gut feel is you shouldn't do it and you should keep the composite key. The composite key (if it's a primary key not just a clustereing key) also would enforce that no duplicate combinations would be inserted into the linking table.

|||

2nf just states that all data in a table must rely on the entire key.

that doesnt mean the key cant be composite.

if this table resolves a many to many relationship (Which sounds to be the case) then it is correctly designed for 2nd (even 3nf)

composite primary keys versus composite unique indexes

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

One item that is not always immediately apparent for this type of scenario has to do with clustering. Is your primary key also presently your clustered index? If the answer is yes and you change to a primary key based on an identity column and leave the CLUSTERED / NOT CLUSTERED aspect of this primary key to be defaulted then any query that still accesses data based on your current primary key will experience a new performance problem -- these accesses will now also require a bookmark lookup.

The good news is that this is might not grieve you for most OLTP activity, but it is likely to impact some reports and perhaps some record lookups; however, this is rarely enough of a drawback to preclude the change your are considering. Just file keep this in mind and if some reports or lookups become slower there is a chance that this modification might be the reason.

The bigger problem usually is modifying tables that reference your table with foreign key constraints. You have two alternatives: First, you can replicate the identity column into the foreign table and now use this value as the foreign key reference; however, if you do this you should also drop the columns that references the old foreign key columns of the table that you are changing. The second alternative is to continue to reference your modified table by what has now become the UNIQUE constraint rather than the primary key. This works and I have used this a number of times but it can be unsavory. Also, if you choose this alternative you need to know the that REFERENCES clause of the foreign key constraint will now need to explicitly list the columns of the UNIQUE constraint or your FK constraint will not compile.

Also, you might have some reports, functions, or stored procedures that filter based on one of these columns in one of your foreign tables. Changing the foreign key reference might be an impact here also.

The moral of this second problem is that you want to know all tables that have foreign key constraints to the table you are considering modifying. There might be a lot of hidden additional work if your table is referenced from a couple of dozen other tables.

Composite clustered index - column order

Want to check my thinking with you folks...

I have a table with a clustered composite index, consisting of 3 columns, which together form a unique key. For illustration, the columns are C1, C2 & C3.

Counts of distinct values for columns are C1 425, C2 300,000 & C3 4,000,000

C3 is effectively number of seconds since 01/01/1970.

The usage of the table is typically, insert a row, do something else, then update it.

Currently, the index columns are ordered C3,C1,C2. Fill factor of 90%.

My thinking is that this composite index is better ordered C1,C2,C3.

My reasoning is that having C3 as the leading column, biases all the inserts towards one side of the indexes underlying B-tree, causing page splits. Also, there'll be a bunch of "wasted" space across the tree, as the values going into C3 only ever get bigger (like an identity), so the space due to the fill factor in lower values never gets used.

Welcome your thoughts.

What are the data types of these columns? If C3 is a datetime or a bigint, updating it with a larger value (more seconds since 1970) should not be causing page splits. That usually happens with varchars that are updated to a larger value for example. You are usually better off to have a narrow clustered index.

What are you trying to accomplish here? Are you worried about SELECT performance, INSERT/UPDATE performance, or about index size and maintenance?

If C3 is being updated a lot, you might be better off to have the clustered index on C1, C2, and then have a non-clustered index on C3.

|||

"What are the data types of these columns"

char(4),Char(4) and int

"If C3 is a datetime or a bigint, updating it with a larger value (more seconds since 1970) should not be causing page splits"

Together the 3 columns provide unique key, and none of the columns are updated. The page splitting aspect I'm considering is, if the first column in the clustered is effectively an identity (so the next value inserted can only ever be bigger than the last), does this bias the inserts to one side of the tree - page splits being necessary there, because a fill factor spreads the free space throughout the tree?

"You are usually better off to have a narrow clustered index"

Yes. I appreciate that, because it gets tagged onto all non-clustered indexes. Let's assume that space isn't an issue.

Looking for best pewrformance for select \ insert & update. Index size & maint not an issue.

Thanks

Complex order by clauses

I have a table, basically consisting of products and their prices. I want to select some products, then sort them by price in ascending order BUT putting prices of zero at the bottom. (e.g. 5.99, 8.99, 10.99, 0.00, 0.00)

I thought I'd be able to do something like:

ORDER BY (price != 0), price

thinking that it would sort rows according to whether the condition was true or not, and then by price, but MSSQL doesn't seem to allow this. should this work, or is there another way around this? One solution would be to load the values into a table object and sort them using that, but I'd rather do all of this in SQL if possible, for speed.

any suggestions?

thanks!

try to do SOMETHING LIKE IN this EXAMPLE:

select

*into #testfrom(select 129.89 price)aa

insert

into #testselect 19.89

insert

into #testselect 1.89

insert

into #testselect 49.89

insert

into #testselect 39.89

insert

into #testselect 29.89

insert

into #testselect 0

insert

into #testselect 0

select

*from #TEst

order

by
case price
When 0then 1
else
0
END,
price

drop

table #test