Advanced SQL tutorial pdf, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, Handling Large SQL Server Tables with Data Partitioning, How to handle a large table on SQLServer - Server Fault, how to handle huge millons of data on sql server, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, sql server - Best way to index this very large table, SQL Server - General Interview Questions and Answers, sql server 2008 - How to handle huge table, sql server 2008 - SQL large table design, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Forums - How to handle huge database tables, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions, Top 10 Must-Do Items for your SQL Server Very Large Table, Very large tables in SQL Server
SQL Server Interview Question – How do you manage very large tables in SQL Server ?
This question has been asked to me many times. So finally I have decided to make the answer online, many more people also wrote blogs about this. I also have some points. The performance of very large table can be measured by following things-
- Load Data Performance
- How quickly we can insert data into very large tables.?
- Query Performance
- How our queries are performing which includes large tables?
- Data Management Performance(Backups, Rebuilding Indexes, Updating Statistics)
- How quick you can take your backups, rebuild indexes and update statistics.?
Solutions to this problem are-
- Use Multiple File Groups and Files
- Insert data into empty UnIndexed tables and then create indexes on it.
- Use Partitioned Tables
- Use ReadOnly File Groups
- Check Constraints
Now I will discuss the above solutions in details.
Use Multiple File Groups and Files
Here you need to spread data to as many as disks possible. This will help in storage and provide good performance.
Check constraints informs SQL Server which data to be inserted in which table.
We will get good performance for queries that specify predicates that allow SQL Server to do partition elimination. Also note that this feature is only available in enterprise version. Other advantages are given below-
- Table can spans multiple file groups. It gives us huge advantage on performance and storage.
- We can load data and extract data as a metadata-only operation with SWITCH commands.
- Partition wise update stats(Starting SQL Server 2014).
- Partition level index maintenance. If you have SQL Server 2014 so you can even do online-rebuilds per partition.
- Column Store Indexes at partitions (Starting SQL 2012).
- Per Partition Compression.
Insert data into empty UnIndexed tables and then create indexes on it.
Well if insert data into a big table with multiple indexes, it will take more time then inserting data into a empty table and build indexes on it.
Use ReadOnly File Groups
- It just requires one backup after becoming read only
- Don’t require any maintenance
- Don’t require page lock and row locks
I hope you have enjoyed the article. Cheers, Thanks for reading !