|
Generally it is best to have a clustered index defined on every SQL Server table - otherwise it is treated as a heap. By default SQL-Server creates clustered indexes for primary keys. The engine contains a specific optimisation that makes it efficient to have a clustered index that is based upon an incrementing identity column.
A clustered index contains the table's data in its leaf nodes, so the records are physically sorted - and so is ideal for "range" scans. The following scenario takes advantage of this:- A "Customer" table has a clustered primary key on "CustId".
- An "Invoice" table has a non-clustered primary key on "InvoiceId", and a unique clustered index on "CustId, InvoiceId".
It is now very efficient to join between the Customer and Invoice tables.
Another technique that may prove useful, is to define a non-clustered index, but include additional columns that you would not normally be searching upon. SQL-Server will automatically use the data from the index (and not attempt to read the table data) if all of the information it requires for a query is located in a suitable index. This is called "covering".
http://www.sql-server-performance.com/tips_performance.asp[^] contains a bunch of articles and tips that you may find useful.
|
|
|
|
|
Good points, but the OP never mentioned SQL Server . I'm not an expert in other databases, so I hope you can tell me is this the same with other engines?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi Pete
I know that the following engines have clustered indexes:- Sybase (as you would expect because of SQL-Server's heritage).
- Informix had them back in 87.
- Oracle has Index Organized Tables (IOTs), which amounts to the same thing.
All of these conform broadly to the guidelines I provided earlier. If you google for "Clustered Index" then SQL-Server gives your the most hits.
The "covered" index thing has been supported by most high-end engines for many years. SQL Server 2005 has a new "include" clause for non-clustered indexes that is well worth checking-out. There are circumstances where this has better query performance than a clustered index.
On small databases there isn't much to be gained - so most of the lower league engines (MS-Access, etc) do not seem to support the concept. Interestingly, it looks like MySql supports them.
Regards
Andy
|
|
|
|
|
Andy
Thanks for that. Guess which database I specialise in . It's always good to find out how things work from somebody who knows about them.
Many thanks.
Pete
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
anujose wrote: Which index is good to apply.clustered or nonclustered?
If you are using SQL Server (and I presume any other type of database) you can only have one clustered index. A clustered index is where the actual rows are sorted in the order of the index. A non-clustered index means that a separate index is set up that points to the real rows.
|
|
|
|
|
Dear All,
I tried to get the views that are defined using a particular table name. Can any one give me the query for that? I already tried with user_views in the following way.
select view_name from user_views where text like '%tablename%';
The problem here is that the text column datatype is LONG. So I am getting inconsistent datatype err.
Thanks
Surendran
|
|
|
|
|
Hello,
I am attempting to create an autocomputed column with persisted values. The column's value is defined by this function:
CREATE FUNCTION [dbo].[GetMinsFromSStart]
(
@DayOfWeekID tinyint,
@Hour tinyint,
@Minute tinyint,
@Second tinyint
)
returns smallint
as
begin
declare @MinsFromSessionStart smallint;
set @MinsFromSessionStart = 420 + (@DayOfWeekID - 1) * 1440 + @Hour * 60 + @Minute;
if (@Second > 0)
set @MinsFromSessionStart = @MinsFromSessionStart + 1;
if (@MinsFromSessionStart > 0)
return @MinsFromSessionStart;
else if (@MinsFromSessionStart < 0)
begin
set @MinsFromSessionStart = 10080 + @MinsFromSessionStart;
return @MinsFromSessionStart;
end
return 10080;
end
Unfortunately, I am getting this error:
'Tick' table
- Unable to modify table.
Computed column 'MinsFromSStart' in table 'Tmp_Tick' cannot be persisted because the column is non-deterministic.
Why is the above function considered non-deterministic?
Thank you for any input,
Michal Kreslik
|
|
|
|
|
Your table has a computed column. You have not included the code for the computed column so I can't say.
Some functions are non-deterministic. There is a list of non-deterministic functions in the Books-on-line. For example GETDATE() is non-deterministic because, given the same inputs, the output can be different.
|
|
|
|
|
The code for the computed column is simply using the above function:
dbo.GetMinsFromSStart([DayOfWeekID], [Hour], [Minute], [Second])
I think there's nothing non-deterministic in that.
Michal
|
|
|
|
|
This link[^] might help. I'm going out on a limb here and guessing that since your function has three different return statements in it, that is making it non-deterministic. Also I'm more used to using 'end if;' to close off any if conditions, but your code has none. Are you sure it is logically what you are after?
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Hello, Chris,
thanks for the link - but I have already read these guidelines before posting a question here.
Also, quite naturally, I have been testing my function whether it's returning the correct results according to the intended logic: and it does. Just for info, the logic behind this function is to return the number of minutes elapsed from the start of Forex trading session. The first minute of the forex trading session starts every week on Sunday at 17:00:01 EST. The inputs are DayOfWeek index (0 for Sunday thru 6 for Saturday) and hour, minute & second values.
The three different return statements are there because it makes the function work faster (which is a concern as I'm running that on an OLAP db with 255 million+ rows). The first condition is true for most input sets, so the function doesn't have to go thru all of the other logical branches.
I'm certain my function complies to the Microsoft definition of a "deterministic function":
Determinism
Deterministic functions always return the same result any time they are called with a
specific set of input values and given the same state of the database. Nondeterministic
functions may return different results each time they are called with a specific set of input
values even if the database state that they access remains the same.
Also, there's no trace of "endif" keyword in the T-SQL reference for IF..ELSE:
http://msdn2.microsoft.com/en-us/library/ms182717.aspx
So where might be the problem?
Thanks for help,
Michal
|
|
|
|
|
I have now written a simple function that returns the higher value of the two:
CREATE FUNCTION [dbo].[GetHigherValue]
(
@Value1 int,
@Value2 int
)
RETURNS int
AS
BEGIN
declare @ReturnVal int;
if (@Value1 > @Value2)
set @ReturnVal = @Value1;
else
set @ReturnVal = @Value2;
return @ReturnVal;
END
There's only one return statement in the above function and it's by all means deterministic, but I'm stil getting the same "non-deterministic" error when I attempt to use it in the computed column specification.
Any ideas?
Thanks,
Michal
|
|
|
|
|
It seems like every UDF (user-defined funcion) is by default non-deterministic.
Is there any way to change this?
Thanks,
Michal
|
|
|
|
|
Ok, guys,
so the correct answer is that it was not created with the "WITH SCHEMABINDING" option.
Now it works ok.
Michal
|
|
|
|
|
Hi experts.
I'm trying to upload binary file into an oracle blob field. I retrieve the binary file from an XML document loaded in memory. I'll try to explain this in detail:
1) A page builds an XMLDocument with client code. In a node of this document, it sets the type to binary and attach a local filesystem document. Then, assigns the value to an hidden input field and sends it to itself
2) This page receives the post data, selects the binary content of the xml node and try to insert the binary data into the blob field (server side code).
I show you the code of this two parts:
1) This part works propertly (I only shows it for if it helps you)
function creaObjecte()
{
// create ADO-stream Object
var ado_stream = new ActiveXObject("ADODB.Stream");
// create XML document with default header and primary node
var xml_dom = new ActiveXObject("MSXML2.DOMDocument");
xml_dom.loadXML('<?xml version="1.0" ?> <root/>');
// specify namespaces datatypes
xml_dom.documentElement.setAttribute("xmlns:dt", "urn:schemas-microsoft-com:datatypes");
// create a new node and set binary content
var l_node1 = xml_dom.createElement("file1");
l_node1.dataType = "bin.base64";
// open stream object and read source file
ado_stream.Type = 1; // 1=adTypeBinary
ado_stream.Open();
ado_stream.LoadFromFile("C:\\TEMP\\b.pdf");
// store file content into XML node
l_node1.nodeTypedValue = ado_stream.Read(-1); // -1=adReadAll
ado_stream.Close();
xml_dom.documentElement.appendChild(l_node1);
document.getElementById("p_arxiu").value = xml_dom.xml;
}
2) I'm using this code. It inserts data into the blob, but doesn't inserts the binary data correctly (I use a pl/sql procedure for download the binary fields for the moment)
Protected Sub btnenviar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnenviar.Click
' Cadena de connexió a oracle
'Dim connectionString As String = "Data Source=AJSTJUST; Password=ajstjust;User ID=AJSTJUST"
Dim connectionString As String = "Data Source=AJSTJUSTCREAF; Password=sipan_mact;User ID=sipan_mactuacions"
' Recuperem l'arxiu binari del node que toca
Dim nodeBinari As XmlNode
Dim xmlHidden As XmlDocument
xmlHidden = New XmlDocument()
xmlHidden.LoadXml(arxiuBinari)
' Seleccionem el node que conté el binari
nodeBinari = xmlHidden.DocumentElement.SelectSingleNode("file1")
If (nodeBinari Is Nothing) Then
Response.Write("No s'ha trobat el node
")
Exit Sub
End If
' Passem el contingut del node a format binari
Dim binariFinal() As Byte = Convert.FromBase64String(nodeBinari.InnerText)
Dim queryString As String = _
"INSERT INTO DEMO (ID,THEBLOB) VALUES (1,'" & binariFinal(1) & "')"
Using connection As New OracleConnection(connectionString)
Dim command As New OracleCommand(queryString)
command.Connection = connection
Try
connection.Open()
command.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.Message)
End Try
End Using
End Sub
I think the problem is in this little piece of code:
' Passem el contingut del node a format binari
Dim binariFinal() As Byte = Convert.FromBase64String(nodeBinari.InnerText)
Dim queryString As String = _
"INSERT INTO DEMO (ID,THEBLOB) VALUES (1,'" & binariFinal(1) & "')"
But I'm newbie in .NET and I don't know how to do this.
Any help would be aprreciated. Thanks.
|
|
|
|
|
Hello
I´m working on a project that implements a SQL server 2005 DB as the main DB. Users should be able to extract an given project from the main server DB and "copy" it to there own local machine and work on it there (offline). When they return to work, they should merge it with the main DB.
And I hoped it would be possible to show conflicts through vs.NET so that the user can solve them.
I´m wondering if anyone out there has ever implementet a solution with Merge replication from SQL Server 2005 in Vs.Net 2005.
And if this could be the solution to my problem?
Thanx 
Spaz
|
|
|
|
|
please help me about
SQL: how many type of Store Procedure parameter ?
Pavan Pareta
|
|
|
|
|
Do you mean Input, Output and Return value, or do you mean the actual datatypes. If it's the datatypes, then there are as many types of parameter as there are allowed by the database engine in your tables. I'm not going to list them because they are database dependent.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
please help me about
SQL: diffrence between Clustered and Non Clustered Indexing
Pavan Pareta
|
|
|
|
|
This information is readily available through Google or reading the Books Online.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
Clustered means that the data is stored physically according to the index ordering.
Non-clustered means that there's only a logical mapping.
Thus, if you add/modify the clustered index on your table, the SQL has to reorder the data physically on the disk. If you add/modify a non-clustered index, the data remains where it is and some internal mapping table is added to the database.
Michal
|
|
|
|
|
|
could someone please suggest to me a query i can run on the query analyzer to retrieve logical file name (physical file location) of a database?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
Thank u very much Dear for your smart reply ... !!!!
![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
