Maximum No of Columns
-
Is there any limitation for the maximum number of columns in a table ?
subban -
1024
-
How many do you need? If it's more than 1024, I suggest you revist your design.
Also keep in mind the max byte size for a row is 8060, so havin 1024 means < 8 bytes per field.
-
Another interesting thing to note is that if you use a lot of columns and use ADO to update records you can get double the number of columns sent in a sql statement. Check it out with sql profiler!
1024 seems like over kill but you might have some needs if your doing some interesting word matching routines based on phonemes or syllables or other stemming types of data. I'm not certain how normalizing the design or partitioning the design would help these types of scenarios. You kind of want to have the selectivity of an index dependent on these columns sets to be directly dependent on the clustered set of words.
Can be useful when joining words against other types of database entities? Some would say FT indexes are a better answer but the are an administrative overhead right now with there current implementation. What's needed of course is benchmarks for your situation.
One thing to note is that its the maximum number of nonclustered indexes per table 249 and the maximum numbers of columns per index 16 that can be bigger factor in these kinds of designs.
As fars as implementation specs go check out this MSDN link to see the
details.
Peter Evasn (__PETER Peter_)
Maximum Capacity Specifications
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_90rs.asp
Configuration Options Specifications
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_90rs.asp
Memory Used By Sql Server Objects specifications
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_90rs.asp
-
Actually what i was trying to do is ( As discussed in my previous Query) transposing a table which is having 24 Columns and 2500 Rows,
In that case , if i traspose the table it will become 2500 Columns and 24 Rows,
It is practicaly not possible (2500 Columns) !!!!!!
That is it
Thanks to all
subban -
Sounds like what you want is this upcoming SQL Server 2005 feature in the update to T-SQL for SS2k5
Peter Evans (__PETER Peter_)
C&P from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_90rs.asp
The new PIVOT operator in SQL Server Yukon allows you to write crosstab queries that rotate rows into columns. The UNPIVOT operator does the opposite—working on pivoted data to rotate columns into rows. Figure 11 shows the result of using the PIVOT operator in the database when you want to return yearly total sales order values for each salesperson, with each year's value appearing in a different column.
The important thing to note when using the PIVOT operator is that you need to provide it with a query expression that returns only the columns of interest by using a view, derived table, or a CTE. The reason is that PIVOT makes an implicit GROUP BY operation behind the scenes on all columns that are not explicitly referenced by the operator. In this case, you need the salesperson ID, the order year, and the order value:
USE AdventureWorksSELECT SOH.SalesPersonID, YEAR(SOH.OrderDate) AS OrderYear, SOD.OrderQty * SOD.UnitPrice AS OrderValueFROM SalesOrderHeader AS SOH JOIN SalesOrderDetail AS SOD ON SOD.SalesOrderID = SOH.SalesOrderID
SQL Server figures out that the "GROUP BY" column list should be the list of columns in the input table that were not referred to explicitly by the PIVOT operator in the aggregate function or in the IN clause. So that you don't get undesired columns in the implicit GROUP BY column list, you need to provide the PIVOT operator with an input table containing only the columns of interest for the aggregate function, IN clause, and implicit GROUP BY. This can be achieved by using a CTE or a derived table containing the previous query that returns only the columns of interest.
The code in Figure 12 shows how you use this query within a CTE, and have the outer query issue a PIVOT operation on the CTE's results. SUM(OrderValue) tells PIVOT which aggregation to calculate to populate the cells of the pivoted column. The FOR clause tells PIVOT which source column contains the values to rotate into result columns. The IN clause contains the list of values that are going to appear as result column names.
SQL Server requires you to specify explicitly the list of values in the IN clause to rotate to result columns. You can't use a static query and have SQL Server figure out all distinct values in OrderYear. To achieve this, you have to use dynamic execution to construct the query string dynamically, as the code in Figure 13 shows.
To see the UNPIVOT operator in action, first create the SalesPivoted table by running the query in Figure 12 with the addition of "SELECT INTO SalesPivoted" before the FROM clause (see Figure 14). The UNPIVOT operator's arguments are very similar to PIVOT's arguments. But this time you specify a name of a result column that will contain the pivoted cell's values all in one column. Following the FOR clause, you specify the name of the result column that will store the names of the pivoted columns as column values. In the parentheses following the IN clause, specify the list of pivoted columns you want to unpivot:
SELECT *FROM SalesPivoted UNPIVOT(OrderValue FOR OrderYear IN([2001], [2002], [2003], [2004])) AS U
UNPIVOT does not return rows for cells that contained NULL values. To clean up the extra table and index I've built in the database, run the following code:
DROP INDEX SalesOrderHeader.idx_nc_OrderDateDROP TABLE SalesPivoted
-
As per BOL,
Columns per base table is 1024
Columns per SELECT statement is 4096
Columns per INSERT statement is 1024
so if you can find out a way of pivoting by using selects only (i know it is a tough one ... ), then you may be able to do it.
-
Actually what i was trying to do is ( As discussed in my previous Query) transposing a table which is having 24 Columns and 2500 Rows,
In that case , if i traspose the table it will become 2500 Columns and 24 Rows,
It is practicaly not possible (2500 Columns) !!!!!!
To me, there is only one answer to your question. Don't do this at the server. This is job for your front-end app. And a rather easy one!
BTW, there is no such nonsense as a pivot table. Any beginner in statistics courses will tell that this thing was, is and probably will be a crosstab.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I agree with Frank on this one. You'd be better off doing it at the front end. You [could] potentially split the columns between several tables and use a key to link the rows I suppose, but I wouldn't open that can o' worms...
Also, I hate to stir up trouble and pick nits - or offend anyone - but I'm certain I read somewhere about the actual number of bytes available being [less] than 8060 since there's some header/baggage info for each row. I seem to recall it being something like 8039 or 8038. I haven't validated that information but I recall it was from a pretty reliable source. If Steve is monitoring this thread I'm hoping he may throw his two cents in on this. I may also be referencing old information on a previous version of SQL Server (flashbacks from being a DBA on SQL 4.21...:blink
Anybody have any input on this?
Cheers,
Ken
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply