Indexing View Definition Table Columns

Sep 29, 2007

Hi experts,


Im very very new to sql server world..wanted to know what kind of indexes to be created on the below mentioned table columns for making this view run fastly.As of now there are no indexes created on these view definition columns


CREATE View hrinu.Parity as
select
T1.Matcle as CorpID,
T2.Nmpres as Name,
T4.DATDEB as LeaveFrom,
T4.TEMDEB as PM,
T4.DATFIN as LeaveTo,
T4.TEMFIN as AM,
T10.LIBLON as LeaveType,
T8.LIBLON as Location,
T12.LIBLON as ParentOrg

from HRINU.zy00 T1,
HRINU.zy3y T2,
HRINU.zy39 T3,
HRINU.zyag T4,
HRINU.zy38 T5,
HRINU.zy1s T6,
HRINU.zd00 T7,
HRINU.zd01 T8,
HRINU.zd00 T9,
HRINU.zd01 T10,
HRINU.zd00 T11,
HRINU.zd01 T12
where T4.Nudoss = T3.nudoss
and T4.Nudoss = T1.Nudoss
and T1.Nudoss = T2.nudoss
and T3.nudoss = T5.nudoss
and T6.nudoss = T1.nudoss
AND T7.NUDOSS = T8.NUDOSS
AND T9.NUDOSS = T10.NUDOSS
AND T11.NUDOSS = T12.NUDOSS
AND T3.IDWKLO = T7.CDCODE
AND T4.MOTIFA = T9.CDCODE
AND T5.IDESTA = T11.CDCODE
and T6.stempl = 'A'
and t7.cdstco = 'z04'
AND T8.CDLANG = 'U'
and t9.cdstco = 'DSJ'
AND T10.CDLANG= 'U'
and t11.cdstco= 'DRE'
AND T12.CDLANG= 'U'
and T4.DATDEB <= T3.DTEN00 and T4.DATFIN >= T3.DTEF00
and T3.DTEN00 <= T5.DTEN00 and T3.DTEN00 >= T5.DTEF00
and T6.dtef1s <= getdate() and T6.datxxx > getdate()


Also Please suggest me some links where i can get info about the indexes that has to be created on these types of queries where joins are involved on these many tables.
Also throw some light on how to analyse the execution plan for further enhancements.



Thanks in advance


Regrds
Arvind L

View 3 Replies


ADVERTISEMENT

View Definition Includes No Output Columns ...

Jan 18, 2007

hello all,

i am trying to create a view but i keep getting the error 'View definition includes no output columns or no items in the FROM clause.'

below is the select statement that's the basis of my view. the explanation i got from the F1 help of enterprise manager was ...
View definition includes no output columns or no items in the FROM clause.
A view definition must have at least one table or table-structured object in the FROM clause, and must have at least one column in the select list. The view definition is missing one or both. Modify the view definition accordingly.


query:

select
Case_CaseId,
Logged,
CAST(DATEDIFF(minute, Logged, Waiting)/60.0 AS NUMERIC(9, 2)) AS Waiting,
CAST(DATEDIFF(minute, Logged, Investigating) /60.0 AS NUMERIC(9, 2)) AS Investigating,
CAST(DATEDIFF(minute, Logged, Rejected) /60.0 AS NUMERIC(9, 2)) AS Rejected,
CAST(DATEDIFF(minute, Logged, Resolved) /60.0 AS NUMERIC(9, 2)) AS Resolved,
CAST(DATEDIFF(minute, Logged, Solved) /60.0 AS NUMERIC(9, 2)) AS Solved,
CAST(DATEDIFF(minute, Logged, Closed) /60.0 AS NUMERIC(9, 2)) AS Closed
from
(

SELECT
Case_CaseId,
MIN(CASE WHEN case_stage = 'Logged' THEN Case_CreatedDate END) AS Logged,
MIN(CASE WHEN case_stage = 'Waiting' THEN Case_CreatedDate END) AS Waiting,
MIN(CASE WHEN case_stage = 'Investigating' THEN Case_CreatedDate END) AS Investigating,

AS Rejected, MIN(CASE WHEN case_stage = 'Resolved' THEN Case_CreatedDate END) AS Resolved,
MIN(CASE WHEN case_stage = 'Solved' THEN Case_CreatedDate END) AS Solved,
MIN(CASE WHEN case_stage = 'Closed' THEN Case_CreatedDate END) AS Closed
FROM
CaseProgress
GROUP BY Case_CaseId
) as temp
order by Case_CaseId

