A CREATE VIEW statement that uses TOP and ORDER BY clauses : Create View « View « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. View
  3. Create View
4>
5>
6> create table Billings (
7>     BankerID           INTEGER,
8>     BillingNumber      INTEGER,
9>     BillingDate        datetime,
10>     BillingTotal       INTEGER,
11>     TermsID            INTEGER,
12>     BillingDueDate     datetime ,
13>     PaymentTotal       INTEGER,
14>     CreditTotal        INTEGER
15>
16> );
17> GO
1>
2> INSERT INTO Billings VALUES (1, 1, '2005-01-22', 165, 1,'2005-04-22',123,321);
3> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, '2001-02-21', 165, 1,'2002-02-22',123,321.);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, '2003-05-02', 165, 1,'2005-04-12',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, '1999-03-12', 165, 1,'2005-04-18',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, '2000-04-23', 165, 1,'2005-04-17',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, '2001-06-14', 165, 1,'2005-04-18',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, '2002-07-15', 165, 1,'2005-04-19',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, '2003-08-16', 165, 1,'2005-04-20',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, '2004-09-17', 165, 1,'2005-04-21',123,321);
2> GO

(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, '2005-10-18', 165, 1,'2005-04-22',123,321);
2> GO

(1 rows affected)
1>
2> CREATE VIEW TopBankers
3> AS
4> SELECT TOP 5 PERCENT BankerID, BillingTotal
5> FROM Billings
6> ORDER BY BillingTotal DESC
7> GO
1>
2> drop view TopBankers;
3> GO
1>
2> drop table Billings;
3> GO








16.2.Create View
16.2.1.The syntax of the CREATE VIEW statement
16.2.2.A view is a SELECT statement that's stored with the database.
16.2.3.WITH SCHEMABINDING clause protects a view by binding it to the database structure, or schema.
16.2.4.Views have a couple of limitations.
16.2.5.Create a view based on correlated subquery and then query it
16.2.6.Encrypting aView
16.2.7.A CREATE VIEW statement that uses TOP and ORDER BY clauses
16.2.8.Creating an Indexed View (CLUSTERED, NONCLUSTERED)
16.2.9.Using the ORDER BY Clause in a View with TOP 100 PERCENT
16.2.10.Supplying Column Names in the Header of the View