parameterized viewc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

parameterized viewc

Author

Topic

Teachme
Starting Member

45 Posts

Posted - 2006-10-04 : 15:04:54

can someone plz explain to me wats a parameterized view or may be a link somewhere on the web or a few examples of parameterized view. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-04 : 15:08:22

You can read about them in SQL Server Books Online. They are used on very large tables where performance is a problem.

Tara Kizer

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-04 : 17:44:24

quote:
You can read about them in SQL Server Books Online. They are used on very large tables where performance is a problem.

Tara Kizer

I think you're referring here to partitioned views?

A parameterized view is a view that can take parameters, and in SQL Server is implemented by creating an inline table-valued function.

So for example if you created a regular view on the Customers table like this

CREATE VIEW v_Customer
AS
SELECT CustomerId, CompanyName FROM Customers

You cannot make this return only a single selected customer. You have to execute it with a WHERE clause like this to get a single customer

SELECT * FROM v_Customer WHERE CustomerId = 'ALFKI'

but if you execute it without a WHERE clause you get all customers.

Now if you rather create a parameterized view (an inline table-valued function), you would do this

CREATE FUNCTION fn_Customer (@CustID char(5))
RETURNS TABLE
AS
RETURN
SELECT CustomerId, CompanyName FROM Customers 
WHERE CustomerId = @CustID

and you'd use it like this

SELECT * FROM fn_Customer('ALFKI')

Note that you cannot execute this in a way that returns more than one customer, it always uses the parameter and does not require the caller to use a WHERE clause.

Teachme
Starting Member

45 Posts

Posted - 2006-10-05 : 11:05:42

Thanks for all ur help

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-06 : 03:47:36

I've never seen sn's option before but I guess you could call it a "parameterized view" even though it doesn't involve a view and there is no such term in sql server.

A partitioned view is, like Tara said, a view used on large tables for performance reasons. Imagine a table with 100 mill records. Instead of having all records in the same physical table you partition them by some logical conditions into 10 tables with 10 mill in each, and then you query a view like this instead:

CREATE VIEW myPartitionedView
AS

SELECT * FROM sales1
UNION ALL
SELECT * FROM sales2
...
UNION ALL
SELECT * FROM sales10

This will give you increased performance when reading the table because sql server only has to search one 10th of the table instead of everything (given that the table is partitioned correctly.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources