• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

Views and Indexs

What are views and indexes for?

How do i create them?

Any examples in which scenarios can i use them?
0
jedistar
Asked:
jedistar
  • 4
  • 4
  • 2
  • +1
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
refer books online for  these statements

CREATE VIEW
CREATE INDEX

you will get a detailed explanation and some examples

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
indexes are like indexes in books: to find back some information based on key information.

views are basically to give a new "view" of the data, filtering, aggregating etc.

short code version:
CREATE INDEX <indexname> ON <tablename> ( <colums> )

CREATE VIEW <viewname> AS <SELECT STATEMENT>
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Note that indexes are VITAL to get high-performance queries.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
ptjcbCommented:
Views are a virtual table. You can combine selected columns from several tables into a view for security (if a department should not have access to a base table but needs to see some of the data you can create a view of just what they need) or for reports.

Indexes allow queries to (maybe) run faster. It depends on several factors (selectivity of the columns being one of the most important). Think of a book, if you want to know something you can flip to the index at the back and it will send you the right pages. Selectivity is how many different values a column has - for example a bit column would be a poor choice for an index because you would have 50% chance of either yes or no. A unique column would be a much better choice.
0
 
jedistarAuthor Commented:
I don't have to convince you this isn't.

Clearly the question has no 'soundings' as a HOMEWORK qn.

The question is based on to hear experts view on INDEX/VIEWS
and how they use them.

If its homework, isn't googling them and reading them off definitions and Wikipedia better?

I thought this is the place to hear different views of experts and not the absolute correct answer.
0
 
jedistarAuthor Commented:
Experts:

Thanks for the answer.

I have a few stored procs that is queried very often, how do i create an index
on them so the query is faster..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I have a few stored procs that is queried very often, how do i create an index on them so the query is faster..

well, this depends on the query's WHERE and JOIN clauses to start with.

select * from table where field1 = <value>

the index should be
CREATE INDEX idx_table_field1 ON table ( field1 )
0
 
jedistarAuthor Commented:
thanks do i type this in sql query analyser?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
yes, you need to type this in Query analyzer.

Remember to select appropriate database before running the Query
0
 
jedistarAuthor Commented:
How do view our current indexes?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
In Enterprise Manager, right-click the table, and choose All Tasks->manage indexes
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now