Can i use Indexed view in sql sever to fetch only non duplicate records using indexed views
0
We have a huge table with millions of records. We want to fetch only first record based on some key in that table. When I tried using self-join it's resulting in temp db space issue. From the performance point of view, I was thinking of using an indexed view. but I cant use left join or CTE in an indexed view. Is there any way available to fetch only non duplicate records using indexed views?
sql-server duplicates indexed-view asked Nov 12 at 10:08
Icarus
176111
|
show 1 more comment
0
We have a huge table with millions of records. We want to fetch only first record based on some key in that table. When I tried using self-join it's resulting in temp db space issue. From the performance point of view, I was thinking of using an indexed view. but I cant use left join or CTE in an indexed view. Is there any way available to fetch only non duplicate records using indexed views?
sql-server duplicates indexed-view asked Nov 12 at 10:08
Icarus
176111
1
It's been a while so i'm unsure if you can, but have you looked into using windowing functions within the indexed view?
– Rich Benner
Nov 12 at 10:10
That's a very good point - try using a windowing function instead of a self join. Indexes views are usually no solution for anything.
– Nick.McDermaid
Nov 12 at 10:59
We have a view which is used in more than 50 places so I was thinking of achieving the desired result with minimum impact.
– Icarus
Nov 12 at 11:53
@RichBenner I tried rank function CTE but the indexed view is not allowing them. maybe I need to drop that views and create a table with the same name ;) underline table updated once daily.
– Icarus
Nov 12 at 11:55
No, you won't be able to use a CTE but you should be able to use a window function usingROW_NUMBER()in your select. red-gate.com/simple-talk/sql/t-sql-programming/…
– Rich Benner
Nov 12 at 12:12
|
show 1 more comment
0
0
0
We have a huge table with millions of records. We want to fetch only first record based on some key in that table. When I tried using self-join it's resulting in temp db space issue. From the performance point of view, I was thinking of using an indexed view. but I cant use left join or CTE in an indexed view. Is there any way available to fetch only non duplicate records using indexed views?
sql-server duplicates indexed-view asked Nov 12 at 10:08
Icarus
176111
We have a huge table with millions of records. We want to fetch only first record based on some key in that table. When I tried using self-join it's resulting in temp db space issue. From the performance point of view, I was thinking of using an indexed view. but I cant use left join or CTE in an indexed view. Is there any way available to fetch only non duplicate records using indexed views?
sql-server duplicates indexed-view sql-server duplicates indexed-view asked Nov 12 at 10:08
Icarus
176111
asked Nov 12 at 10:08
Icarus
176111
asked Nov 12 at 10:08
Icarus
176111
asked Nov 12 at 10:08
Icarus
176111
asked Nov 12 at 10:08
Icarus
176111
176111
1
It's been a while so i'm unsure if you can, but have you looked into using windowing functions within the indexed view?
– Rich Benner
Nov 12 at 10:10
That's a very good point - try using a windowing function instead of a self join. Indexes views are usually no solution for anything.
– Nick.McDermaid
Nov 12 at 10:59
We have a view which is used in more than 50 places so I was thinking of achieving the desired result with minimum impact.
– Icarus
Nov 12 at 11:53
@RichBenner I tried rank function CTE but the indexed view is not allowing them. maybe I need to drop that views and create a table with the same name ;) underline table updated once daily.
– Icarus
Nov 12 at 11:55
No, you won't be able to use a CTE but you should be able to use a window function usingROW_NUMBER()in your select. red-gate.com/simple-talk/sql/t-sql-programming/…
– Rich Benner
Nov 12 at 12:12
|
show 1 more comment
1
It's been a while so i'm unsure if you can, but have you looked into using windowing functions within the indexed view?
– Rich Benner
Nov 12 at 10:10
That's a very good point - try using a windowing function instead of a self join. Indexes views are usually no solution for anything.
– Nick.McDermaid
Nov 12 at 10:59
We have a view which is used in more than 50 places so I was thinking of achieving the desired result with minimum impact.
– Icarus
Nov 12 at 11:53
@RichBenner I tried rank function CTE but the indexed view is not allowing them. maybe I need to drop that views and create a table with the same name ;) underline table updated once daily.
– Icarus
Nov 12 at 11:55
No, you won't be able to use a CTE but you should be able to use a window function usingROW_NUMBER()in your select. red-gate.com/simple-talk/sql/t-sql-programming/…
– Rich Benner
Nov 12 at 12:12
1
1
It's been a while so i'm unsure if you can, but have you looked into using windowing functions within the indexed view?
– Rich Benner
Nov 12 at 10:10
It's been a while so i'm unsure if you can, but have you looked into using windowing functions within the indexed view?
– Rich Benner
Nov 12 at 10:10
That's a very good point - try using a windowing function instead of a self join. Indexes views are usually no solution for anything.
– Nick.McDermaid
Nov 12 at 10:59
That's a very good point - try using a windowing function instead of a self join. Indexes views are usually no solution for anything.
– Nick.McDermaid
Nov 12 at 10:59
We have a view which is used in more than 50 places so I was thinking of achieving the desired result with minimum impact.
– Icarus
Nov 12 at 11:53
We have a view which is used in more than 50 places so I was thinking of achieving the desired result with minimum impact.
– Icarus
Nov 12 at 11:53
@RichBenner I tried rank function CTE but the indexed view is not allowing them. maybe I need to drop that views and create a table with the same name ;) underline table updated once daily.
– Icarus
Nov 12 at 11:55
@RichBenner I tried rank function CTE but the indexed view is not allowing them. maybe I need to drop that views and create a table with the same name ;) underline table updated once daily.
– Icarus
Nov 12 at 11:55
No, you won't be able to use a CTE but you should be able to use a window function using
ROW_NUMBER() in your select. red-gate.com/simple-talk/sql/t-sql-programming/…– Rich Benner
Nov 12 at 12:12
No, you won't be able to use a CTE but you should be able to use a window function using
ROW_NUMBER() in your select. red-gate.com/simple-talk/sql/t-sql-programming/…– Rich Benner
Nov 12 at 12:12
|
show 1 more comment
1 Answer
1
active
oldest
votes
0
The scenario is that an indexed view contains distinct values of the "key" that are joined to the first row on the source table.
Prepare example data
SET NOCOUNT ON;
CREATE TABLE dbo.t (
product_name nvarchar(20),
vendor_name nvarchar(20)
)
GO
INSERT INTO dbo.t (product_name, vendor_name) VALUES
('SQL Server', 'Microsoft'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('Oracle', 'Oracle'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('DB2', 'IBM'),
('DB2', 'IBM');
Create indexed view
IF OBJECT_ID ('dbo.v_t', 'view') IS NOT NULL
DROP VIEW dbo.v_t
GO
CREATE VIEW dbo.v_t
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) prod_count, vendor_name
FROM dbo.t
GROUP BY vendor_name
GO
CREATE UNIQUE CLUSTERED INDEX IX1_v_t
ON dbo.v_t (vendor_name);
GO
The query
SELECT t.*
FROM dbo.v_t v
CROSS APPLY(SELECT TOP 1 *
FROM dbo.t t
WHERE v.vendor_name = t.vendor_name
ORDER BY t.product_name) t
Result
product_name vendor_name
-------------------- --------------------
DB2 IBM
SQL Server Microsoft
Oracle Oracle
answered Nov 12 at 12:39
serge
59537
if i change insert statement to INSERT INTO dbo.TBD_TEST (product_name, vendor_name) VALUES ('SQL Server', 'Microsoft'), ('Oracle', 'Oracle'), ('DB2', 'IBM1'), ('Oracle', 'Oracle'), ('Oracle', 'Oracle'), ('DB2', 'IBM2'), ('DB2', 'IBM3'), ('DB2', 'IBM4'); i need only IBM1 and discard other without changing the way i use view in stored procedures
– Icarus
Nov 12 at 13:45
@Icarus it's a test data example to process and discard unused rows later
– serge
Nov 12 at 14:13
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53259889%2fcan-i-use-indexed-view-in-sql-sever-to-fetch-only-non-duplicate-records-using-in%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
The scenario is that an indexed view contains distinct values of the "key" that are joined to the first row on the source table.
Prepare example data
SET NOCOUNT ON;
CREATE TABLE dbo.t (
product_name nvarchar(20),
vendor_name nvarchar(20)
)
GO
INSERT INTO dbo.t (product_name, vendor_name) VALUES
('SQL Server', 'Microsoft'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('Oracle', 'Oracle'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('DB2', 'IBM'),
('DB2', 'IBM');
Create indexed view
IF OBJECT_ID ('dbo.v_t', 'view') IS NOT NULL
DROP VIEW dbo.v_t
GO
CREATE VIEW dbo.v_t
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) prod_count, vendor_name
FROM dbo.t
GROUP BY vendor_name
GO
CREATE UNIQUE CLUSTERED INDEX IX1_v_t
ON dbo.v_t (vendor_name);
GO
The query
SELECT t.*
FROM dbo.v_t v
CROSS APPLY(SELECT TOP 1 *
FROM dbo.t t
WHERE v.vendor_name = t.vendor_name
ORDER BY t.product_name) t
Result
product_name vendor_name
-------------------- --------------------
DB2 IBM
SQL Server Microsoft
Oracle Oracle
answered Nov 12 at 12:39
serge
59537
if i change insert statement to INSERT INTO dbo.TBD_TEST (product_name, vendor_name) VALUES ('SQL Server', 'Microsoft'), ('Oracle', 'Oracle'), ('DB2', 'IBM1'), ('Oracle', 'Oracle'), ('Oracle', 'Oracle'), ('DB2', 'IBM2'), ('DB2', 'IBM3'), ('DB2', 'IBM4'); i need only IBM1 and discard other without changing the way i use view in stored procedures
– Icarus
Nov 12 at 13:45
@Icarus it's a test data example to process and discard unused rows later
– serge
Nov 12 at 14:13
add a comment |
0
The scenario is that an indexed view contains distinct values of the "key" that are joined to the first row on the source table.
Prepare example data
SET NOCOUNT ON;
CREATE TABLE dbo.t (
product_name nvarchar(20),
vendor_name nvarchar(20)
)
GO
INSERT INTO dbo.t (product_name, vendor_name) VALUES
('SQL Server', 'Microsoft'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('Oracle', 'Oracle'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('DB2', 'IBM'),
('DB2', 'IBM');
Create indexed view
IF OBJECT_ID ('dbo.v_t', 'view') IS NOT NULL
DROP VIEW dbo.v_t
GO
CREATE VIEW dbo.v_t
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) prod_count, vendor_name
FROM dbo.t
GROUP BY vendor_name
GO
CREATE UNIQUE CLUSTERED INDEX IX1_v_t
ON dbo.v_t (vendor_name);
GO
The query
SELECT t.*
FROM dbo.v_t v
CROSS APPLY(SELECT TOP 1 *
FROM dbo.t t
WHERE v.vendor_name = t.vendor_name
ORDER BY t.product_name) t
Result
product_name vendor_name
-------------------- --------------------
DB2 IBM
SQL Server Microsoft
Oracle Oracle
answered Nov 12 at 12:39
serge
59537
if i change insert statement to INSERT INTO dbo.TBD_TEST (product_name, vendor_name) VALUES ('SQL Server', 'Microsoft'), ('Oracle', 'Oracle'), ('DB2', 'IBM1'), ('Oracle', 'Oracle'), ('Oracle', 'Oracle'), ('DB2', 'IBM2'), ('DB2', 'IBM3'), ('DB2', 'IBM4'); i need only IBM1 and discard other without changing the way i use view in stored procedures
– Icarus
Nov 12 at 13:45
@Icarus it's a test data example to process and discard unused rows later
– serge
Nov 12 at 14:13
add a comment |
0
0
0
The scenario is that an indexed view contains distinct values of the "key" that are joined to the first row on the source table.
Prepare example data
SET NOCOUNT ON;
CREATE TABLE dbo.t (
product_name nvarchar(20),
vendor_name nvarchar(20)
)
GO
INSERT INTO dbo.t (product_name, vendor_name) VALUES
('SQL Server', 'Microsoft'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('Oracle', 'Oracle'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('DB2', 'IBM'),
('DB2', 'IBM');
Create indexed view
IF OBJECT_ID ('dbo.v_t', 'view') IS NOT NULL
DROP VIEW dbo.v_t
GO
CREATE VIEW dbo.v_t
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) prod_count, vendor_name
FROM dbo.t
GROUP BY vendor_name
GO
CREATE UNIQUE CLUSTERED INDEX IX1_v_t
ON dbo.v_t (vendor_name);
GO
The query
SELECT t.*
FROM dbo.v_t v
CROSS APPLY(SELECT TOP 1 *
FROM dbo.t t
WHERE v.vendor_name = t.vendor_name
ORDER BY t.product_name) t
Result
product_name vendor_name
-------------------- --------------------
DB2 IBM
SQL Server Microsoft
Oracle Oracle
answered Nov 12 at 12:39
serge
59537
The scenario is that an indexed view contains distinct values of the "key" that are joined to the first row on the source table.
Prepare example data
SET NOCOUNT ON;
CREATE TABLE dbo.t (
product_name nvarchar(20),
vendor_name nvarchar(20)
)
GO
INSERT INTO dbo.t (product_name, vendor_name) VALUES
('SQL Server', 'Microsoft'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('Oracle', 'Oracle'),
('Oracle', 'Oracle'),
('DB2', 'IBM'),
('DB2', 'IBM'),
('DB2', 'IBM');
Create indexed view
IF OBJECT_ID ('dbo.v_t', 'view') IS NOT NULL
DROP VIEW dbo.v_t
GO
CREATE VIEW dbo.v_t
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) prod_count, vendor_name
FROM dbo.t
GROUP BY vendor_name
GO
CREATE UNIQUE CLUSTERED INDEX IX1_v_t
ON dbo.v_t (vendor_name);
GO
The query
SELECT t.*
FROM dbo.v_t v
CROSS APPLY(SELECT TOP 1 *
FROM dbo.t t
WHERE v.vendor_name = t.vendor_name
ORDER BY t.product_name) t
Result
product_name vendor_name
-------------------- --------------------
DB2 IBM
SQL Server Microsoft
Oracle Oracle
answered Nov 12 at 12:39
serge
59537
answered Nov 12 at 12:39
serge
59537
answered Nov 12 at 12:39
serge
59537
answered Nov 12 at 12:39
serge
59537
59537
if i change insert statement to INSERT INTO dbo.TBD_TEST (product_name, vendor_name) VALUES ('SQL Server', 'Microsoft'), ('Oracle', 'Oracle'), ('DB2', 'IBM1'), ('Oracle', 'Oracle'), ('Oracle', 'Oracle'), ('DB2', 'IBM2'), ('DB2', 'IBM3'), ('DB2', 'IBM4'); i need only IBM1 and discard other without changing the way i use view in stored procedures
– Icarus
Nov 12 at 13:45
@Icarus it's a test data example to process and discard unused rows later
– serge
Nov 12 at 14:13
add a comment |
if i change insert statement to INSERT INTO dbo.TBD_TEST (product_name, vendor_name) VALUES ('SQL Server', 'Microsoft'), ('Oracle', 'Oracle'), ('DB2', 'IBM1'), ('Oracle', 'Oracle'), ('Oracle', 'Oracle'), ('DB2', 'IBM2'), ('DB2', 'IBM3'), ('DB2', 'IBM4'); i need only IBM1 and discard other without changing the way i use view in stored procedures
– Icarus
Nov 12 at 13:45
@Icarus it's a test data example to process and discard unused rows later
– serge
Nov 12 at 14:13
if i change insert statement to INSERT INTO dbo.TBD_TEST (product_name, vendor_name) VALUES ('SQL Server', 'Microsoft'), ('Oracle', 'Oracle'), ('DB2', 'IBM1'), ('Oracle', 'Oracle'), ('Oracle', 'Oracle'), ('DB2', 'IBM2'), ('DB2', 'IBM3'), ('DB2', 'IBM4'); i need only IBM1 and discard other without changing the way i use view in stored procedures
– Icarus
Nov 12 at 13:45
if i change insert statement to INSERT INTO dbo.TBD_TEST (product_name, vendor_name) VALUES ('SQL Server', 'Microsoft'), ('Oracle', 'Oracle'), ('DB2', 'IBM1'), ('Oracle', 'Oracle'), ('Oracle', 'Oracle'), ('DB2', 'IBM2'), ('DB2', 'IBM3'), ('DB2', 'IBM4'); i need only IBM1 and discard other without changing the way i use view in stored procedures
– Icarus
Nov 12 at 13:45
@Icarus it's a test data example to process and discard unused rows later
– serge
Nov 12 at 14:13
@Icarus it's a test data example to process and discard unused rows later
– serge
Nov 12 at 14:13
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53259889%2fcan-i-use-indexed-view-in-sql-sever-to-fetch-only-non-duplicate-records-using-in%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
It's been a while so i'm unsure if you can, but have you looked into using windowing functions within the indexed view?
– Rich Benner
Nov 12 at 10:10
That's a very good point - try using a windowing function instead of a self join. Indexes views are usually no solution for anything.
– Nick.McDermaid
Nov 12 at 10:59
We have a view which is used in more than 50 places so I was thinking of achieving the desired result with minimum impact.
– Icarus
Nov 12 at 11:53
@RichBenner I tried rank function CTE but the indexed view is not allowing them. maybe I need to drop that views and create a table with the same name ;) underline table updated once daily.
– Icarus
Nov 12 at 11:55
No, you won't be able to use a CTE but you should be able to use a window function using
ROW_NUMBER()in your select. red-gate.com/simple-talk/sql/t-sql-programming/…– Rich Benner
Nov 12 at 12:12