Solved

T-SQL:  How to Add an Index

Posted on 2014-02-28
2
993 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 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now