clustered index problem in creating bacpac file

al4629740
al4629740 used Ask the Experts™
on
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].[frmUnregisteredEvent] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
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].[tblBackupSummerYouthGrid] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
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] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
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].[tblOrgMonthlyProgressSetbacks] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
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].[tblOrgCurrentActivity] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblSummerYouthGrid] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblOrgCommunityAreas] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[frmRegistrationPITW] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
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].[frmRegistrationBridges] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
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] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[tblSummerYouthGridBackup2012] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[frmRegistrationSummerYouthEmploymentBackup2012] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
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].[frmRegistrationSummerYouthEmployment] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
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
------------------------------
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Developer
Top Expert 2014
Commented:
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:

ALTER TABLE [dbo].[tblSQLStrings]
ADD [ID] INT IDENTITY(1,1) PRIMARY KEY
GO

Open in new window


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.

Author

Commented:
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...

Author

Commented:
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?
Russell FoxDatabase Developer
Top Expert 2014

Commented:
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;'

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial