?
Solved

T-SQL:  How to Add an Index

Posted on 2014-02-28
2
Medium Priority
?
1,150 Views
Last Modified: 2014-03-01
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
0
Comment
Question by:TBSupport
2 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39894751
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
 
LVL 23

Expert Comment

by:Steve Wales
ID: 39895176
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question