| Author |
Topic |
|
dambo5
Starting Member
12 Posts |
 Posted - 2012-09-14 : 12:33:34
|
| Dear All,I have a table with millions records like this:id intdate datetime...description varchar(2000)Is there a possibility to create a 'link' ('alias') of the column description so that it points to a new column in a new table or so, ending up with much more records per 4K page. (afterwards i'll reorganize clustered index and everything). This way I will not need to change the source code and procedures whenever this table is mentioned. The reason why I need this is because very often the request is to get id and date only in the reports and very rare i need select * from table or select description for table.Thank you,Aleksandar |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-14 : 12:52:03
|
| Just create a NC index with just id, date-Chad |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-16 : 01:38:37
|
| Just to clarify - is it a replica of the same data?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
dambo5
Starting Member
12 Posts |
Posted - 2012-09-18 : 05:43:04
|
| Yes it is replica of the same data.As for the covering index I think I did not ask the question correctly. I have many many columns in the table and many queries. I should include many indexes which will dramatically slow down my inserts/updates (physically)I was thinking of a solution which will be something like a look up column which will insert the big data somewhere in other db (which I can put on some other disks)Thank you.Aleksandar |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-18 : 14:35:56
|
| Based on what you're saying , a few suggestions.1) Place non clustered indexes on a separated drive , in separate filegroup2)Indexed View - As for performance – an indexed view can perform better given the circumstances. For example , if the dataset is not updated frequently and the query deals with large aggregations.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-09-18 : 14:39:16
|
| I'm pretty sure there is a way to force SQL to store the varchar field off row. I can't remember the command to do it though.-Chad |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-19 : 01:41:03
|
| sp_tableoption N'MyTable', 'large value types out of row', 'ON'You can also check the state via DBCC PAGEJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
dambo5
Starting Member
12 Posts |
Posted - 2012-09-19 : 02:17:45
|
| Exactly what I was trying to achieve (at least from what it says in the manuals for this option)I'll give it a try, test performance and write back the results.Thank you all |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-19 : 10:27:29
|
| I think this relates to Varchar(max),nvarchar(max),varbinary(max) and xml columns.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
dambo5
Starting Member
12 Posts |
Posted - 2012-09-21 : 04:41:04
|
| jackv you are rightIt does apply to max columns.Now is it clever to redefine columns from nvarchar(2000) or nvarchar(4000) to nvarchar(max) so that I can "move" the data? |
|
|
|
|
Reorganize physical pages with alias columns