?
Solved

T-SQL:  How to Add an Index

Posted on 2014-02-28
2
Medium Priority
?
1,094 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 22

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

762 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