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!
Hi all,
I just realized recently that a database "XYZ" in the Object Explorer of my SQL Server Management Studio Express (SSMSE) is put in the Database Explorer of my VB 2005 Express for processing a Stored Procedure in executing the SELECT statements (not by using Input and/or Output Parameters) during the ADO.NET 2.0-VB 2005 Express programming, then the content of the database "XYZ" is not in the SSMSE. How can I return the database "XYZ" from the DataBase Explorer of VB 2005 Express back to the Object Explorer of SQL Server Management Studio Express (SSMSE) safely? Please help and advise.
Thanks in advance,
Scott Chang
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?
I have come across a database system which isn't designed to work optimally. It is fairly large (~400GB) and performance of loading and querying is degrading (improper data types, fragmented indexes, non unique clustering key and other problems). So, I have quite a task in front of me, but I am up for the challenge. I figure this is not a unique situation, many of us would have come across this before. I have done this before too, but only for smaller databases, some of the operations here I expect to take a couple of hours or more to complete (depending on load/infrastructure speed etc, I know).
My plan is thus:
+ Take a full backup of the database
+ Set the recovery model of the DB to simple
+ Drop non clustered indexes
+ Drop clustered indexes
+ Remove PKs (wrong data types, too large!)
+ Narrow data types (add new column, update column in batches to old value, rename new column to old column)
+ Add PKs, which will create clustered indexes automatically based on PK ID
+ Create non clustered indexes
+ Run a SHRINKDB (normal operations I would never do this, but this is a special case, ensure log file is truncated to a logical size especially after all those table modifications...)
+ Set the recovery model of the DB to Full
+ Ensure everything works OK or better
I found that when I install MSDE, then I can create/edit database objects for MSDE database in Visual Studio 2003 Server Explorer, but when I try to create/edit database objects under the default database created by the SQL Server 2000 installation, I cannot do these tasks as no option for these tasks appear when I right-click on the database object in Server Explorer. Anyone knows why this is happening?
View 3 Replies View RelatedIf I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?
Also, How can I see if I Need to update statistics^on the tables of my database?
Hi,
I need to create a table and a few fields in the SQL database programmatically in asp.net.
I know the tabes name, the server which it resides on, username/password. Would anyone have a small sample of this code?
Thanks
rad
I am very new to SQL Server 2005 so please excuse my mandune queastion.
First what I accomplished was I created several tables within SQL Server 2000 using the Database and Table Wizards. I also created all the relationships the same way. Next, I had SQL Server 2000 and had the tool reverse engineer the SQL for this new database with all the relationships.
My next step was to run this newly created SQL script on my local box with SQL Server 2005 (SMSS). What I did was create the database first and then assign the users to the new database. From here, I moved to running the sql script. While it was not 100% perfect, it did create all the tables and relationships with no problems.
Now, I am trying to accomplish the same process one a development instance of SQL Server 2005, but the script fails. The data base error says the new database created does not exist. When we go back and look at the database within SMSS, it is GONE!
I am at a lost at how to replicate the database I have on my local box. Any suggestions would be greatly appreciated.
Russ
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..
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedThanks 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,
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
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?
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
Hi all,
I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.
Thanks,
Scott Chang
I have installed 2 SQL Server 2005 Express sample databases from 2 books, ASPnet 2.0 and ADOnet 2.0. The ASPNETDB.MDF was shown in App_Data and Database Explorer, but not in the SQL Server Management Studio Express. The AdoStepBy Step database created by a ConfigDB.exe was displayed in the Management Studio, but not in the App_Data, or Database Explorer.
Is this the way SQL Server 2005 runs the 2005 databases for SQL Server 2005 Express only? Or also in SQL Server 2005?
TIA,
Jeffrey
Hi all,
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
........................
etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Please help and advise.
Thanks,
Scott Chang
Hi there
I am working on Visual Web Developer Express Edition 2005. When I right click on database explorer to create an SQL server database then I always find the option " Create New SQL Server database " Disabled.
Can any one tell me how to enable that option please ?
Hi all,
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
Me.GroupBox1.SuspendLayout()
Me.groupBox2.SuspendLayout()
CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'GroupBox1
'
Me.GroupBox1.Controls.AddRange(New System.Windows.Forms.Control() {Me.labelPAF, Me.labelNbrPrices, Me.textBoxPAF, Me.UpdatePrices})
Me.GroupBox1.Location = New System.Drawing.Point(8, 8)
Me.GroupBox1.Name = "GroupBox1"
Me.GroupBox1.Size = New System.Drawing.Size(240, 112)
Me.GroupBox1.TabIndex = 9
Me.GroupBox1.TabStop = False
'
'labelPAF
'
Me.labelPAF.Location = New System.Drawing.Point(8, 16)
Me.labelPAF.Name = "labelPAF"
Me.labelPAF.Size = New System.Drawing.Size(112, 32)
Me.labelPAF.TabIndex = 2
Me.labelPAF.Text = "Enter Price Adjustment Factor"
'
'labelNbrPrices
'
Me.labelNbrPrices.Location = New System.Drawing.Point(8, 80)
Me.labelNbrPrices.Name = "labelNbrPrices"
Me.labelNbrPrices.Size = New System.Drawing.Size(216, 16)
Me.labelNbrPrices.TabIndex = 5
'
'textBoxPAF
'
Me.textBoxPAF.Location = New System.Drawing.Point(120, 16)
Me.textBoxPAF.Name = "textBoxPAF"
Me.textBoxPAF.TabIndex = 0
Me.textBoxPAF.Text = ""
'
'UpdatePrices
'
Me.UpdatePrices.Location = New System.Drawing.Point(8, 48)
Me.UpdatePrices.Name = "UpdatePrices"
Me.UpdatePrices.Size = New System.Drawing.Size(88, 23)
Me.UpdatePrices.TabIndex = 6
Me.UpdatePrices.Text = "Update Prices"
'
'groupBox2
'
Me.groupBox2.Controls.AddRange(New System.Windows.Forms.Control() {Me.TenMostExpensive, Me.grdNorthwind})
<Part 1----To be continued due to the length of this post>
Hi all,
I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:
--User-defined stored procedure 'InsertCustomer'--
ALTER PROCEDURE dbo.InsertCustomer
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
INSERT INTO Customers
(
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
)
VALUES
(
@CustomerID,
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@Phone,
@Fax
)
=================================================
In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code:
--Form_Kimmel.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@CustomerID", "PAULK")
command.Parameters.Add("@CompanyName", "Pauly's Bar")
command.Parameters.Add("@ContactName", "Paul Kimmel")
command.Parameters.Add("@ContactTitle", "The Fat Man")
command.Parameters.Add("@Address", "31025 La Jolla")
command.Parameters.Add("@City", "Inglewoog")
command.Parameters.Add("@Region", "CA")
command.Parameters.Add("@Counrty", "USA")
command.Parameters.Add("@PostalCode", "90425")
command.Parameters.Add("@Phone", "(415) 555-1234")
command.Parameters.Add("@Fax", "(415 555-1235")
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
Thanks in advance,
Scott Chang
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
How do I move a table (test1) to a physical file group, and the table(test1) indexes to another file group. One clustered index and 3 nonclustered indexes. Thanks!!!!
View 4 Replies View RelatedHello List,
I've a customer table and It has two indexes only. But when I view from the query analyser it list ten indexes with the following name:
"_WA_Sys_status_01EAB64E"
I do not know, what does the above index does and i remember, I did not created that. When trying to drop that, It says, Its not an index. I dont know, what to do. Would somebody please advice me on this.
Thanks,
Hi,
Can someone point me to where I can find out if an SQL server table has an index ? I was in enterprise manager and brought up properties for any given table but was unable to find the information.
Thanks,
Jeff
Hi,
Im trying to connect IBM U2 XML Database using my ms.net Server explorer.but server not itself getting detected.
Prerequisites :i have installed the following things in my system
1. U2 on my machine and created databaes and tables.
2. IBM Database Add-ins for Vs.net 2005
below are the following steps im trying to connect IBM U2 Xml Database
1.Server Explorer ->Addconnection
Datasource : IBM DB2,IDS and U2 servers
2.in server tab its not showing my server which created using U2
Can any one please guide me in this issue
In SQL Visual Basic 2005 Express Edition, I was wondering if it is possible in the Add Connection dialog under Advanced settings to change the Data Source SQL server from the local .sqlexpress to a network SQL Express server. It seems as though the option is locked. I am in a doman, and using Windows Authentication. I also have TCP/IP enabled on the SQL server, and no firewall restrictions for the SQL service. Also, my account permissions should also allow me to do this.
John55616
Is there any way to know which ones are the biggest (In Mb) tables and indexes in my database?
thanks
Hi,
I have 2 tables with this design: one has ArticleID as primary key and multiple other fields and one has GroupID as primary key and multiple other fields. Each article can belong in multiple groups so I created a new table called articleGroups with only 2 fields: ArticleID and GroupID to show the groups associated with each article. There is a relation between this table and each of the main 2 tables. My question is, in the articleGroup table, does it make any sense to create an Index on ArticleID, GroupID or both? Since the group is needed for each article the Groups will always be queried everytime the article is queried. So, I am not sure if an index is needed?
Thank you,
Regards,
Iulian