MS SQL Server :: 'Include Clause' In Create Index Syntax

Does The Group By Have To Include All Fields From The SELECT Clause?
hey all,

say i have the following function

SELECT GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2,
F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE,
F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR,
F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME,
F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR

FROM GLF_CHART_ACCT

INNER JOIN F1ADR_ADDRESS ON (GLF_CHART_ACCT.CHART_NAME = F1ADR_ADDRESS.ENTITY_KEY1)
AND (GLF_CHART_ACCT.ACCNBRI = F1ADR_ADDRESS.ENTITY_KEY2)

GROUP BY GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2,
F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE,
F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR,
F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, GLF_CHART_ACCT.ACCNBRI,
F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.CHART_NAME, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR,
GLF_CHART_ACCT.SELN_TYPE1_CODE

HAVING CHART_NAME='ARCHART' AND GLF_CHART_ACCT.DESCR1 <> '' AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade'
AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%' ORDER BY GLF_CHART_ACCT.DESCR1;

I get errors if not all the fields are included in the group by clause.

what i dont get is why i have to create seperate groups for this query...or am i reading it wrong??

Cheers,

Justin

View Replies !

Index With Include.
We today have 3 different indexes on a table that look like this
CREATE INDEX IX_01 ON TORDLOG ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX])
CREATE INDEX IX_02 ON TORDLOG ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX], [KUNDNR], [ALFKOD], [SUMMSP], [SUMMPP], [SUMMBP])
CREATE INDEX IX_03 ON [TORDLOG] ([REGDAT]) INCLUDE ([ORDENR], [SUBNRX], [RADNRX], [KUNDNR], [ALFKOD], [SUMMSP], [SUMMPP], [SUMMBP])
I want to combine the index to only 1 index; How do can combine all the include columns to one large? Or shall keep all index. Bit bad on how to handle include columns. Or just create new index with regdat and forget all about include?
CREATE INDEX IX_02 ON TORDLOG ([REGDAT])

OR

CREATE INDEX IX_02 ON TORDLOG ([REGDAT]) INCLUDE (ALL COLUMNS)

View Replies !

Syntax To Include Apostrophe In Sql Insert
I have a insert statement but one of the strings contains a apostrophe. If I leave the apostrophe in an error occurs becuase it thinks that it is the end of the string. What is the proper syntax for including apostrophes in a string?

Thanks.

View Replies !

Do You Include Clustered Index Column In Other Indexes?
I have a database where records are Inserted by an external process.
There is no updating or deleting of the data once inserted. The table in
question has a Clustered Index on the Machine_ID (integer) (data is from
manufacturing processes). Each record bears a start and end time. Most
queries involve the Machine, a time span (start time between to points in
time), the Downtime Cause, and the Running Mode.

I want to add an index on the Start Time, the Downtime Cause, and the
Runtime Mode.

My question is: should this new index also contain the Machine_id column
or does the existence of the Clustered Index already on that column negate
its need in the new index?

RC - Dedicated to only creating original mistakes!

View Replies !

System SP That Will Retrieve Index Include Columns?
Hi,

SQL Server 2005 has a new very useful feature for creating non-clustered indexes called INCLUDE <columns> which are very helpful when trying to create covering indexes.

Does anyone know of a way to retrieve these INCLUDE columns through any of the system metadata tables? The sp_helpIndex stored procedure is what I currently use but that only returns the (sorted) index columns and not the include columns.

Thanks in advance,

Gordon Radley

View Replies !

How To Create A Report Include 2 Page Form

Hi.
Does anybody knows how to create a report inside include 2 page? per page has itself body & pagefooter. for example on Page 1 is PO on page 2 is this PO contract list.

Thanks

View Replies !

Clustered Index On Client_ID+ORderNO+OrdersubNo, If I Create 3 Noncluster Index On Said Column Will It Imporve Performance

Dear All.

We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.

table is >> Order_Dtl

Columns are:-

Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)

Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.

The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.

Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)

My Question are:-

Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?

Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?

Q3. I want to check what indexes has been used? on what search?

Q4. How can i check what table was populated when, or last date of update (DML)?

My Limitation is i Dont Create a Partioned table. I dont have permission to do it.

In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.

I am thank u to all who read or reply.

Arshad

Manager Database
Esoulconsultancy.com

(Teradata Master)
10g OCP

View Replies !

Include Clustered Index In Non-clustered Index?
Hi everybody!

I just ran the Database Engine Tuning Advisor on a relative complex query to find out if a new index might help, and in fact it found a combination that should give a performance gain of 94%. Fair enough to try that.

What I wonder about: The index I should create contains 4 columns, the last of them being the Primary Key column of the table, which is also my clustered index for the table. It is an identity integer btw.

I think I remember that ANY index does include the clustered one as lookup into the data, so having it listed to the list of columns will not help. It might at worst add another duplicate 4 bytes to each index entry.

Right? Wrong? Keep the column in the index, or remove it since it is included implicit anyway?

Thanks for suggestions!
Ralf

View Replies !

CREATE TABLE Syntax, Combining CREATE, INSERT, SELECT With Character Placement
Working in Access Queries
Access version 2003
MSSQL 2000
Using http://www.mckoi.com/database/SQLSyntax.html#2 and
http://www.minisoft.com/pages/middleware/odbc32/pages/odbcae.htm
for references

I'm trying to just save the query and I keep getting a Quote: "Syntax error in CREATE TABLE statement".
If I try to run/save the Select portion by itself, it's successful.
If I try to run/save the Create Table portion by itself, it's successful.
But I've got something wrong when I try to put them together w/ the Insert statement.

Code:

What I've got
CREATE TABLE Month2 (
ID NUMBER,
CustID NUMBER,
Month TEXT,
Year TEXT,
Amount TEXT);
INSERT INTO Month2 (col1, col2, col3, col4, col5);
SELECT CustID, {fn RIGHT (CurApr, 3)}, {LEFT (CurApr, 3)}, CurApr
FROM dbo_commenehisttrial;

I'm trying to create a table then flow data in from a much larger table while converting the headers and data into respective columns. Also trying to convert the headers into two columns of meaningful data. I've got the header part more or less answered w/

Code:

SELECT CustID
, 'Apr 05' as [Date]
, CurApr as Amount
FROM dbo_commenehisttrial;
union all

The above statment is repeated 36 times for each of my 3 sets of 12 months. That works to create a "query table" but I wasn't sure how to run a second query off a first query results.

I'd actually like to change the header "CurApr" into two fields:
All "Cur" becomes 2005 and "Apr" is listed in the same fashion as the 2nd line of the last section of code.

Something like....

Code:

SELECT CustID
, '2005' as [Year],
, 'Apr' as [Month]
, CurApr as Amount
FROM dbo_commenehisttrial;
union all
repeating...

I'm not sure exactly how to combine CREATE, INSERT, and SELECT, but as a simply display query that last bit works. So right now I'm getting hung up with the syntax error on CREATE TABLE.

Thanks.

View Replies !

Incorrect Syntax Near WHERE Clause
<asp:SqlDataSource ID="productsUpdate" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAccountType %>"
SelectCommand="SELECT [ProductID], [ProductName], [ImageTitle], [ImageData], [ImageMimeType] FROM [Products] WHERE [ProductID] = @ProductID"
UpdateCommand="UPDATE [Products] SET [ImageTitle] = Null, [ImageData] = Null, [ImageMimeType] = Null WHERE [ProductID] = @ProductID" InsertCommand="INSERT INTO [Products] ([ImageTitle], [ImageData], [ImageMimeType]) VALUES (@ImageTitle, @ImageData, @MimeType) WHERE ([ProductID] = @ProductID)">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="ProductID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="ImageTitle" />
<asp:Parameter Name="ImageData" Type="string" />
<asp:Parameter Name="MimeType" Type="string" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ImageTitle" Type="String" />
<asp:Parameter Name="ImageData" />
<asp:Parameter Name="MimeType" Type="String" />
</InsertParameters>
</asp:SqlDataSource> Hi Guys, new to development using visual studio and vb.net 2.0. and SQL express. Have a small problem.I have an update command which im using to change the values of a blob image table to NULL so that i can then insert a new image into the table. This works fine. My problem is that my insert statement has an error and i cant figure this out. if i dont add the where clause it works fine but inserts the new image into the table without the product data, i want to insert the new image into the existing product information. The error message i keep getting is "incorrect syntax near where"Any help would be greatly appreciatedThanks

View Replies !

Syntax Error On Where Clause
I have the code below and the where clause is giving me errors, I have already setup FT Index, I think the error is lack of ' or " or "" or some kinda syntax pls help... as I can figure it out

I passed the word MBAISE

where CONTAINS(*, '+@searchstring+' )

Declare @myStatement varchar(8000)
Declare @LastRec int
Declare @FirstRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec =(@CurrentPage * @PageSize + 1 )

SELECT @myStatement = ' SELECT SalaryStatistical, EmployerID,JobLocation, location, SectorID
FROM #TEMP

where CONTAINS(*, '+@searchstring+' )

AND (LastLogin >= dateadd(d, -' + @HowManyDays + ', getdate())
AND (ID > '+ convert(varchar(20),@FirstRec) +')
AND (ID < '+convert(varchar(20),@LastRec) +')
AND (SalaryStatistical > '+ convert(varchar(20),@SalaryStatistical)+')

'
IF @JobLocation != @myzero

BEGIN
SELECT @myStatement = @myStatement + ' AND (JobLocation IN ('+convert(varchar(90),@JobLocation)+')) '
END

IF @sectorID != @myzero
BEGIN
SELECT @myStatement = @myStatement + ' AND (SectorID IN ('+convert(varchar(90),@sectorID)+')) '
END

SELECT @myStatement = @myStatement
print @myStatement

exec(@myStatement)

GO

View Replies !

Dynamic Create Table, Create Index Based Upon A Given Database
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View Replies !

Syntax Error In FROM Clause - Help Needed Please!!
Hi all,I am a newbie to asp and have been using VWD to make a database for an assignment but I am having big problems trying to extract some data to a datalist view. I intend to use this page to display all information of hotel rooms. I know its probably really obvious to some of you but its driving me mad!!! Any help would be greatly appreciated. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim ds As New DataSet() Dim sGetRooms As String = "SELECT RoomID, RoomType, " _ & "RoomName FROM Rooms2 " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomType" Dim sGetroomsizeandprice As String = "SELECT ID, RoomSize, RoomPrice, @RoomType " _ & "FROM roomprices JOIN Rooms2 ON Rooms2.ID = roomprices.ID " _ & "WHERE RoomType LIKE @RoomType " _ & "ORDER BY RoomPrice" Dim sConnect As String = ConfigurationManager.ConnectionStrings("White Sand's Hotel - Dan MahilConnectionString").ConnectionString Using con As New OleDbConnection(sConnect) Dim da As New OleDbDataAdapter(sGetRooms, con) Dim param As New OleDbParameter("RoomType", OleDbType.VarChar, 10) param.Value = Request.QueryString("RoomType") & "%" da.SelectCommand.Parameters.Add(param) Try da.Fill(ds, "Rooms2") da.SelectCommand.CommandText = sGetroomsizeandprice da.Fill(ds, "roomprices") Catch ex As Exception Label4.Text = "ERROR: " & ex.Message Exit Sub End Try End Using Dim pkcol As DataColumn = ds.Tables("Room2").Columns("RoomID") Dim fkcol As DataColumn = ds.Tables("roomprices").Columns("ID") Dim dr As New DataRelation("MenuLink", pkcol, fkcol) ds.Relations.Add(dr) DataList1.DataSource = ds DataList1.DataMember = "Rooms2" DataList1.DataBind() End Sub

View Replies !

ANSI-92 Inner Join Vs. Where Clause Syntax
Having problems rewriting my join condition using the "inner join" syntax.

My query, working with an intersection table:

SELECT Description, EmailAddress
FROM Accounts_Roles r, Accounts_Users u, Accounts_UserRoles ur
WHERE
r.RoleID = ur.RoleID
AND
u.UserID = ur.UserID

This works fine, but i want to write it using 'inner join' style, so I tried:

SELECT Description, EmailAddress
FROM Accounts_Roles r, Accounts_Users u
INNER JOIN Accounts_UserRoles ur
ON
r.RoleID = ur.RoleID
AND
u.UserID = ur.UserID

which gives me an error (The column prefix 'r' does not match with a table name or alias name used in the query.)

Any ideas as to how I'm screwing this up would be appreciated.

Thanks,
Gordon Z

View Replies !

Syntax For WHERE Clause For Access Database
I have used this query statement with a SQL Server 2005 database and need to use something similar with an Access database:

SELECT products.*, Category AS Expr1
FROM products
WHERE (Category = @Category)

When I test this in a table adapter there is no preview due to lack of parameter. I seem to recall that Access uses different syntax in the WHERE filter clause (i.e., not @). Can someone help me out with this?

View Replies !

Sql Syntax For Multiple Likes Phrases In Where Clause

You know how you can go:

Code Block
where control_id in (11111,22222,33333,44444)

or

Code Block
where TextName in ('11111','22222','33333','44444')

and you can do this:

Code Block
where TextName like '11%' or TextName like '22%'

well how do you do this? Or can you... or can we right a function to do it... or are we just hosed writing like after like.

where TextName like in ('11%','22%',... and so on)? is anything like that possible?

or better yet
where TextName like in ( select substring(columnName, 1, 2) + '%' from whatever )

can you imagine the dynamics if that syntax actually worked?

Can a function be written to mimic this functionality? so I can do something like this:
where TextName = function('11%,22%,33%')

View Replies !

Index / Join / Where Clause Very Slow
Hello,

first of all, some facts of the case:

Table Master Table Dimension
ID Code Price ID Name
1 A44333 5000 1 "Scanner"
2 D442 3000 2 "Notebook"
3 D6644 4000 3 "Banana"

I join both tables on ID and search one time for ID and another time for Name. Looks like

(a)
SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id
WHERE master.id=1
AND Code like 'A44'
(b)
SELECT AVG(Price) From Master JOIN Dimension ON Master.id = Dimension.id
WHERE Name = 'Scanner'
AND Code like 'A44'

Why does query (b) take longer than query (a)? Dimension has 12 Rows and
Master has about 24M Rows.

For index I did
Create Index IX_Master_ID on Master(ID)
Create Index IX_Master_Code on Master(Code)
Create Index IX_Dimension_ID on Dimension(ID)
Create Index IX_Dimension_Name on Dimension(Name)

I noticed, that when i leave the Code like 'A44' clause, query (a) and (b) do take same time. I'm really confused. Can someone please help me out?

Thank you

Silas

View Replies !

Syntax Error When Building Up A Where Clause In Stored Procedure
Can anyone tell me why the line highlighted in blue produces the following error when I try to run this stored proc? I know the parameters are set properly as I can see them when debugging the SP.
I'm using this type of approach as my application is using the objectdatasource with paging. I have a similar SP that doesn't have the CategoryId and PersonTypeId parameters and that works fine so it is the addition of these new params that has messed up the building of the WHERE clause
The Error is: "Syntax error converting the varchar value ' WHERE CategoryId = ' to a column of data type int."
Thanks
Neil
CREATE PROCEDURE dbo.GetPersonsByCategoryAndTypeByName (@CategoryId int, @PersonTypeId int, @FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @StartRow int, @PageSize int)
AS
Declare @WhereClause varchar(2000)Declare @OrderByClause varchar(255)Declare @SelectClause varchar(2000)
CREATE TABLE #tblPersons ( ID int IDENTITY PRIMARY KEY , PersonId int , TitleId int NULL , FirstName varchar (50) NULL , FamilyName varchar (50) NOT NULL , FullName varchar (120) NOT NULL , AltFamilyName varchar (50) NULL , Sex varchar (6) NULL , DateOfBirth datetime NULL , Age int NULL , DateOfDeath datetime NULL , CauseOfDeathId int NULL , Height int NULL , Weight int NULL , ABO varchar (3) NULL , RhD varchar (8) NULL , Comments varchar (2000) NULL , LocalIdNo varchar (20) NULL , NHSNo varchar (10) NULL , CHINo varchar (10) NULL , HospitalId int NULL , HospitalNo varchar (20) NULL , AltHospitalId int NULL , AltHospitalNo varchar (20) NULL , EthnicGroupId int NULL , CitizenshipId int NULL , NHSEntitlement bit NULL , HomePhoneNo varchar (12) NULL , WorkPhoneNo varchar (12) NULL , MobilePhoneNo varchar (12) NULL , CreatedBy varchar(40) NULL , DateCreated smalldatetime NULL , UpdatedBy varchar(40) NULL , DateLastUpdated smalldatetime NULL, UpdateId int )
SELECT @OrderByClause = ' ORDER BY FamilyName, FirstName'
SELECT @WhereClause = ' WHERE CategoryId = ' + @CategoryId + ' AND PersonTypeId = ' + @PersonTypeIdIf NOT @Firstname IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND FirstName LIKE ISNULL(''%'+ @FirstName + '%'','''')'ENDIf NOT @FamilyName IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND (FamilyName LIKE ISNULL(''%'+ @FamilyName + '%'','''') OR AltFamilyName LIKE ISNULL(''%'+ @FamilyName + '%'',''''))'END
Select @SelectClause = 'INSERT INTO #tblPersons( PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId)
SELECT PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId
FROM vw_GetPersonsByCategoryAndType '
EXEC (@SelectClause + @WhereClause +@OrderByClause)

View Replies !

How To Force The Query Engine To Use An Index With An OR Clause?
I have the following table:

Create Table Item(
I_Code     NVarChar(40) Primary Key NOT NULL,
I_MatID    NVarChar(40),
I_Name     NVarChar(160),
I_BC       nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1    nvarchar(55),
I_Acquired nvarchar(35),
I_Info2    nvarchar(55),
I_Info3    nvarchar(55),
I_Date DateTime DEFAULT GetDate()

);

Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);

Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )

While this one use:

select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )

Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423

Some feedback?
Thanks

View Replies !

Syntax Help -- Clustered Index
what's wrong with this code?

i'm trying to create a clustered index

View Replies !

Index Hint Syntax Help
Can someone give me the right syntax for using an index hint in a delete statement as below?

DELETE table1 WITH (INDEX('indexname_IX'))
WHERE datecolumn < @datevariable

I get this error message:

Incorrect syntax near the keyword 'index'.

I'm using Query Analyzer in SQL 2000 Enterprise.

Thanks,

Gerald

View Replies !

DROP INDEX Syntax For MS SQL 2000
I am executing

DROP INDEX [IX_Users] ON [Users]

and i get a syntax error
what is the exact syntax to DROP an INDEX for MS SQL 2000

thank you

View Replies !

Create INDEX Within CREATE TABLE DDL
Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance

View Replies !

CTE Error: Incorrect Syntax Near The Keyword 'with'. If This Statement Is A Common Table Expression Or An Xmlnamespaces Clause,
I am having this error when using execute query for CTE

Help will be appriciated

View Replies !

How To Create Dynamic T-SQL Clause
Hi,

I have a project which is need dynamically t-sql clause...I've created interface for the create dynamic t-sql clause.
But the clause should get data from relational database. It needs inner join...so it's very hard to do it..

for example

lkpProduct

id      product
1       Mouse
2       Keyboard

3       Modem
4       Monitor

main

id       productid

1        4
2        1
3        2
4        3
5        4

You can get this table with 2 ways like this..

1. Select main.id, lkpProduct.product from main,lkpProduct where main.productid = lkpProduct.id
2. Select id, case productid when 1 then 'Mouse'.....end as product from main

mytable

id      product
1       Monitor
2       Mouse
3       Keyboard
4       Modem
5       Monitor

My question is; how can i use CASE function dynamically ?? Mouse should get from lkpProduct....I do not want to write long table manually..

I hope it is clear for you..

Regards.

View Replies !

Syntax To Create A Job

I am new to sqlserver.

Could anybody please help me by giving me a syntax for the command in job schedular to run dts package

i enter the job name in the first screen of the wizard.

I entered the step name. type Transact SQL

database msdb

What should i enter in command text box

My dts package name is fullcube.

Thanks in advance

View Replies !

Create Table Syntax?
Ok I am new to Sql and am wonder How I can (when createing a table ) have a field that is auto incremented.

Here is what I have so far

Code:

CREATE TABLE test (`id` INTEGER NOT NULL, `test` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`)

I want the primary key id to be an incremented number.

I am used to mysql and I would just have the field created as an AUTO_INCREMENT.

Do I use the INCREMENT function within the first query or do I have to have another query that will alter the table and make the field an auto incremented number.

Any suggestions would be great.

Thanks,
Tom

View Replies !

Create View Syntax
Why can't one use a three part naming convention when creating a view?

example:

CREATE VIEW databasename.schemaname.anyview
AS
SELECT * FROM anytable

The following error is returned:

Msg 166, Level 15, State 1, Line 1

'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

View Replies !

Create Index
I need to check if an index available on table T1 and field F1. If not, create a non-clustered on F1. How can I do this in a stored procedure?

View Replies !

Create Index
clustered/non-clustered indexes in the msdnhttp://msdn2.microsoft.com/en-us/library/aa964133.aspx

View Replies !

Create Index
I need to check if an index available on table T1 and field F1. If not, create a non-clustered on F1. How can I do this in a stored procedure?

View Replies !

Need Help To Create An Index
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.carr_summary_Datewise Script Date: 5/15/2008 10:20:37 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[carr_summary_Datewise]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[carr_summary_Datewise]
GO

CREATE proc carr_summary_Datewise --2007,9,27,30,'COMPUTERTEL'

@pYear int,
@pMonth int,
@pDay1 int,
@pDay2 int,
@pOperator varchar(32) -- DisplayName

as
begin

declare @sql varchar(4000)
declare @Service varchar(32)
declare @Operator varchar(32)
declare @tDiff int
declare @pDate1 varchar(32)
declare @pDate2 varchar(32)
declare @pD1 datetime
declare @pD2 datetime
declare @sD varchar(2)
declare @eD varchar(2)
declare @eM varchar(2)
declare @tb1 varchar (32)

set @pDate1 = dbo.AsString(@pYear,@pMonth,@pDay1,0,0,0)
set @pDate2= dbo.AsString(@pYear,@pMonth,@pDay2,23,59,59)
--print @pDate1
--print @pDate2

select @Operator=SystemName,@tDiff=timeDiff from Report..Carriers where DisplayName=@pOperator
select @Service=serviceName from Report..Carriers where SystemName=@Operator
--print @tDiff

set @pD1=dateadd(hh,@tDiff,(cast(@pDate1 as datetime)))
set @pD2=dateadd(hh,@tDiff,(cast(@pDate2 as datetime)))
--print @pD1
--print @pD2

/*set @sD=datepart(dd,@pD1)
set @eD=datepart(dd,@pD2)
set @eM=datepart(mm,@pD1)*/
--print @eM

if @pDay1=1
begin
set @sD=1
end
else
begin
set @sD=datepart(dd,@pD1)
end

if @pDay2=30
begin
set @eD=30
end
else
begin
set @eD=datepart(dd,@pD2)
end

if @pDay2=31
begin
set @eD=31
end
--else
-- begin
-- set @eD=datepart(dd,@pD2)
-- end

--print @sD
--print @eD
--print 'Operator=' + @pOperator
--print 'Service=' + @Service

--set @tb1='ob_sep07'

if @pMonth=1
begin
set @tb1='ob_Jan08'
end
if @pMonth=2
begin
set @tb1='ob_Feb08'
end
if @pMonth=3
begin
set @tb1='ob_Mar08'
end
if @pMonth=4
begin
set @tb1='ob_Apr08'
end
if @pMonth=5
begin
set @tb1='ob_May08'
end
if @pMonth=6
begin
set @tb1='ob_Jun08'
end
if @pMonth=7
begin
set @tb1='ob_Jul08'
end
if @pMonth=8
begin
set @tb1='ob_Aug08'
end
if @pMonth=9
begin
set @tb1='ob_Sep08'
end
if @pMonth=10
begin
set @tb1='ob_Oct08'
end
if @pMonth=11
begin
set @tb1='ob_Nov08'
end
if @pMonth=12
begin
set @tb1='ob_Dec08'
end

set @sql='

select Callyy,Callmm,Calldd,CallDate,dbo.Acc_NearestZone_Tracks(Operatorout,routepfx,CallDate) zone,
routepfx,Talktime,RefUploader.dbo.WhichTimeClass(''' + @pOperator + ''',CallDate) TimeCls,Cost
from
(

select Callyy,Callmm,Calldd,dbo.asString(Callyy,Callmm,Calldd,Callhh,0,0) CallDate,Routepfx,Operatorout,
cast(sum(Talktime/60.) as decimal(10,2)) Talktime,Cost
from Report.dbo.'+@tb1+' (nolock)
where Operatorout=''' + @Operator + '''
and Callyy=' + ltrim(str(@pYear)) + '
and Callmm=' + ltrim(str(@pMonth)) + '
and calldd between ' + ltrim(str(@sD)) + ' and ' + ltrim(str(@eD)) + '
group by Callyy,Callmm,Calldd,dbo.asString(Callyy,Callmm,Calldd,Callhh,0,0),
Routepfx,Operatorout,Cost
)x
where CallDate between ''' + convert(varchar(20),@pD1,120) + ''' and ''' + convert(varchar(20),@pD2,120) + '''
'
--print(@sql)
--exec (@sql)

set @sql=
'select Callyy,Callmm,Calldd,Routepfx Prefix,case when zone is not null then zone else ''--NOT FOUND--'' end zone,
case when TimeCls is not null then TimeCls else ''--NOT FOUND--'' end TimeCls,
''' + @Operator+ ''' Operatorout,Sum(Talktime) Talktime,Cost
from ('+@sql+') x
group by Callyy,Callmm,Calldd,Routepfx,zone,TimeCls,Cost
order by Callyy,Callmm,Calldd,Routepfx,zone,TimeCls
'
--print (@sql)
exec (@sql)
end

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

this procedure takes more time to run

we need make it fastly

what should i need

if i create one index how it works

where should i create index please write one index for me

View Replies !

Create Index
Hi all

Recently we are having issues with one of the indexes during our maintenance. There is one index that which usually would have taken 10-15 mins continues to run even after 3 hrs. Other indexes on the same table finish nicely. Just this one gets stuck. Our index creation scripts drops existing index and creates new one..

CREATE INDEX ... ON ... WITH DROP_EXISTING, SORT_IN_TEMPDB ...etc

So last week when I got woken up because of this, I tried to drop the index explicitly and re create the index (by removing the DROP_EXISTING in above script). Even though it took about 45 mins it still finished in time during the maintenance window.

We had the same issue this week. I was looking at sp_who2 during the index creation, I noticed very low CPU activity and high DiskIO.
example: CPU : 4k cycles, DiskIO 50000.

There is 20% of free space on the index file group. Generally index creation takes higher CPU and lower DiskIO.

How do I find out what the issue is with this index? The table has 40 million rows. this is a non-clustered index. There are 2 other indexes on the table - one clustered. they both finished properly in less than 10 mins.

Thanks in advance,
Don

View Replies !

Create Table Syntax SQL In SQL Server 7
Hi guys,

I need to pass some SQL to someone else who will run it on their database. I have got the SQL for SQL Server 2000 but they are running SQL Server 7. Apparently the below MSSQL 2000 script doesn't work;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tableName](
[id] [int] IDENTITY(1,1) NOT NULL,
[ArticleID] [int] NULL,
[Heading] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BodyContent] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WrittenDate] [datetime] NULL,
CONSTRAINT [tableName] PRIMARY KEY CLUSTERED ( [id] ASC )
)

What is the equivalent of the above in for SQL Server 7? I don't have access to it via SQL Server Manager so have to run the script.

View Replies !

CREATE TABLE Syntax Error...
CREATE TABLE Agents(ID COUNTER NOT NULL CONSTRAINT constraintName_pk PRIMARY KEY,Name VARCHAR(255),Supervisor INTEGER,MasterCalendarVisible BOOLEAN default false)I'm using this against an Access DB using JET driver... I keep gettinga syntax error which goes away when I remove the last line... Anyideas whats wrong with it?Thanks!

View Replies !

Create Database Syntax Error
I am trying to create a database and I manage to get the code right for dropping the database if it already exists and then createing the data file and log file. After this I try to set some options for the database with the following code

ALTER DATABASE MyDataWarehouse
SET RECOVERY SIMPLE,
ANSI_NULLS ON,
ANSI_PADDING ON,
ANSI_WARNINGS ON,
ARITHABORT ON,
CONCAT_NULL_YIELDS_NULL ON,
QUOTED_IDENTIFIER ON,
NUMERIC_ROUNDABORT OFF,
PAGE_VERIFY CHECKSUM,
ALLOW_SNAPSHOT_ISOLATION ON;
GO

But I get an error saying Incorrect syntax near 'CHECKSUM'.

View Replies !

Best Practice: Use Values In WHERE Clause Or Create And Join Temp Table ?
Hi,
I am using a SQL back end to dynamically populate an asp.net report/page.
As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable.
My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query.
Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause?
Any help gratefully appreciated.
Thanks.
John

View Replies !

Scripted CREATE INDEX
I have SQL Server 2005 x64. I found in the studio I can generate allsorts of scripts, including the creation of indexes.I works, however the script contains the CREATE TABLE statements aswell. I want the scripts to recreate the scripts on another filegroup(testing a theory from previous post).Am I missing a switch in the wizard to exclude CREATE TABLE?I did create the script, but then I manually deleted all the CREATETABLE statements, which was a pain.ThanksRob

View Replies !

How To Create Index (Case When)
How to create index when there is a SQL statement like
Select count(1) as [Total],
sum(Case when Field1 < Field2 then 1 else 0 End) as [Selected]
from Table1

Thx in Adv
XLDB

View Replies !

Can't Create An Index On My View
G'day all.

I am trying to create an index on a view that joins two tables.

I get the classic error of course:
'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.'

Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it.

Code is below and any help greatly appreciated.

CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS
SELECTt_number
FROM dbo.ticket_cancellations RIGHT OUTER JOIN
dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number
WHERE dbo.tickets.t_cancelled <> - 1 OR
-- Add all cancellation codes that are to be excluded from the NET view below
(dbo.ticket_cancellations.tc_cancellation_code <> 83943
AND dbo.ticket_cancellations.tc_cancellation_code <> 83946)

GO
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)

View Replies !

Create INDEX WITH DROP_EXISTING
Hello,

Can I create an index ( which is not clustered) with DROP_EXISTING ON ?
Shall I still use on [PRIMARY]?

Create INDEX [name] ON [table name] ([column])
WITH DROP_EXISTING ON [PRIMARY]

Thank you,
Y
:rolleyes:

View Replies !

Unable To Re Create Index
Hi

I was asked to import some data from excel into a table within a sqlserver 2000 db the import was complaining about an index so then I deleted the index imported the data succesfilly but I'm now unable to re create the index, please see the error that I'm getting below. Can someone please help.
Thanks

- Unable to create index 'trainingGo'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 32. Most significant primary key is '439'.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

View Replies !

Any Need To Create An Index For A PK Field?
Are Primary Key fields automatically indexed, or do you have to create a seperate index for a PK in order for it to be indexed? I'm using SQL Server 2005.

View Replies !

How To Create A Covering Index
I have a table there is a query which has bad performance. This query normally can not use index because a lot of 'IS NULL','OR','LIKE' USED in where clause. I want to create a covering index for this query so it can use index scan only rather than Processor had to look up the row columns it needs
from a table or a clustered index.

current there are two index apply on that table:

CREATE UNIQUE CLUSTERED INDEX [Person_RecordID_UIX] ON Person
(
    [RecordID] ASC
)
go

ALTER TABLE [Person] ADD CONSTRAINT Person_PK PRIMARY KEY NONCLUSTERED
(
    [EntityID] ASC
)

How to add a covering index for the columns such as familyName,givenName, dob. All of these columns are not too much high selective.
Can I do like the following :

--create third index

CREATE NONCLUSTERED INDEX [Person_Cover_IX] ON [dbo].[Person]
(
    [EntityID] ASC
)
INCLUDE ( [FamName],

          [FirstName],

          [DOB],

          [Gender]
)

Or DROP Current index on entityid and recreate it with include clause.

What's the different between these two.

Thank you.

View Replies !