T-SQL: How to Add an Index

Hello:

Below is my T-SQL view.  The data in this view was returned a lot more quickly, before I added two lines to it.  I added the following lines to this view:

INNER JOIN GL20000 ON AAG30000.JRNENTRY = GL20000.JRNENTRY
INNER JOIN GL30000 AS GL30000_1 ON AAG40000.JRNENTRY = GL30000.JRNENTRY

I added these lines, because I will need to eventually pull fields from the GL20000 and GL30000 tables.

I'm sure that I can speed up the time for the data that is returned, if I only knew how and where to add indexes.  

I'm not too familiar with the syntax for adding indexes nor do I understand the difference even between a clustered and nonclustered index.  Also, I need to know if there is any harm to the database or to the data, if I add indexes.

Can someone please let me know what syntax I need to use for adding indexes to this view for those two lines, in order to speed up the time for the data that is returned?

Thanks!

TBSupport

--ALTER VIEW [ACCTTRANSREVISED] AS
SELECT DISTINCT
                      AAG30000.JRNENTRY AS [Journal Entry], AAG30000.GLPOSTDT AS [Transaction Date], GL00105.ACTNUMST AS [Account Number],
                      GL00100.ACTDESCR AS [Account Description], GL00102.ACCATDSC AS [Account Category],    AAG30001.DEBITAMT AS [Debit Amount], AAG30001.CRDTAMNT AS [Credit Amount],
                      COALESCE (AAG00401.aaTrxDimCode, '') AS [Transaction Dim Code], COALESCE (AAG00401.aaTrxDimCodeDescr, '') AS [Transaction Dim Code Description], GL00100.ACTINDX as [Account Index],
                      CASE WHEN GL00100.ACCTTYPE = '1' THEN 'Posting Account'
                                          WHEN GL00100.ACCTTYPE = '2' THEN 'Unit Account'
                                          WHEN GL00100.ACCTTYPE = '3' THEN 'Posting Allocation Account'
                                          WHEN GL00100.ACCTTYPE = '4' THEN 'Unit Allocation Account'
                                          ELSE '' END as [Account Type],
                              CASE WHEN GL00100.ACTIVE = '1' THEN 'Active' ELSE 'Inactive' END as [Active Or Inactive],
                              CASE WHEN GL00100.PSTNGTYP = '0' THEN 'Balance Sheet' ELSE 'Profit and Loss' END as [Posting Type],
                              CASE WHEN GL00100.TPCLBLNC = '0' THEN 'Debit' ELSE 'Credit' END as [Typical Balance]
FROM         AAG30000 INNER JOIN
                      AAG30001 ON AAG30000.aaGLHdrID = AAG30001.aaGLHdrID INNER JOIN
                      GL00105 ON AAG30001.ACTINDX = GL00105.ACTINDX INNER JOIN
                      GL00100 ON GL00105.ACTINDX = GL00100.ACTINDX LEFT OUTER JOIN
                      AAG30003 ON AAG30001.aaGLHdrID = AAG30003.aaGLHdrID AND AAG30001.aaGLDistID = AAG30003.aaGLDistID LEFT OUTER JOIN
                      AAG00401 ON AAG30003.aaTrxCodeID = AAG00401.aaTrxDimCodeID AND
                      AAG30003.aaTrxDimID = AAG00401.aaTrxDimID
                              INNER JOIN GL00102 ON GL00100.ACCATNUM = GL00102.ACCATNUM
                              INNER JOIN GL20000 ON AAG30000.JRNENTRY = GL20000.JRNENTRY
--where GL20000.DSCRIPTN <> ''
GROUP BY AAG30001.aaGLDistID, AAG30000.JRNENTRY, AAG30001.DEBITAMT, AAG30001.CRDTAMNT, GL00105.ACTNUMST,
                      GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr, AAG30000.GLPOSTDT, GL00102.ACCATDSC, GL00100.ACTINDX, GL00100.ACCTTYPE, GL00100.ACTIVE, GL00100.PSTNGTYP, GL00100.TPCLBLNC
UNION ALL
SELECT DISTINCT
                      AAG40000.JRNENTRY AS [Journal Entry], AAG40000.GLPOSTDT AS [Transaction Date], GL00105.ACTNUMST AS [Account Number],
                      GL00100.ACTDESCR AS [Account Description], GL00102.ACCATDSC as [Account Category], AAG40001.DEBITAMT AS [Debit Amount], AAG40001.CRDTAMNT AS [Credit Amount],
                      COALESCE (AAG00401.aaTrxDimCode, '') AS [Transaction Dim Code], COALESCE (AAG00401.aaTrxDimCodeDescr, '') AS [Transaction Dim Code Description], GL00100.ACTINDX AS [Account Index],
                       CASE WHEN GL00100.ACCTTYPE = '1' THEN 'Posting Account'
                                          WHEN GL00100.ACCTTYPE = '2' THEN 'Unit Account'
                                          WHEN GL00100.ACCTTYPE = '3' THEN 'Posting Allocation Account'
                                          WHEN GL00100.ACCTTYPE = '4' THEN 'Unit Allocation Account'
                                          ELSE '' END as [Account Type],
                                    CASE WHEN GL00100.ACTIVE = '1' THEN 'Active' ELSE 'Inactive' END as [Active Or Inactive],
                                    CASE WHEN GL00100.PSTNGTYP = '0' THEN 'Balance Sheet' ELSE 'Profit and Loss' END as [Posting Type],
                                    CASE WHEN GL00100.TPCLBLNC = '0' THEN 'Debit' ELSE 'Credit' END as [Typical Balance]
FROM         AAG40000 INNER JOIN
                      AAG40001 ON AAG40000.aaGLHdrID = AAG40001.aaGLHdrID INNER JOIN
                      GL00105 ON AAG40001.ACTINDX = GL00105.ACTINDX INNER JOIN
                      GL00100 ON GL00105.ACTINDX = GL00100.ACTINDX LEFT OUTER JOIN
                      AAG40003 ON AAG40001.aaGLHdrID = AAG40003.aaGLHdrID AND AAG40001.aaGLDistID = AAG40003.aaGLDistID LEFT OUTER JOIN
                      AAG00401 ON AAG40003.aaTrxCodeID = AAG00401.aaTrxDimCodeID AND AAG40003.aaTrxDimID = AAG00401.aaTrxDimID LEFT OUTER JOIN
                      GL30000 ON AAG40000.JRNENTRY = GL30000.JRNENTRY
                      INNER JOIN GL00102 ON GL00100.ACCATNUM = GL00102.ACCATNUM
                      INNER JOIN GL30000 AS GL30000_1 ON AAG40000.JRNENTRY = GL30000.JRNENTRY
--where GL30000.DSCRIPTN <> ''
GROUP BY AAG40001.aaGLDistID, AAG40000.JRNENTRY, AAG40001.DEBITAMT, AAG40001.CRDTAMNT, GL00105.ACTNUMST,
                      GL00100.ACTDESCR, AAG00401.aaTrxDimCode, AAG00401.aaTrxDimCodeDescr, AAG40000.GLPOSTDT, GL00102.ACCATDSC, GL00100.ACTINDX, GL00100.ACCTTYPE, GL00100.ACTIVE, GL00100.PSTNGTYP, GL00100.TPCLBLNC
LVL 1
TBSupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Without digging into your T-SQL holy cats..), use CREATE INDEX with an INCLUDE(

Replace the below with the correct names, and column1, column2 you'll want to add the columns that participate in the JOIN, and column3, column4 would be the columns involved in a SELECT.  

CREATE INDEX ix_give_me_a_name
    ON table_name (column1, column2)
    INCLUDE (column3, column4)
GO

Open in new window


Keep in mind that indexes add to the overhead of inserting-updating-deleting rows, which will slow down those queries.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve WalesSenior Database AdministratorCommented:
You also asked about the difference between a CLUSTERED and NON CLUSTERED index.

The documentation describes is nicely: http://technet.microsoft.com/en-us/library/ms190457.aspx

However, in brief, a clustered index is the physical ordering of the rows on the disk.  When you fetch data on the clustered index you're actually fetching the physical row.

Non Clustered indexes are stored as a tree of pointers to the physical row location.

There can only be one clustered index per table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.