View 2 Replies View Related

How To Get Information From System Table Or View For Trigger's Definition

May 29, 2008

Such as check all triggers that assign value to some columns ?

Thank you very much.

View 1 Replies View Related

DB Engine :: Indexing Strategy For Table With Two Columns In SPs WHERE Clause

Oct 1, 2015

I have an SP which concatenates 2 columns in a where clause - ie WHERE [Column1] + [Column2] = @var.  This, as far as I'm aware, is not going to access any seek on an index on a table.

My task is to create an index on this table and get the SP to access said index.

Aside from combining the two columns into one column on the table, how I can get an SP to access the newly created index when queried by the SP?

One thought of mine is to firstly index Column1 and allow the SP to access this index by inserting the rows into a temp table.  Then a search on the temp table to retrieve the records on a search on Column2.

View 8 Replies View Related

Different Query Plans For View And View Definition Statement

Mar 9, 2006

I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?

View 10 Replies View Related

Matching A View's Columns To It's Underlying Table's Columns

Jul 20, 2005

Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name

View 2 Replies View Related

IF Statement In View Definition

Apr 7, 1999

We are trying to create a view that references lengths in both metres and feet.

What we want to do is to create a baselength column which either holds the value of the metres column or calculates the metric value of the feet column if there is no value in the metric column.

We can do all the maths for the calculations etc, it is just putting the IF statement into the view to test the values that we are struggling with.

Any help would be appreciated.

Thanks

View 2 Replies View Related

View Definition From Schema

Mar 12, 2004

I'm using the following to get table/column names:

select so.name, sc.name
from sysobjects so, syscolumns sc
where so.id = sc.id
and so.type = 'U'
order by so.name, sc.colid

What is the equivalent for views?

Thanks

View 4 Replies View Related

Can You Use Cursors Within A View Definition?

May 13, 2008



I want to create a view on the table "workorder" that contains a serial id "woid". Another table, "wo_comment" contains multiple comments for each "woid".

I would like to create a view view_workorder with a column "allcomments" that is basically a concatenation of all the comments within the table "comment".

Table: workorder has columns: woid, startdate (plus many others)
Table: wo_comment has columns: woid, comment, sequence

Desired View: view_workorder has columns: woid, startdate, allcomments

I tried to create a view as follows but get errors concerning the "CURSOR" statement making me wonder if I can use declarations and CURSORS within a view?


