July 27, 2017 at 12:22 pm
OK, I feel like a dummy because I thought I knew\understood this but I am putting together a very basic presentation on indexing and I have come across something that is confusing me. First, keep in mind that this is just being setup for demonstration and all of the data is made up through a process that generates random data. I think part of my problem is that I have just been looking at all of this for so long I am starting to confuse myself. I apologize for the length but if anyone can help me understand my confusion, I appreciate it.
EDIT: VERY IMPORTANT: My actual table has 1 million rows. The sample data here is just to give you an idea of what is in it. (Thank you Jacob Wilkins for helping me realize I left this tidbit out of my original post.)
Here is some setup information:
I have a table setup with the following code:SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Phone_Book_Phone_Nbr_PK_CL]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Phone_Book_Phone_Nbr_PK_CL](
[Phone_Nbr] [varchar](14) NOT NULL,
[Last_Name] [varchar](50) NOT NULL,
[First_Name] [varchar](50) NOT NULL,
[Street_Address] [varchar](100) NULL,
[City] [varchar](50) NULL,
[State] [char](2) NULL,
[Zip_Code] [varchar](5) NULL,
CONSTRAINT [PK_Phone_Book_Phone_Nbr_PK_CL] PRIMARY KEY CLUSTERED
(
[Phone_Nbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [ix_nc_LastName_FirstName] Script Date: 7/27/2017 11:21:11 AM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Phone_Book_Phone_Nbr_PK_CL]') AND name = N'ix_nc_LastName_FirstName')
CREATE NONCLUSTERED INDEX [ix_nc_LastName_FirstName] ON [dbo].[Phone_Book_Phone_Nbr_PK_CL]
(
[Last_Name] ASC,
[First_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [ix_nc_State_City] Script Date: 7/27/2017 11:21:11 AM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Phone_Book_Phone_Nbr_PK_CL]') AND name = N'ix_nc_State_City')
CREATE NONCLUSTERED INDEX [ix_nc_State_City] ON [dbo].[Phone_Book_Phone_Nbr_PK_CL]
(
[State] ASC,
[City] ASC
)
INCLUDE ( [Last_Name],
[First_Name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Use the following to load some sample data:
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(780) 024-8729', 'Newton', 'Hammett', '3373 Wedgewood Ave', 'Madison', 'WY', '96646')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(712) 234-8909', 'Lundeen', 'Jarrod', '1821 Water St', 'Coos Bay', 'OR', '09802')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(835) 951-1867', 'Clarke', 'Alfonso', '823 Kedzie St', 'Greenfield', 'HI', '04712')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(950) 841-1576', 'Douglas', 'Troy', '3270 Buck St', 'Moorhead', 'NV', '10303')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(667) 827-5974', 'Carrillo', 'Elmo', '3034 Coronado Road', 'Niagara Falls', 'FL', '61048')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(302) 994-9096', 'Chaney', 'Alfonso', '3795 Whitefish Way', 'Ada', 'AZ', '63327')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(345) 490-2308', 'Gibbs', 'Lionel', '7694 Beech St', 'Sunnyvale', 'OH', '35729')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(299) 659-2087', 'Hughey', 'Donna', '8035 Pioneer Trail', 'Sandy', 'VA', '18720')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(682) 817-8935', 'Douglas', 'Ciaran', '920 Century Oaks Way', 'Sandpoint', 'AZ', '36660')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(128) 152-4750', 'Dejesus', 'Lewis', '1569 State St', 'New Rochelle', 'AZ', '12707')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(372) 988-9797', 'Barr', 'Hasad', '1249 River St', 'Hoboken', 'NV', '61048')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(703) 323-5883', 'Newton', 'Dolan', '7207 Hammond St', 'Sandpoint', 'DC', '88519')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(139) 724-7358', 'Gates', 'Arsenio', '4914 Orchard St', 'Warner Robins', 'VA', '78271')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(251) 946-1493', 'Mcclain', 'Carol', '4007 Mountain View Lane', 'Anderson', 'GA', '20913')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(641) 980-1780', 'LaPine', 'Lisa', '9864 Red Cedar Road', 'Phoenix', 'SD', '89257')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(292) 264-5342', 'Bazinaw', 'Lionel', '1677 Woodland Valley', 'East Hartford', 'SC', '66851')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(828) 520-5064', 'Rowland', 'Ethan', '7691 Mt Hope Ave', 'Decatur', 'TN', '49976')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(757) 024-4509', 'Rowland', 'Chancellor', '1670 Hammond St', 'Louisville', 'AZ', '01218')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(611) 295-6664', 'Duncan', 'Dolan', '7050 Shasta Way', 'Manitowoc', 'WY', '61248')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(494) 013-2945', 'Montgomery', 'Betty', '4819 Michigan Avenue', 'San Dimas', 'MD', '25090')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(147) 075-5178', 'Rowland', 'Brandon', '9452 Ann St', 'Manassas', 'NJ', '13060')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(142) 793-8824', 'Hughey', 'Ethan', '7114 Whitefish Way', 'Bowie', 'NM', '39190')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(514) 959-6963', 'Lundeen', 'Kennedy', '9948 Wilson Road', 'Lodi', 'SC', '22881')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(713) 170-7131', 'Coffey', 'Quinn', '5065 West County Road D', 'Florence', 'DC', '12707')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(666) 307-5294', 'Anderson', 'Mark', '161 Charles St', 'Leominster', 'WV', '31302')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(815) 855-5092', 'Good', 'Stephanie', '271 Beech St', 'Hoboken', 'NC', '98882')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(325) 157-5362', 'Warner', 'Chad', '9658 Bailey St', 'Hartford', 'CO', '42990')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(378) 806-0957', 'Wright', 'Ethan', '2289 Orchard St', 'Tupelo', 'AZ', '38459')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(168) 756-3015', 'LaPine', 'Kyle', '4870 Anderson Lakes Parkway', 'Leominster', 'NY', '05877')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(799) 028-7862', 'Vance', 'Quinn', '8026 Water St', 'Hanahan', 'WV', '69840')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(320) 849-6882', 'St. Onge', 'Richard', '4864 P.O. Box', 'Peabody', 'NE', '52616')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(926) 492-2738', 'Coleman', 'Dolan', '1291 Annie St. W', 'Florence', 'SD', '90540')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(619) 692-8468', 'Dingman', 'Donovan', '4751 Harvard St', 'Oro Valley', 'KY', '47230')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(705) 564-8594', 'Gibbs', 'Quinn', '3727 South Hwy 101', 'Moorhead', 'ID', '47379')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(927) 347-4185', 'Nielsen', 'Ethan', '805 Yale St', 'Rockford', 'SD', '25090')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(906) 358-7705', 'Mercado', 'Kathy', '1312 Thompson Ln', 'Boulder Junction', 'NE', '09323')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(289) 918-1400', 'Espinoza', 'Jennifer', '6275 P.O. Box', 'Morgan City', 'NC', '62009')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(511) 021-1656', 'Summerfield', 'Troy', '1510 Thousand Oaks Dr', 'Durham', 'NM', '43546')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(299) 818-1812', 'Chaney', 'Robert', '2635 Shasta Way', 'Gainesville', 'NM', '52010')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(394) 588-8444', 'Wright', 'Kennedy', '122 Broadland Cove', 'Bangor', 'PA', '03487')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(756) 923-1373', 'Black', 'Richard', '2975 Truscott St.', 'New Iberia', 'KS', '68145')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(244) 668-7824', 'Mcdowell', 'Kane', '8089 France Ave', 'Maywood', 'WV', '92104')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(272) 910-5968', 'Rose', 'Carla', '4390 West County Road D', 'Bloomington', 'KY', '52010')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(697) 165-7112', 'Watts', 'Lewis', '366 Shasta Way', 'Eden Prairie', 'KY', '96646')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(231) 775-1863', 'Chaney', 'Carla', '9435 Yale St', 'Decatur', 'CO', '39148')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(156) 954-9098', 'Nielsen', 'Mitch', '4194 Century Meadow Ct', 'Bell', 'NV', '96646')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(433) 838-1521', 'Rice', 'Davis', '5736 Truscott St.', 'Beaver Falls', 'VT', '92554')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(295) 851-6387', 'Mccoy', 'Hiram', '8021 Trowbridge Rd', 'Minot', 'OR', '89257')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(826) 564-3615', 'Wright', 'Gil', '3554 Kedzie St', 'Stamford', 'DC', '50379')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(725) 406-2842', 'Vance', 'Jarrod', '1098 Dormitory Rd', 'Modesto', 'ND', '25592')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(999) 576-5641', 'Dow', 'John', '6188 Bailey St', 'West Hollywood', 'NY', '61863')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(785) 064-0648', 'Delgado', 'Devin', '5401 Watchmaker St', 'Eden Prairie', 'VA', '96646')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(762) 682-3059', 'Warner', 'Bob', '9801 Spyglass Plaza', 'Chester', 'MD', '46719')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(893) 638-1094', 'Mcclain', 'Quinn', '8350 Raymond Heights Road', 'Salisbury', 'SC', '73619')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(942) 926-2821', 'Duncan', 'Tina', '3985 Union St', 'Dover', 'CO', '49757')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(533) 028-9426', 'Malone', 'Chancellor', '1859 Truscott St.', 'Columbia', 'CA', '73619')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(563) 669-9309', 'Wolf', 'Kennedy', '5647 Red Cedar Road', 'Oklahoma City', 'NJ', '20211')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(537) 356-1501', 'Sanchez', 'Gil', '3256 W Buena Vista St', 'Leominster', 'VT', '22881')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(679) 758-1970', 'Rose', 'Ciaran', '4790 Ann St', 'Citrus Heights', 'KY', '38459')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(201) 245-7526', 'Randolph', 'Lisa', '4402 Whitefish Way', 'Yigo', 'OR', '16805')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(389) 659-5094', 'Anderson', 'Donovan', '4053 Buck St', 'Dana Point', 'GA', '09062')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(541) 070-1594', 'Watts', 'Elmo', '6062 Whitefish Way', 'Macon', 'MS', '19961')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(151) 544-3083', 'Montgomery', 'Chancellor', '6338 Wilson Road', 'Pasadena', 'CO', '67114')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(513) 630-5914', 'Bloswick', 'Robert', '4715 Kirkwood Ave', 'Lodi', 'TX', '86049')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(169) 014-0422', 'Nielsen', 'Anna', '7087 Dormitory Rd', 'Florence', 'NJ', '77254')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(630) 825-6713', 'Lane', 'Timothy', '7626 Water St', 'Oxnard', 'DC', '09445')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(762) 150-5193', 'Chaney', 'Lionel', '2586 Beech St', 'Wisconsin Rapids', 'NH', '73262')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(781) 379-1282', 'Guthrie', 'Sherri', '3291 Valley View Road', 'Chattanooga', 'MO', '85771')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(500) 369-9708', 'Gunter', 'Honorato', '900 Albert Ave', 'Hanahan', 'KS', '09445')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(419) 802-8401', 'Montgomery', 'Jennifer', '5855 Mountain View Lane', 'Muskegon', 'NY', '85581')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(592) 657-5776', 'Coleman', 'Avram', '3170 Harvard St', 'Temple City', 'ME', '16576')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(569) 807-4993', 'Knox', 'Alfonso', '2112 William St', 'Georgetown', 'MN', '50124')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(445) 212-5612', 'Vance', 'Todd', '8214 Wilson Road', 'Lafayette', 'NY', '01218')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(765) 111-7969', 'Wolf', 'Gil', '8571 Cedar St', 'Phoenix', 'NC', '96646')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(694) 131-0575', 'Rowland', 'Todd', '8882 Annie St. W', 'West Lafayette', 'NC', '10695')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(224) 870-9012', 'Mercado', 'Kato', '3754 Collingwood Dr', 'Dover', 'NJ', '09802')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(112) 829-2851', 'Montgomery', 'Hammett', '8963 Old Santa Fe Trail', 'Green Bay', 'CO', '83712')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(224) 961-4257', 'Chaney', 'Hiram', '9642 Broadland Cove', 'Ada', 'NC', '31302')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(318) 130-3491', 'Carrillo', 'Chancellor', '9954 Hassinger Road', 'Beaver Falls', 'WV', '77254')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(696) 624-5946', 'Barr', 'Raja', '4665 Red Cedar Road', 'Seal Beach', 'WV', '10303')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(166) 666-1416', 'Wright', 'Jennifer', '2747 Thompson Ln', 'Kingsport', 'WY', '06568')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(289) 524-2176', 'Clarke', 'Troy', '1107 Beech St', 'Waterbury', 'MT', '68375')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(530) 549-5076', 'Rowland', 'Jodi', '7297 Michigan Avenue', 'Hoboken', 'OR', '82110')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(900) 430-8789', 'Coleman', 'Ida', '1665 Broadland Cove', 'Warner Robins', 'PA', '79522')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(531) 619-3244', 'Espinoza', 'Mitch', '4012 Mt Hope Ave', 'Somerville', 'OR', '19961')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(456) 199-0591', 'Knox', 'Hasad', '7783 State St', 'Durham', 'AZ', '73913')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(169) 568-8646', 'Wolf', 'Hiram', '8878 Kirkwood Ave', 'Fernley', 'ID', '76133')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(632) 950-8592', 'Montgomery', 'Honorato', '4681 Webster Ave', 'Dana Point', 'NM', '29689')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(348) 805-5810', 'Clarke', 'Lionel', '1985 Watchmaker St', 'Chico', 'VT', '69840')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(763) 070-1999', 'Montgomery', 'Davis', '1108 Franklin Place', 'Waterbury', 'TN', '21712')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(542) 478-4016', 'Harrison', 'Kassie', '2276 Broadland Cove', 'Fort Worth', 'KS', '26895')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(122) 453-3987', 'Rose', 'Gil', '3948 Snyder Road', 'Fresno', 'VA', '26895')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(535) 307-7318', 'Lane', 'Ciaran', '4191 Collingwood Dr', 'Rockford', 'GA', '49976')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(691) 388-1364', 'Harrison', 'Troy', '2024 Shaw Lane', 'Chula Vista', 'MD', '27397')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(568) 640-6415', 'Mccoy', 'Donovan', '7314 Trinity Place', 'Mesquite', 'MI', '85581')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(238) 014-5672', 'Montgomery', 'Lisa', '340 W Buena Vista St', 'Sioux City', 'NV', '41881')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(807) 427-1219', 'Delgado', 'John', '5567 Broadland Cove', 'Phoenix', 'DE', '49927')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(149) 595-5954', 'Delgado', 'Lance', '1733 Burcham Dr', 'Madison', 'NV', '49295')
INSERT INTO dbo.Phone_Book_Phone_Nbr_PK_CL VALUES('(291) 275-4543', 'Randolph', 'Kato', '9052 Beech St', 'Kingsport', 'NV', '55819')
Now, my misunderstanding is on how SQL Server decides which index to use with various queries and why.
Just as a reminder, away from the CREATE TABLE statement, my indexes are as follows:
Clustered: Phone_Nbr (Primary Key)
Non-Clustered: Last_Name, First_Name (no included columns)
State, City (includes Last_Name, First_Name)
The following are some queries, along with explanations, that are giving me problems:
A: When I run the following query, I get a Clustered Index Seek, as expected: SELECT *
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Phone_Nbr = '(299) 659-2087'
B: When I run the following query, I get a Clustered Index Scan. I believe this is because I am pulling all the data columns but why doesn't it use the NC index with Last_Name and First_Name then do a lookup?SELECT *
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Last_Name = 'Douglas'
ORDER BY Last_Name, First_Name
C: When I run the following two queries, I get an Index Seek on the non-clustered index with Last_Name and First_Name, as expected:SELECT Last_Name, First_Name
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Last_Name = 'Douglas'
ORDER BY Last_Name, First_Name
SELECT Last_Name, First_Name, Phone_Nbr
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Last_Name = 'Douglas'
ORDER BY Last_Name, First_Name
D: When I run the following query, I get an Index Scan on the index with the State and City. This one has me baffled.SELECT Last_Name, First_Name, Phone_Nbr, City, State
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Last_Name = 'Douglas'
ORDER BY Last_Name, First_Name
E: When I run the following query, I get a Clustered Index Scan. Is it because of the data columns being returned? Why not the non-clustered then a lookup?SELECT Last_Name, First_Name, Phone_Nbr, Zip_Code
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Last_Name = 'Douglas'
ORDER BY Last_Name, First_Name
The following queries are the same as above but I use both the Last_Name and the First_Name in the WHERE clause:
F: When I run the following, I get an Index Seek, on the non-clustered Last_Name, First_Name index, with a Key Lookup, as expected:SELECT *
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Last_Name = 'Douglas'
AND First_Name = 'Elmo'
ORDER BY Last_Name, First_Name
G: The following two queries gives me an Index Seek on the non-clustered Last_Name, First_Name index, as expected:SELECT Last_Name, First_Name
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Last_Name = 'Douglas'
AND First_Name = 'Elmo'
ORDER BY Last_Name, First_NameSELECT Last_Name, First_Name, Phone_Nbr
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Last_Name = 'Douglas'
AND First_Name = 'Elmo'
ORDER BY Last_Name, First_Name
H: The following two queries gives me an Index Seek on the non-clustered Last_Name, First_Name index, with a Key Lookup, as expected:SELECT Last_Name, First_Name, Phone_Nbr, City, State
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Last_Name = 'Douglas'
AND First_Name = 'Elmo'
ORDER BY Last_Name, First_NameSELECT Last_Name, First_Name, Phone_Nbr, Zip_Code
FROM dbo.Phone_Book_Phone_Nbr_PK_CL
WHERE Last_Name = 'Douglas'
AND First_Name = 'Elmo'
ORDER BY Last_Name, First_Name
I included a lot of examples that work as I expected, just to give you an idea of what I have been testing with. My questions are specifically on the reasons for B, D, and E. I thought that, when appropriate, SQL Server would still try to use the non-clustered index, with Last_Name as the first column, then, if necessary, use a lookup to get any additional data.
I'm sure I'm going to slap myself when I read the responses but I am having a major brain fart right now.
Again, any help is greatly appreciated.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
July 27, 2017 at 12:34 pm
I'll have to actually run through your example to give exact numbers for this exact data, but I can make a quick general point.
With a plan that uses a lookup, you're talking about at least 1 IO for each row returned from the access of the non-covering index (to perform the lookup).
That gets expensive very quickly as the number of rows ramps up, so that the tipping point at which a scan makes sense instead of seek+lookup is often much lower than you might intuitively guess.
Imagine, for example, that you have 1000 rows, and they fit on 10 pages. That means a scan of the entire primary data structure could be accomplished by reading ten pages.
Now imagine you have a query that returns just 20 of those 1000 rows, but to use a seek you would have to use a non-clustered index and then a lookup. For simplicity's sake, let's say the lookups cost only 1 IO each.
Even with only 2% of the table returned, the plan with seek+lookup will do more than twice as much IO as just scanning the base table, and that's with moderately wide rows. If the rows were more narrow, the difference would be exacerbated.
You can read a nice piece on this at https://www.sqlskills.com/blogs/kimberly/the-tipping-point-query-answers/
Cheers!
EDIT: Cleared up some wording.
July 27, 2017 at 12:41 pm
Thank you Jacob, I definitely got tunnel vision on this one. I am aware of what you are talking about and, now that you say it, it makes perfect sense. I just got myself stuck heading down one road (way of thinking) and I kind of stuck there.
I'm pretty certain this is the case here because, and I failed to mention this in my post so I will go edit it, the actual table I am using has 1 million rows with a lot of duplicates in the non-clustered indexes. With that number, it would make perfect sense.
Thanks for the quick response. That really helped get me back on the right path.
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
July 27, 2017 at 12:43 pm
Think more about the contents of a given index. For D and E, you're getting what you should have expected. SQL Server can get everything it needs from the index it used for D, and for E, it will prefer the CLUSTERED index because it can get ALL of the data there. For B, you're making an unwarranted assumption about a lookup. If you have MAXDOP = 1 going on, there's a performance hit for the lookup that isn't actually needed, and even if MAXDOP is higher than 1, you still are doing a lookup that you don't need to do. Generally, if SQL Server doesn't actually need to do a lookup, it's not going to do it if it represents additional work. There are probably exceptions out there, but the general idea is that SQL Server isn't going to do work it doesn't think it needs to do.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 28, 2017 at 2:28 am
http://www.sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2017 at 9:27 am
The table definition could be adjusted for efficiency.
At a minimum, get rid of the formatting chars in the phone# and make both it and zip char rather than varchar, i.e:
Phone_Nbr char(10)
Phone_Nbr_Format tinyint
...
Zip_Code char(5)
Add the formatting code to allow proper output configuration and/or for lookup, if the format can affect the lookup.
You should also consider encoding people names and even street names (not the address number). That is, use a numeric code instead of a string. Yes, you'll need to lookup the actual names then, but the main table becomes much smaller and faster to search.
CREATE TABLE dbo.Names ( Name_Id int IDENTITY(1, 1), Name varchar(100), CONSTRAINT [PK_Names] PRIMARY KEY CLUSTERED Name ) WITH ( FILLFACTOR = 99, ... ) ON [<filegroup_name>]
CREATE UNIQUE NONCLUSTERED INDEX UQ_Names ON dbo.Names ( Name_Id ) WITH ( FILLFACTOR = 99, ... )
First_Name_Id int NOT NULL CONSTRAINT FK_Names_First_Name REFERENCES dbo.names ( Name ) ...
Last_Name_Id int NOT NULL CONSTRAINT FK_Names_Last_Name REFERENCES dbo.names ( Name ) ...
etc.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply