Hi,
I have a question do I have to increase the Transaction Log size to create new indexes after dropping existing one on a table which has 18108360 rows. I need to recreate 5 indexes on that table. As this table is corrupt. Let me know. The corruption was on one of the indexes of this table.
Thanks,
Cynthia
Just a quick question, Is it better to build an Indexindicies after creating a new table or after populating the table with the data. Which is quicker? What about statistics? This table is dropped/and then recreated on a weekly basis. It is a reporting database.
Thanks.
I ran the index tuning wizard and it suggested 4 new indexes w/ a 20% improvement to be gained..
I saved the index creations to a script file and I'm curious if I can implement them at any time or is it better to do that after most of the users are done w/ the system for the day..
Also, any other performance suggestions to help speed up queries? Thanks in advanced..
Thanks to help from Ray Maio I have been able to create copies of various tables using the Select Into command.
Ray also responded that this command does does not transfer Primary Key and Index information.
I have attempted to create an index by using the following statement:
create nonclustered index SchoolIndx on wrestlerstest (School)
I did not get an error, but can't tell ift the index was created. The question is, how do I find out if
there is an index for the column School? Is there a command that will tell me. I'm just sending commands
to the server via a short .asp program and don't really know how to obtain a message back. Could I find the information
by browsing the
Thanks again in advance,
Greg Zafros
Hi
I want to know if we can create indexes on Views in SQL 7.0. If yes, then how
Thanks
alsi
Greetings all,
What's best practice for creating clustered indexes?! Should they be added to a table AFTER it has been populated or should the clustered index be created BEFORE?
Thanks for your advice in advance.
Hi all,
I have created a new database from a backup. When I run the application, it seems like it is not getting the right indexes but it is in Development environment. So, I ran a script to recreate the index by DBCC DBREINDEX(TABLENAME). Still not getting it right. mainly it is not using the primary key index. I believe that dbcc dbrenindex will also reindex primarykey indexes. Any one know, how to overcome this problem. It is a production issue and I would appreciate for your prompt advise. I have also ran the update statistics on all tables but with no luck
Note: Does anyone has any script to drop the primarykey index and to recreate it.
Thanks,
Joe
I'll be running SQL v7.0 with raid level 0. The DB will have approx. 250 tables and 1200 indexes.
Can you have the indexes located on a physically separate drive with SQL v7.0 and would this be the best practice?
I would like to create an index on a table from the Database Explorer.
I am using Microsoft Visual C# 2005 Express Edition.
I downloaded Microsoft SQL Server Management Studio Express with the intention of using it to create an index, but it does not seem to find the database that I created from the Database Explorer.
Thanks!
Dear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I€™m using 5 conditions, I can€™t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:
CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))
CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))
INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC
INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID
WHERE RecordedCalls.ID <= '9369907'
AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))
AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)
AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')
AND RecordedCalls.ServerID NOT IN('2')
AND RecordedCalls.AgentID NOT IN('1000010000')
AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))
AND RecordedCalls.IsDeleted='false'
GROUP BY RecordedCalls.CallType
SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')
WHEN 'Arabic' THEN #tempLookups.NameA
ELSE #tempLookups.NameE
END AS CallsType FROM
#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code
DROP TABLE #tempLookups
DROP TABLE #tempTable
Thanks all,
Tayseer
Any suggestions how to create efficient indexes??!!
I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:
Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.
Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!
-mike
I hope i'm in the right place, but thanks anyway....
Actually i have 2 questions (regarding sql-server Indices/Keys):
1) I have an index, which is consisted of 4 columns.
I've read elsewhere that this index functions (as well) as an index (single column
index) on the first column of this multi-column index.
Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns
seperately i need to define only 3???
2) I have a unique key consisted of multiple columns.
I'd like to save an index to this combination of columns as well (to speed up
things in DB...).
Does the definition of a multiple-columns key free me from defining the multiple-
columns index???
can anyone explain the main diference between Keys and Indices???
I hope i'm in the right place, but thanks anyway....
Actually i have 2 questions (regarding sql-server Indices/Keys):
1) I have an index, which is consisted of 4 columns.
I've read elsewhere that this index functions (as well) as an index (single column
index) on the first column of this multi-column index.
Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns
seperately i need to define only 3???
2) I have a unique key consisted of multiple columns.
I'd like to save an index to this combination of columns as well (to speed up
things in DB...).
Does the definition of a multiple-columns key free me from defining the multiple-
columns index???
can anyone explain the main diference between Keys and Indices???
thanks,
Ran Kizi
Hi Guys,
I have a requirement to create update,delete triggers in all the columns of each table in my development database. I would really appreciate if anyone could guideme or send me the script.
Thaks a lot for your consideration
Jay
hi,
I am trying to create a file using a query in SQL...
kindly help.
Thankyou. --- Vijay
I want a stored procedure to run everytime the batch Status field is set to 'Released'
<Code>
CREATE TRIGGER [CSITSS].[tdecker].[FB401BV_TRIGGER] ON [CSITSS].[dbo].[Fbbatch] FOR UPDATE [Status]
AS
DECLARE @RC int
DECLARE @Batch int
SET @Batch = (??? BATCH NUMBER THAT WAS SET TO RELEASE ???)
EXEC @RC = [CSITSS].[tdecker].[GLP_FB401BV_BATCH] @Batch
</Code>
Hi all,
I'm trying to create a temporary table with one of the columns with a datetime datatype. Sql server 7.0 is giving me an error:
Error 195 date is not a recognized function name.
Example of my create table looks something like this:
create table #temp_table(column_one int, column_two(15,2), Column_three datetime)
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
Hi,
I need to parameterize some values in the data flow so that i can chnage the values directly in parameter file and re run the data flow for new value in the passed in the parameter. This can be easy for other who do not know about the flow of data flow task as to where to change the variable/parameter.
How can this be accomplished. I want the data flow task to refer to this file before it starts executing and pick the appropriate value from the file.
Or is their any better way to accompalish what i want to do here in SSIS.???
tHNAKS FOR UR HELP FOLKS !!!
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
frenz:
i have the following procedure:while running
EMP_SP_EmpDetails_Display 'FirstName', 'ASC'
i got the error like this: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
plz can any one solve this problem.
My procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER procedure [dbo].[EMP_SP_EmpDetails_Display]
-- Add the parameters for the stored procedure here
(@colum as varchar(50),@order as varchar(50))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sqlexec varchar(1500)
-- Insert statements for procedure here
set @sqlexec=''
set @sqlexec=@sqlexec + 'SELECT EL.UserName,EL.Password, E.EmpID, E.FirstName, E.LastName, E.DesignationID, E.DepartmentID, E.Sex '
set @sqlexec=@sqlexec + 'E.DOB, E.Age, E.Address, E.Email, H.Hobby,'
set @sqlexec=@sqlexec + 'ContactNo=Case When E.phone is not null Then E.phone '
set @sqlexec=@sqlexec + 'When E.phone is null Then E.Mobile End, '
set @sqlexec=@sqlexec + 'E.DateOfjoining, D.Designation, DE.Department '
set @sqlexec=@sqlexec + 'From dbo.EMP_Tbl_Employee E '
set @sqlexec=@sqlexec + 'Inner Join dbo.EMP_Tbl_Designation D On E.DesignationID=D.DesignationID '
set @sqlexec=@sqlexec + 'Inner Join dbo.EMP_Tbl_Department DE On E.DepartmentID=DE.DepartmentID '
set @sqlexec=@sqlexec + 'Inner Join dbo.EMP_Tbl_EmployeeHobbies EH On E.EmpID=EH.EmpID '
set @sqlexec=@sqlexec + 'Inner Join dbo.EMP_Tbl_Hobby H On E.Sno=H.Sno '
set @sqlexec=@sqlexec + 'Inner Join dbo.EMP_Tbl_EmployeeLogin EL On E.Sno=EL.Sno order by '+@colum+' '+@order+''
exec(@sqlexec)
END
i have some doubt that if we want to put some data in the database, is two paragraph words, so is it i need to type this two paragraph word in the database?
how i can made a paragraph in database? is it possible?
Hi there
I have a question here. I need to import the excel template into sql server data base tables. I did worked on the regular importing of excel spread sheets to import into the appropriate data base columns. I am not getting idea with the eacel template. My main goal is to import the excel template to sql tables and then reproduce the same template for the user in the browser. If any body can help i would really appreciate.....
hi all
I have heard that neither SQL Server 2000 nor SQL Server 7.0 supports adding the IDENTITY property to an existing column by using a single T-SQL command .Is it true?
ok
tanx in advance
ramesh writes "i was using oracle database in last 2 years.
presently my concern using sqlserver 2000.
so i have some doubt in sqlserver 2000.
pls answer me.
1.Give some tips T-SQL with Examples.
2.how to call .sql files in query analyser."
Hello,
When hourly transcation log backups are taken , is any log sequence number(LSN) generated .
I want to know if this is right or wrong.I
do the T-log backups until 10.00 pm and I do the complete DB backup at 12.00 AM. So the complete DB backup contains the DB and the T-log from 10.00 pm till 11.59 pm.
So, is my understanding right.
Thanks
Hi,
I have a table in my database. Now I will import the data into table from excel. Now I have Firstname,Lastname,email,phone from excel.There is a chance some email,phone can be null. Now I have to identify and update the null values with values from tables from another database which has a master table containing all values.
How to write a script?
Hi guys I am new to SQL Pls help me on this...
In sql server 2005 we have ROW_NUMBER() as inbuilt function...
But i am using sql server 2000... instead of ROW_NUMBER () what is the functionality in SQL SERVER 2000. can anyone help me out in this pls...
Thnaks in advance!!!!
Thanks and Regards,
Samuel P
hi,How to search a word in the sqltable?.ie suppose the field name has data type nvarchar(200). From there i enter the persional address of that persion. now i want to know the information whoes are coming from particular city?. The city value can enter in the address field. how we can make search?...with regards,ks.kumar****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...753175428d29c27*****************************************
View 1 Replies View RelatedHi,
How to retrive the health insurance claim cms form in fastest way using sql server 2005
Hello:
I had a Sql query doubt. I have two tables. One is table with profile data but in this table instead of actually values it stores codes. The value of each code is stored in another table. Something like this
Profile Table
ProfileID Title First Name Last Name State
1 122 xxx yyy 333
So as you can see title and state both are code which are further stored in another table which is something like this
Code_ID Code_Value
122 Director
124 International
333 AZ
Now I want to replace all these codes with values. Something like this
ProfileID Title First Name Last Name State
1 Director xxx yyy AZ
I tried Case statement but it fails. I am not sure how to do this. I have hundred thousands of records to doing programmaticall slows the process. I would appreciate your help or ideas.
When I try to restore a database on one server from another server, by choosing the ‘From device’ option, I am not able to see any mapped drives. So I always have to copy the backup file to my local drive before performing the restore. Is there any other way to do it?
For example, Server1 contains a database db1. A full backup, differential backup and log backups of db1 are stored in ,
D:mssql7ackupfulldb1_full.bak
D:mssql7ackupdiffdb1_diff.bak
D:mssql7ackuplogdb1_log.bak
The backup devices have been created using sp_addumpdevice.
Server2 contains a database db1.
If I need to restore a copy of db1 on server1 onto db1 of server2, I first copy
D:mssql7ackupfulldb1_full.bak
D:mssql7ackupdiffdb1_diff.bak
D:mssql7ackuplogdb1_log.bak
to a local drive on server2.
I then restore using the ‘from device’ option.
SQL Server uses NT Domain account with admin. Privileges, log on as a service rt. Etc.
Thankyou.
Praveena
When I try to restore a database on one server from another server, by choosing the ‘From device’ option, I am not able to see any mapped drives. So I always have to copy the backup file to my local drive before performing the restore. Is there any other way to do it using Enterprise Manager?
For example, Server1 contains a database db1. A full backup, differential backup and log backups of db1 are stored in ,
D:mssql7ackupfulldb1_full.bak
D:mssql7ackupdiffdb1_diff.bak
D:mssql7ackuplogdb1_log.bak
The backup devices have been created using sp_addumpdevice.
Server2 contains a database db1.
If I need to restore a copy of db1 on server1 onto db1 of server2, I first copy
D:mssql7ackupfulldb1_full.bak
D:mssql7ackupdiffdb1_diff.bak
D:mssql7ackuplogdb1_log.bak
to a local drive on server2.
I then restore using the ‘from device’ option.
SQL Server uses NT Domain account with admin. Privileges, log on as a service rt. Etc.
Thankyou.
Praveena