I have 10 million records in a table. But the table is created on primary filegroup.All the indexes and primary keys were created in primary filegroup.There 5 partitions on the table which are also on Primary File group. Is there any way to move Table, indexes and primary key and partitions to user-defined filegroup Without loosing or changing the data in the table?
121386
I have big SQL Server database, with a lot of blob data in some tables.  For performance optimization, I want to move one table to separate hard drive.  I created new filegroup, and "row data" file.  But, I can't change any parameters in table properties: they are all disabled.  And I don't know a way to enable it.  Can anybody help me?
87554
We have 2 servers:  Server A. production (no partitions) Server B. New Server (partitions and compression)  We restored a backup to the new server, partitioned a large table by datetime and turned on compression, and we were happy. Trying to be clever, we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression. We cannot query any old data on the new server, only data from when we turned on replication. The clustered index is the old one, and compression is not on, and no partitions to be seen in SSMS, it's using the PRIMARY filegroup  (same as the current live server is using). On the physical disk, I can see the partition files, and they are still massive with GBs of data, so I know the data is there, but it appears the table has lost the link to it. It appears turning on replication replicated the filegroup side of things. Is there a way to point the table back to partitions and save all the work we did getting it ready for switch over? It's a pretty large database. This one table is 11TB on the production server. SQL Server 2017 Enterprise Edition.
11758
Given the schema:  create table containers (     id int primary key,      name text,      product_id int,      lot int,      qty int,      parent_id int);  create table orders_items (     id int,     position int,     container_id int);   Where containers is a hierarchical table with an unknown number of levels.   containers.parent_id = containers.id OR null   A container can hold other containers, this is a containers tree structure:    23 Box 40 Parent  [ID: NULL, Lot: NULL, Qty: SUM(20+20)=40]    |    |--22 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]    |   |--21 Bag 10 [Parent ID: 22, Lot: 701, Qty: 10]    |   |--22 Bag 10 [Parent ID: 22, Lot: 703, Qty: 10]    |     |--19 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]        |--17 Bag 10 [Parent ID: 19, Lot: 700, Qty: 10]        |--18 Bag 10 [Parent ID: 19, Lot: 701, Qty: 10]    For us a container is considered as one expeditionary unit, depending on customer requirements a container can be a simple plastic bag of 10 pieces, or a full pallet with 10 boxes of 5000 pieces each one. Only containers of last level has a lot assigned, parent containers can hold different lots, but always belonging to same product.  We need to list all products included in a single order, group by lot.   Different products can't have the same lot. One container can only hold containers of the same product.   Sample data:  insert into containers values   (23, 'Box 40',  2, null, 40,  null) , (16, 'Pallet',  1, null, 120, null) , (12, 'Bag 20',  1, null, 20,  14) , (13, 'Bag 20',  1, null, 20,  14) , (14, 'Box 40',  1, null, 40,  16) , (19, 'Bag 20',  2, null, 20,  23) , (22, 'Bag 20',  2, null, 20,  23) , (5,  'Bag 20',  1, null, 20,  7) , (6,  'Bag 20',  1, null, 20,  7) , (7,  'Box 40',  1, null, 40,  16) , (1,  'Bag 10',  1, 500,  10,  5) , (2,  'Bag 10',  1, 501,  10,  5) , (3,  'Bag 10',  1, 502,  10,  6) , (4,  'Bag 10',  1, 500,  10,  6) , (8,  'Bag 10',  1, 600,  10,  12) , (9,  'Bag 10',  1, 601,  10,  12) , (10, 'Bag 10',  1, 502,  10,  13) , (11, 'Bag 10',  1, 501,  10,  13) , (15, 'Box 40',  1, 600,  40,  16) , (17, 'Bag 10',  2, 700,  10,  19) , (18, 'Bag 10',  2, 701,  10,  19) , (20, 'Bag 10',  2, 703,  10,  22) , (21, 'Bag 10',  2, 701,  10,  22);  insert into orders_items values (1, 1, 16), (1, 2, 23);   The order number 1 has two details lines, containers 16&amp; 23, I need to get all lots included in these containers.  In this example the result should show this rows:  +----+------------+-------------+------+------+------------+ | id |    name    |  product_id |  lot |  qty |  parent_id | +----+------------+-------------+------+------+------------+ |  1 |  'Bag 10'  |           1 |  500 |   10 |          5 | |  2 |  'Bag 10'  |           1 |  501 |   10 |          5 | |  3 |  'Bag 10'  |           1 |  502 |   10 |          6 | |  4 |  'Bag 10'  |           1 |  500 |   10 |          6 | |  8 |  'Bag 10'  |           1 |  600 |   10 |         12 | |  9 |  'Bag 10'  |           1 |  601 |   10 |         12 | | 10 |  'Bag 10'  |           1 |  502 |   10 |         13 | | 11 |  'Bag 10'  |           1 |  501 |   10 |         13 | | 15 |  'Box 40'  |           1 |  600 |   40 |         16 | | 17 |  'Bag 10'  |           2 |  700 |   10 |         19 | | 18 |  'Bag 10'  |           2 |  701 |   10 |         19 | | 20 |  'Bag 10'  |           2 |  703 |   10 |         22 | | 21 |  'Bag 10'  |           2 |  701 |   10 |         22 | +----+------------+-------------+------+------+------------+   Group by lot:  |----------|---------|-----|----------| | Order ID | Product | Lot | Quantity | |----------|---------|-----|----------| |    1     |    1    | 500 |    20    | |    1     |    1    | 501 |    20    | |    1     |    1    | 502 |    20    | |    1     |    1    | 600 |    50    | |    1     |    1    | 601 |    10    | |    1     |    2    | 700 |    10    | |    1     |    2    | 701 |    20    | |    1     |    2    | 703 |    10    | |----------|---------|-----|----------|   I've created a  example with this values.
9742
I just need advice on how I am going to optimize my database.  Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN   I have that format of tables on my database. Each tables has 30-days of records with hundred thousands of records each data. Uploading of data to database is every morning. The data of yesterday will be uploaded.  The most common queries to run is grouped by Date or Date and Area or Date and Area and Block. Now, in order to make queries faster, I found out using indexes. I used indexes before. But that was when I am using a primary key. In this table, I didn't use primary key because it is totally unnecessary.   Now, I am really confused on how I am going to optimize this. Based on  on Column Considerations part, clustered index is not a good choice on Columns that undergo frequent changes. I am really confused.  Please help me if I need to use clustered index here or just non clustered or both.
38040
Two things I'd like to know:   how do you safely move tempdb with minimal downtime? how many tempdb files do you need?   Is it 1 file per core? So quad-core = 4 tempdb files, creating three new ones?
36406
I have a task: I have created a database with a filegroup lamb that has a few tables.  I need to extract a csv into a table in a filegroup named lamb and back up that filegroup.  Then extract another csv file again and back up again.  But I need to be able to restore the first backup.  What is the backup and restore method? Is it possible to restore only the lamb filegroup?
34502
I have backups up everything, but I'm still uneasy about the process and would like confirmation. I'd rather do this one time correctly. I want to shrink /dev/sda6/ by 200GB and reallocate it to /dev/sda2. I boot a LiveCD and turn off LinuxSwap (locked otherwise). I've been reading that it is very hard to move &quot;left&quot; when partitioning and other AskUbuntu threads are confusing me. Do I need to resize /dev/sda6 by 200GB &quot;preceding&quot; (rather than &quot;following&quot;), and then resize /dev/sda4 by 200GB? A little guidance would ease my nerves of messing everything up. Thank you ahead of time.
85229
I'm trying to determine how I should store historical transactional data.  Should I store it in a single table where the record just gets reinserted with a new timestamp each time?  Should I break out the historical data into a separate 'history' table and only keep current data in the 'active' table.  If so, how do I best do that?  With a trigger that automatically copies the data to the history table?  Or with logic in my application?  Update per Welbog's comment:  There will be large amounts of historical data (hundreds of thousands of rows - eventually potentially millions)   Primarily searches and reporting operations will be run on the historical data.  Performance is a concern.  The searches shouldn't have to run all night to produce results.
33028
I have a SQL Server table that contains over 20 billion rows, with 13 billion rows in the last partition.  The table is currently partitioned 6 ways by a "Time" field:  2011 2012 2013 2014 2015 2016/2017/2018   The 2016/2017/2018 partition needs to be split into their own partitions so there is a 2016, 2017, 2018 partition.    How can this be done and in the process avoiding a massive log file growth? The database is in Simple Recovery and currently each partition is in a separate file group on its own set of disks.   CREATE TABLE [dbo].[Log](        [RefNo] [nchar](7) NOT NULL,        [DevID] [nvarchar](7) NOT NULL,        [Time] [datetime] NOT NULL,        [summary] [float] NULL,        [staging] [float] NULL,        [position] [float] NULL,  CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED  (        [RefNo] ASC,        [DevID] ASC,        [Time] ASC  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 96)  )
51266
I have a 90GB SSD but 90GB soon gets filled up.  My users folder alone is 26GB.  To get round this I use Symlinks (read the  for more).    Is there any way to make a separate SSD and HDD work like a hybrid so that frequently accessed files are moved to the SSD automatically?  This would be a nice addition to Windows 8 but I'm not holding out my hopes.    I guess ReadyBoost is a bit like this but its more designed for USB sticks I think.  I did try installing Windows 7 with the SSD as a readyboost but didn't really notice any much improvement over just using the HDD.
85257
Are there any (good known, reliable) file systems on Linux that store the creation time of files and directories in the i-node table?   If there are, is the "changed" time replaced by the creation time of an i-node in a stat call?
72309
I have only 2GB left, so I need to remove this history table. This table now is empty but the database disk space not released. And the database file is 320GB.
45539
I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was reduced but the server's ibdata1 file went crazy. Any ideas?
75479
I'm about to delete a large table which is approximately 100GB in size.  I'd like to understand the impact on the mirroring that's in place on this db.  Can anyone confirm?
121095
I have a table with a varchar(max) column that stores text ~4-10KB in size. Data is just being added - never modified, it is a log. My problem is that a table grows too large in size - I would like to keep about 1000000 rows in this table.   I am limited to SQL Server Express and hit the limit on database size. Compression is not available in SQL Server Express and I afraid that Filestream may not be appropriate and the number of files would be too big.   What is the best solution in this case?
119820
I have a memory optimised table in SQL Server 2016, with file durability enabled. The table is only 100MB. SQL Server is constantly creating new files on disk, which start off at a few hundred MB, but then grow constantly until the drive runs out of space then they clear down and the process starts again. The files are growing at about 900MB an hour. Running the SQL below shows that all the space is in Precreated Data - so it's not even actually storing large amounts of data, just allocating space to use later.  SELECT state_desc, file_type_desc, COUNT(*) AS [count],    SUM(file_size_in_bytes) / 1024 / 1024 AS [on-disk size MB]    FROM sys.dm_db_xtp_checkpoint_files   GROUP BY state, state_desc, file_type, file_type_desc   ORDER BY state, file_type     File growth on the file group is set to unlimited, so I'm assuming I can set a value on this and stop it growing, but I can't find anything to say why it's allocating so much space in the first place. The data in the table does change very rapidly (a lot of inserts and deletes as the data is constantly processed, but it the data that isn't Precreated never gets over 4GB.  Is there a setting in SQL Server that controls this file precreation? Is it safe to set a limit on the autogrowth for the file group?
66948
Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to another group.  When trying to decide on a primary key for the item table, what should I use?  Should I make up an artificial global serial key like this:  CREATE TABLE items (     item serial PRIMARY KEY,     group integer NOT NULL REFERENCES groups(group), );   ... or should I rather use a composite key and per-group item serial like this:  CREATE TABLE items (     group integer NOT NULL REFERENCES groups(group),     item integer NOT NULL,      PRIMARY KEY(group, item) );   The reason why I'm leaning more towards the second solution is that the post URL will always show the group and item, so it makes sense to have both of them as the composite primary key. In case of the first solution, the URL contains superfluous information because the group ID can already be deduced from the item ID alone. The URL structure is given, however, and cannot be changed.  The disadvantage of the second solution is that you have to manage a per-group serial (i.e. each item integer should start from 0 for each group).  What's better in terms of best practices, normalization and performance? Or is it simply a matter of taste?