clustered index problem in creating bacpac file
I am trying to export data-tier application to create a bacpac file. When running the utility I get the following error:
Mostly clustered index errors. What does that mean and how do I resolve this issue to that I can create the file? If I create a clustered index, what does that do to my data? I am ultimately loading my SQL on prem to SQL Azure
TITLE: Microsoft SQL Server Management Studio
--------------------------
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71564: Table Table: [dbo].[tblSQLStrings] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblIVPAMonthly] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[frmUnregisteredEven
Error SQL71564: Table Table: [dbo].[tblKeyLeaders] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblBackupSummerYout
Error SQL71564: Table Table: [dbo].[tblSYABGrid] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblCCRet] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[frmRegistrationCSW]
Error SQL71564: Table Table: [dbo].[tblIVPATA] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblOrgMonthlyProgre
Error SQL71564: Table Table: [dbo].[tblYAT2] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblYAT1] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblMonthlyReport3] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblOrgCurrentActivi
Error SQL71564: Table Table: [dbo].[tblSummerYouthGrid]
Error SQL71564: Table Table: [dbo].[tblOrgCommunityArea
Error SQL71564: Table Table: [dbo].[frmRegistrationPITW
Error SQL71564: Table Table: [dbo].[tblOrgProfile] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[AttendanceGrid] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[frmRegistrationBrid
Error SQL71564: Table Table: [dbo].[tblOrgSubGroup] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblAttendanceSYAB] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblRegistrationSYAB
Error SQL71564: Table Table: [dbo].[tblSummerYouthGridB
Error SQL71564: Table Table: [dbo].[frmRegistrationSumm
Error SQL71564: Table Table: [dbo].[tblIVPAPlan] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblJJDP] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblOrgEvents] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblMonthlyReport1] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[frmRegistrationSumm
Error SQL71564: Table Table: [dbo].[tblIVPAActivities] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblIVPAStaff] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblDueDate] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblDailyLog] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblMonthlyReport4] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
(Microsoft.SqlServer.Dac)
--------------------------
BUTTONS:
OK
--------------------------
Mostly clustered index errors. What does that mean and how do I resolve this issue to that I can create the file? If I create a clustered index, what does that do to my data? I am ultimately loading my SQL on prem to SQL Azure
TITLE: Microsoft SQL Server Management Studio
--------------------------
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71564: Table Table: [dbo].[tblSQLStrings] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblIVPAMonthly] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[frmUnregisteredEven
Error SQL71564: Table Table: [dbo].[tblKeyLeaders] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblBackupSummerYout
Error SQL71564: Table Table: [dbo].[tblSYABGrid] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblCCRet] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[frmRegistrationCSW]
Error SQL71564: Table Table: [dbo].[tblIVPATA] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblOrgMonthlyProgre
Error SQL71564: Table Table: [dbo].[tblYAT2] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblYAT1] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblMonthlyReport3] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblOrgCurrentActivi
Error SQL71564: Table Table: [dbo].[tblSummerYouthGrid]
Error SQL71564: Table Table: [dbo].[tblOrgCommunityArea
Error SQL71564: Table Table: [dbo].[frmRegistrationPITW
Error SQL71564: Table Table: [dbo].[tblOrgProfile] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[AttendanceGrid] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[frmRegistrationBrid
Error SQL71564: Table Table: [dbo].[tblOrgSubGroup] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblAttendanceSYAB] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblRegistrationSYAB
Error SQL71564: Table Table: [dbo].[tblSummerYouthGridB
Error SQL71564: Table Table: [dbo].[frmRegistrationSumm
Error SQL71564: Table Table: [dbo].[tblIVPAPlan] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblJJDP] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblOrgEvents] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblMonthlyReport1] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[frmRegistrationSumm
Error SQL71564: Table Table: [dbo].[tblIVPAActivities] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblIVPAStaff] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblDueDate] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblDailyLog] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblMonthlyReport4] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.
(Microsoft.SqlServer.Dac)
--------------------------
BUTTONS:
OK
--------------------------
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It's telling you that every table must have a clustered index (and almost every table you ever make should). When you create a clustered index, SQL will actually re-order your data on disk into the order of the index. Most people just create an ID integer field with an auto-increment value:
Now, if you have 1002 records and you add a new one, SQL will automatically include ID=1003 when you do the insert. You can only have one clustered index on a table because you can't physically re-order them multiple ways.
You can have many non-clustered indexes because those are stored separate from the data. Say your tblSQLStrings has a DateCreated field and you often query for items by date. You can create a non-clustered index on DateCreated and your query will run faster because it can quickly check the stored index for a range of values. The index points to the records rather than moving them around on disk.
ALTER TABLE [dbo].[tblSQLStrings]
ADD [ID] INT IDENTITY(1,1) PRIMARY KEY
GO
Now, if you have 1002 records and you add a new one, SQL will automatically include ID=1003 when you do the insert. You can only have one clustered index on a table because you can't physically re-order them multiple ways.
You can have many non-clustered indexes because those are stored separate from the data. Say your tblSQLStrings has a DateCreated field and you often query for items by date. You can create a non-clustered index on DateCreated and your query will run faster because it can quickly check the stored index for a range of values. The index points to the records rather than moving them around on disk.
So do I need to go through all my tables and identify which columns are frequently used or can I just simply convert the Primary Key, which is usually the ID, into a clustered index. Hopefully I understood that right...
Also, if I create a clustered index now with a decent size DB, will it take quite some time for the server to "reorder" things thus possibly chewing up resources?
Yes, and yes, unfortunately. You might want to run it during off-hours. The good news is that on a large table you're likely to see a performance improvement after the index is created. Here's a script that will identify all tables on a server that do not have a primary index (type_desc = "heap"). I'm doing this very project in my next sprint: one of my web developers created a bunch of un-indexed tables and it's killing performance!
EXEC sys.sp_MSforeachdb
'USE ?;
IF DB_NAME() NOT IN(''master'', ''msdb'', ''tempdb'', ''model'')
SELECT
DB = DB_NAME()
, o.name
, i.type_desc
, o.type_desc
, o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.type_desc = ''USER_TABLE''
AND i.type_desc = ''HEAP''
ORDER BY o.name;'
Premium Content
You need an Expert Office subscription to comment.Start Free Trial