create view [workorder_view] as (
Select
w.woid,
w.startdate,
cast( DECLARE @all_comments nvarchar(4000),
@curr_comment nvarchar(256)
DECLARE wo_comment_cur CURSOR
FOR
SELECT woc.Comments
FROM WO_Comment as woc
WHERE woc.woid = w.woid
OPEN wo_comment_cur
FETCH NEXT FROM wo_comment_cur INTO
@curr_comment
WHILE (@@FETCH_STATUS = 0)
BEGIN
Set @all_comments = @all_comments + @curr_comment
FETCH NEXT FROM wo_comment_cur INTO
@curr_comment
END
CLOSE wo_comment_cur;
DEALLOCATE wo_comment_cur;
as nvarchar(80000)) as COMMENTS

from workorder AS w

Thanks for any help you can provide!

View 4 Replies View Related

SQL 2012 :: View Definition Of Objects?

Sep 8, 2014

I want to create a login with some restriction like the following...

1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only,
2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login.
3.If we Deny the View definition option he can't able to see the Objects which are there under the DB.
4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....

View 3 Replies View Related

SQL 2012 :: Grant View Definition

Nov 19, 2014

I'd like to find out whether or not people grant VIEW DEFINITION to their developers in UAT and PROD environments. My view is that a developer shouldn't be able to touch a PROD environment at all (we also include UAT as PROD), and any issue in a production environment should be investigated by a DBA and escalated to the dev if necessary.

View 1 Replies View Related

COUNT_BIG() In Indexed View Definition.

Apr 1, 2008

Hi,

When I am trying to create an indexed view using group and COUNT(*), it gave me the following error:

€œMsg 10136, Level 16, State 1, Line 2
Cannot create index on view "AdventureWorks.Sales.vOrders" because it uses the aggregate COUNT. Use COUNT_BIG instead.€?

When I refered the SQL Books Online, I found the following statement: €œIf GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING€?.

Though my question is basic, I am curious to know the difference between COUNT() and COUNT_BIG(). The only difference I knew is COUNT_BIG() returns bigint. If this is the only difference, why can€™t we use COUNT() in indexed view definition and why COUNT_BIG() is allowed?


Regards,
-Aazad.

View 5 Replies View Related

SQL 2012 :: Get View Definition Using Modules With Linebreaks

May 6, 2014

SELECT [DEFINITION]
FROM SYS.SQL_MODULES WHERE [OBJECT_ID] = OBJECT_ID(@OBJECTNAME)

I know I can use SP_HELPTEXT to get linebreaks, The reason I want to use this is I can exclude comments section from SQL_Modules, but cannot exclude the comments section from SP_HELPTEXT.

View 1 Replies View Related

View / Underlying Column Definition Metadata

Mar 18, 2008

Have many views based on legacy tables that have different table and column names. Want to create a table that shows view table / column and underlying table column, e.g.

CREATE VIEW [dbo].[Branch]
AS
SELECT

divbra_id BranchID,
cmpny_id CompanyID,
divbra_cde BranchCode,
divbra_nme BranchName

FROM MyDatabase.dbo.divbranc

GO

is an existing view. I want to pull out the following metadata:

divbranc divbra_id Branch BranchID
divbranc cmpny_id Branch CompanyID
divbranc divbra_cde Branch BranchCode
divbranc divbra_nme Branch BranchName

Is there anyway to get this from SQL metadata without actually parsing the view SELECT statement in code?

View 7 Replies View Related

SQL Security :: View Definition Permission On Target Database

May 15, 2015

I am trying to do a schema compare and data compare via VS2012 and I am getting below error: The reverse engineering operation cannot continue because you do not have View Definition permission on the 'Target' database.

Whats interesting is I created a viewdefinition role and added the group(to which the user belongs) to the role. However I dont get the error if I make the group the dbowner. Is this a bug?

View 2 Replies View Related

Creating A View With Columns Not In Another Table

Jul 20, 2005

Hello,I'm somewhat of a newbie and I need to create a view with a columnthat is not derived from any other tables. What I want is for thisfield to be an auto-increment field or some kind of UID. Can anyonequickly tell me how to do this.Here is my dilemma. I need to pull data from a unique data set from atable which does not have a primary key, and none exists in its data.Please tell me how to put this data in a view(or another table) sothat I can pull it out with unique records. It doesnt matter what theunique field consists of.Thanks

View 3 Replies View Related

Indexing A View

Dec 14, 2005

Hi All,I'm hoping someone can help me. I think I'm missing something verybasic. I'm trying to put a clustered index on a view that I havecreated. I keep getting the error:Server: Msg 8668, Level 16, State 1, Line 1An index cannot be created on the view 'cew_avwage_uscnty' because theselect list of the view contains a non-aggregate expression.Here is the create view statement:CREATE view dbo.cew_avwage_uscntywith schemabindingasselect statefips, countyfips, naics_code, sum(disc * 0) as disc,data_year, sum(qtr_payroll)/((sum(emp1+emp2+emp3))/12) as avwagefrom dbo.qcew_own_n where year= '2004'and (ownership = '0'and (naics_code like '__' or naics_code like '__-__')or (ownership = '5' and naics_code = '10'))and countyfips <> '999'and naics_code <> '99'and ((disc = '0') or (disc <> '0' and (emp1 <> 0 or emp2 <> 0 or emp3 <>0)))group by statefips, countyfips, naics_code, data_yearHere is the create index statement I am using:create unique clustered index main_cdxon dbo.cew_avwage_uscnty (statefips, countyfips, naics_code, year)Each field that I have listed in the select statement MUST appear in theview. The disc field needs to be set = 0 and the data_year field needsto be set = 2004. Initially I had select statement for disc anddata_year set as, disc = '0', year = '2004' .. but thought this wasgiving me my problem. I changed it and am still getting the same error?Is my problem because of the fields that I am grouping by? Any advicewould be very much appreciated. I'm trying to avoid creating an extracttable, as these data are updated regularly - so I would like to onlyupdate the base table and have the view refresh itself.Thanks again!Bethany*** Sent via Developersdex http://www.developersdex.com ***

View 6 Replies View Related

SQL Server 2008 :: Granting Explicit View Definition Permissions On Stored Procedure To DBO?

Mar 6, 2013

The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.

While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.

When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.

Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?

View 9 Replies View Related

SQL 2012 :: Clustered Indexing On View

Jun 15, 2015

I've created a table which will hold staffing data (name, grade, etc.) and any shifts that are going to be entered. I've got some test data in the table.

The base table looks like this:-

CREATE TABLE [dbo].[tbl_ForecastShifts_New](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ward] [nvarchar](255) NULL,
[StaffBand] [real] NULL,
[StaffMemberName] [nvarchar](255) NULL,

[Code] ....

I've attached a spreadsheet with the test data from the table and the required layout on the second tab.

I've created a view which displays the data as I want it and I need to attach this to an MS Access front-end for the users to input/edit the data in the table with the use of a Access form.

The view I've created looks like this:-

CREATE view [dbo].[vw_Forecast_Staffing] with schemabinding
as
select
s.Ward
,s.StaffBand
,s.StaffName
,d1.Date1

[Code] ....

It displays all the staff members and any shifts they've been given for a 7-day period with day 1 begin supplied by the user.

The user will also supply the ward they are interested in viewing.

When I attached the view to Access it becomes read-only as it doesn't have any indexes on it.

I can't create a clustered index on the view as there are derived tables.

View 3 Replies View Related

Indexing Columns

Jul 27, 2007



If you have a table with 3 columns,


ID (Primary Key)

Col1

Col2

And you have to perform the following query frequently




Code SnippetSelect ID where Col1='SomeValue' and Col2='SomeOtherValue'





Is it a bad idea to define a non clustered index on "Col1, Col2, ID" or am I better off just having the default indexing on the the primary key "ID"

I have never had to define an index that included all the columns in a table before so I am not sure if this is a bad idea

View 8 Replies View Related

INDEXING A VIEW In MSSQL2K5x64.de Standard W.SP1 ... ERROR 1939

Aug 2, 2006

I'm experiencing a problem while trying to index a view ... I'm using a single field and everything else are default entry's. I'm getting the Error Message 1939 .

The code is a follows:

USE [CS_WGO]

GO

SET ARITHABORT ON

GO

SET CONCAT_NULL_YIELDS_NULL ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

SET ANSI_PADDING ON

GO

SET ANSI_WARNINGS ON

GO

SET NUMERIC_ROUNDABORT OFF

GO

CREATE UNIQUE CLUSTERED INDEX [IDX_ALG_ORG_BEZ_V_1] ON [dbo].[ALG_ORG_BEZ_V]

(

[NDL] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]

GO



And the Error is:

Msg 1939, Level 16, State 1, Line 1

Index kann für die 'ALG_ORG_BEZ_V'-Sicht nicht erstellt werden, da die Sicht nicht schemagebunden ist.



For those of you who do not read German ... it means something like ..." The Index kann not be created because the view is not bound to a schema.

I don't understand what that means for me ... can someone explain that to me a little bit more in depth ... ?

Thanx ... wvg







View 7 Replies View Related

Transact SQL :: Any Reason For Difference In Order Of Columns Between Index Of Constraint And Its Statistics Definition?

Sep 5, 2015

I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.

As per definition,

stats_column_id
int

1-based ordinal within set of stats columns

This script duplicates this for me.

BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,

[Code] ....

The result I get is this:

object_id       stats_name                                     
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID,  cpID,  ipuID,

and

object_id      index_name                                     
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID,  ipuID,  cpID,

Also a query I used to discover this in my db is:

WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc

[Code] .....

View 6 Replies View Related

Full-Text Indexing For Smaller Columns

Nov 1, 2004

Hello,

I'm looking at using full-text indexing for tables to query. I have some smaller fields (varchar(50) that stores names) that I was contemplating using full-text indexing for. I was just curious if it is worth it?

Basically the data that will be there are one-word names, without any spaces or whatnot.

Brian

View 3 Replies View Related

Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns

Jul 24, 2012

Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.

This is what I have so far,

CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID

[code]...

View 2 Replies View Related

Column Name Or Number Of Supplied Values Does Not Match Table Definition When Trying To Populate Temp Table

Jun 6, 2005

Hello,

I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp

INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'

The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?

The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.

Thanks,
Greg.

View 2 Replies View Related

Finding Logins That Have GRANT CONTROL And GRANT VIEW DEFINITION

Jul 21, 2015

Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?

GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda
GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda

View 5 Replies View Related

Altering Table Definition On A Linked Server Table.

Aug 11, 2000

SQL Server 7.0 (SP1)
Error:
------
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.

A column was deleted from the a table on the linked server and now this message appears when using the linked server definition to access the table. Deleting/Recreating the Linked Server has no effect. I found an earlier note on this...but it just kind of ended with no resolution. Anyone have any thoughts on this now.

Thanks for any input.

View 1 Replies View Related

How To Get Table Definition

Mar 6, 2008

Hi,
I need a query to get table Definition in sql server 2005.
please help me out.



Raghu sunkara.

View 6 Replies View Related

Reorder The Table Definition Of A SQL 2005 Table

Aug 9, 2007

Hi,Is it possible to reorder the table definition in a SQL 2005 table?Example : i want to put to table definition in alphabetical order.Thanks in advance

View 8 Replies View Related

Allow User To See Table Definition

May 8, 2008

I have set up a couple of views for a user but they want to be able to see the database table columns by doing right click on table and getting the columsn.

I tried to give the permissions to database by right clicking on database and doing the user and giving view defintion.

-- [TABLEA] contains no columns that can be inserted or the current user does not have permissions on that object.

User not allowed to select the data .

Thanks

View 2 Replies View Related

Table Definition Documentation

Dec 1, 2006

Hosam writes "Dear Team

Im new to SQL server 2005, and I wonder if I can create the database documentation, I mean table schema and attributes as word or excel file.

Is there anyone can help me please

Thanks
Hosam"

View 1 Replies View Related

Check Table And STP Definition ?

Feb 7, 2008

Hi SQL Professionals ,


I have a requirement like this,

I have a Live Database as well as the Test database with the same Definition

now i need to write a SQL to check identity of these two database tables,

i mean i need to check if the Test Database has got the Same Table definition as Live Database table definition ?
In the same way how do i check for the Stored peocedures ?




how do i write a SQL for this ?


regardssuis

View 4 Replies View Related

What Is The True Definition Of Catalog Table?

Jul 23, 2005

We hear about catalog table in documentation but is this the same asdatabase schema?What is the definiation of catalog table?What does it pertain to?Thanks--Message posted via http://www.sqlmonster.com

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved