|
Can anyone give me some help regarding Creation of Indexes in SQL Server. I also need to know its advantages and most importantly, its difference when compared to Primary Key Constraints.
The name is Sandeep
|
|
|
|
|
Hello Sandeep,
Indexes are created for faster retrival of data. Its like searching a topic in a book with the help of index in book. Imagine if have to search the topic in a book without an index. It will be slower as you have to browse through each an every page to check for the topic.
Indexes are of two types
1) Clustered index
2) Non clustered index.
For more information about these two type index you can check books online of sql server
Everytime you create a primary key using enterprise manager, it by default creates a clustered index. But thats not mandatory. You can have a primary key without any index.
Its because its recommened to have a clustered index on a unique column.
I hope I answered your question.
please let me know incase of any query.
Rahul Goel
Microsoft Cerfied Solution Developer .Net
Contact me at : rahul.g2510@gmail.com
|
|
|
|
|
Hello Rahul,
Thnx for your reply. Let me tell you what I have understood by Indexes. Please verify my points.
Indexes should be only used in tables used for data retrieval and not in tables where frequent INSERT & UPDATE take place.
When an Index is created over a table, then there is no need for defining a PrimaryKey for that table.
Indexes are best suited for Views.
Are these correct?
The name is Sandeep
|
|
|
|
|
Xandip wrote: Indexes should be only used in tables used for data retrieval and not in tables where frequent INSERT & UPDATE take place.
When an Index is created over a table, then there is no need for defining a PrimaryKey for that table.
Indexes are best suited for Views.
No. Indexes can be used in tables which perform frequent insert/update/deletes. They are used to speed up retrieval of information, so if you need to select information from these tables you should consider whether or not they should be used.
A primary key is not intended to act as an index. Just because a primary key normally has an index associated with it does not mean that having an index on a table negates the need for a primary key. The PK is normally used to provide a mechanism to allow you to reference a row in another table. This is because the PK is unique, so it uniquely identifies a row.
Indexes can be used in views (DB dependent), but they are not best suited for them.
|
|
|
|
|
sometimes oracle gives error "unable to get oracle connection".
how to solve that?
vijaya
|
|
|
|
|
first, did you start the listener in database server?
goto controlPanel/services and start the listener of the concerned sid (databse).
from the client your installing forms,
use net8 easy config asstnt to create services or connectstrings.
check the availability of listener from comand prompt
using
tnsping < yourServiceName >
And then run your application.
Regards,
Satips. ![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
I have been reading and attempting to use a data table for a side project I am working on and I am looking at using a dataview in addition to the data table. I think I have the basics about both but if anyone can see any misconceptions I would appreciate you point them out.
The columns collection makes up the schema of the DataTable and the rows make up the actual data in the DataTable. A DataView is a bindable, customized view of a DataTable. You can create multiple DataView's off one DataTable, each one can contain different data sorted in different order. Does the first row actually, row(0), contain the row header (column name from the database)?
I have found BindingSource.Find to narrow my selection to a particular string in the DataTable. I am also wanting to make sure that I am doing this in the most efficient way, does anyone have a better way of narrowing results in a DataTable? I am under the impression that SQL statements cannot be used to query the DataTable. Please correct me if I am wrong.
Dan
|
|
|
|
|
Dear Web Owner,
My apologies for repeating the same thread on another section. As i am confused who are the best people can ans this ASP or SQL...
Thanks!!
Error Details...
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I have checked my conn string, Surface config set to local/remote, created alias aslo........WHAT NEXT PLEASE HELP
Thanks for your time...
Amit
Amit
|
|
|
|
|
I have answered it in the ASP.NET Forum. Pls check it out there.
Regards,
Satips.
|
|
|
|
|
Go to Configuration Manager for SQL Server 2005.
Check if Shared Memory, Named Pipes & TCp/IP for the instance are enabled or not.
Hopefully this should resolve ur error.
Regards
SG (sgg245@yahoo.co.in)
|
|
|
|
|
I'm feeling rather dim here--but how did Hibernate get its name? Why 'Hibernate'?
David Veeneman
www.veeneman.com
|
|
|
|
|
I assume your talking about the data access stuff here? 
It's called hibernate because it provides persistance i.e. when your computer goes to sleep/turns off, it'll remember stuff when it wakes up.
|
|
|
|
|
I have a number of triggers in an SQL Server 2000 database and I want to generate scripts for them. Using the mouse to select each and clicking "Script trigger as..." would be a serious pain in the wrist. Are there DDL or similar commands or a utility to do this?
|
|
|
|
|
You can right-click the database in Enterprise Manager, select All Tasks, Generate SQL Script. From there, click Show All. Then select the tables and views that the triggers are attached to and click Add to move them to the 'Objects to be scripted' box. On the Options tab, check Script Triggers.
That will get you the table definitions as well as the triggers. I don't know of a way to just get the triggers.
The contents of stored procedures, triggers etc are stored in the syscomments table.
|
|
|
|
|
I'll take a look at syscomments, thanks
|
|
|
|
|
Hi,
Can anybody tell what are the mail difference or advantages of sql server 2005 over sql server 2000.
regards
alex.
|
|
|
|
|
|
Have you heard of Google? There only about 1mil returns on the subject...
______________________
stuff + cats = awesome
|
|
|
|
|
I am having issues with the following code ever since I added a subquery to it. The error tells me that more than one value was returned from the subquery. The query should in fact retrieve more than one value in most instances so this is fine. Thing is it stops the query from working and I don't know why. My code is as follows in it's entirety. Thanks for any help
SELECT<br />
IH.InvoiceDate AS HistoryDate,<br />
'Part of Invoice' AS HistoryType,<br />
SI.SiteName AS HistoryBranch,<br />
('Invoice Number - ' + IH.InvoiceNumber + ' - ' + C.CustomerForeName + ' ' + C.CustomerSurname) AS HistoryReference,<br />
(<br />
SELECT TIP.InvoicePhoneIMEI <br />
FROM tblInvoicePhones TIP <br />
INNER JOIN tblInvoiceItems TII <br />
ON TIP.InvoiceItemID = TII.InvoiceItemID <br />
WHERE TII.InvoiceID = IH.InvoiceID<br />
) As HistoryIMEIs,<br />
(ST.StaffForeName + ' ' + ST.StaffSurname) AS HistoryUser<br />
FROM tblInvoiceHeader IH<br />
INNER JOIN tblInvoiceItems II<br />
ON II.InvoiceID = IH.InvoiceID<br />
INNER JOIN tblSites SI<br />
ON IH.SiteID = SI.SiteID<br />
INNER JOIN tblStaff ST<br />
ON IH.StaffID = ST.StaffID<br />
LEFT JOIN tblCustomers C<br />
ON IH.CustomerID = C.CustomerID<br />
WHERE II.StockID = '1750'<br />
AND IH.InvoiceDate >= 'January 1, 2007'<br />
AND IH.InvoiceDate <= 'July 2, 2007'<br />
AND IH.SiteID = '10'<br />
AND IH.InvoiceProForma = 0
|
|
|
|
|
The message relates to the subquery. In this example the subquery can only return one row. I would run the subquery only and see how many rows it is returning. If it returns more than one then you have a problem
Jon
|
|
|
|
|
As i mentioned in my original post the subquery should and does return more than one rows. I want for example the following to be returned.
12/10/2007
Sale
967676689768879, 23987298739872, 39278387298372, 3927328739872973
24/12/2007
Sale
8768676876876876, 87687678876876786, 9876987865765678
Where the large numbers are collected from the subquery. Is this not possible? How else can I go about getting information in this way?
|
|
|
|
|
No, You cant do it that way. The way your SQL is structured the subquery is in effect a single field. you cant just put several values into one field.
I see what you are trying to do but i dont think there is a way to do it with a single SQL statement (although i may be wrong about that).
|
|
|
|
|
Ok so you can't merge the fields in anyway. Does anyone else know?
|
|
|
|
|
Hi
You can write your sub-query in a separate function. (table-valued function). Using this function, you can join with the main query as given below.
Select Col1, Col2, dbo.Fn_SubQuery(param1,param2)
from Table1
Hope this helps.
See the example below
<br />
use northwind<br />
go<br />
create function dbo.Fn_OrdersProductID(@OrderID int)<br />
returns nvarchar(4000)<br />
as <br />
begin<br />
DECLARE @ProductID nvarchar(4000)<br />
set @ProductID = null <br />
SELECT @ProductID = COALESCE(@ProductID + ',','') + cast(ProductID as nvarchar)<br />
FROM [Order Details]<br />
WHERE OrderID = @OrderID<br />
<br />
RETURN @ProductID<br />
<br />
end<br />
<br />
-- run the foll. stmt after executing the above function<br />
-- SELECT OrderID,dbo.Fn_OrdersProductID(OrderID) from Orders
Apply the same for your query
-- modified at 7:08 Tuesday 3rd July, 2007
-- modified at 7:11 Tuesday 3rd July, 2007
Harini
|
|
|
|
|
what is the need of COALESCE
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
