CREATE INDEX
-
I was looking at the documentation for CREATE INDEX in SQL: https://www.w3schools.com/sql/sql_create_index.asp
CREATE INDEX index_name
ON table_name (column1, column2, ...);I don't understand when you would create it for multiple columns. Let's say you did this:
CREATE INDEX idx_pname ON Persons (LastName, FirstName);
Does that create one index on two columns? Or, is that the same as creating two indexes? For instance, does that statement create two hashtables? Or one hashtable? Thank you.
-
I was looking at the documentation for CREATE INDEX in SQL: https://www.w3schools.com/sql/sql_create_index.asp
CREATE INDEX index_name
ON table_name (column1, column2, ...);I don't understand when you would create it for multiple columns. Let's say you did this:
CREATE INDEX idx_pname ON Persons (LastName, FirstName);
Does that create one index on two columns? Or, is that the same as creating two indexes? For instance, does that statement create two hashtables? Or one hashtable? Thank you.
See [CREATE INDEX (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver16#syntax)
-
I was looking at the documentation for CREATE INDEX in SQL: https://www.w3schools.com/sql/sql_create_index.asp
CREATE INDEX index_name
ON table_name (column1, column2, ...);I don't understand when you would create it for multiple columns. Let's say you did this:
CREATE INDEX idx_pname ON Persons (LastName, FirstName);
Does that create one index on two columns? Or, is that the same as creating two indexes? For instance, does that statement create two hashtables? Or one hashtable? Thank you.
One index. First column is most significant, second is then used to reduce it further when doing searches. I don't think you should consider it as a 'hash table'. At a minimum I doubt any relational database uses a hash, in general, to fully implement an index. I suspect hash tables are not an optimal solution for file based storage. A quick google suggests SQL Server specifically supports that type but for a memory table (so not a normal table.) I don't want to do the deep dive research to actually figure out what they might use for db indexes. But you can read up on what Clustered Indexes are. Although not directly related the one example I know without research is the comparison of doing sorting. Binary Sort is for memory. Heap Sort is for file based processing.
-
I was looking at the documentation for CREATE INDEX in SQL: https://www.w3schools.com/sql/sql_create_index.asp
CREATE INDEX index_name
ON table_name (column1, column2, ...);I don't understand when you would create it for multiple columns. Let's say you did this:
CREATE INDEX idx_pname ON Persons (LastName, FirstName);
Does that create one index on two columns? Or, is that the same as creating two indexes? For instance, does that statement create two hashtables? Or one hashtable? Thank you.
Copy/paste from the answer to the question that was posted before yours;
Quote:
That means the index is sorted on col1, then col2. Meaning, col1 will be located first, then most rows following with the same col1 will be sorted on col2.
Meaning, in a list of users, while random stored in the table, would have an index that orders it as such; Lastname, Firstname Doe, Jane Doe, John Vluggen, Albert Vluggen, Eddy Vluggen, Zack If you search it, you go "where lastname = bla (col1 from index) AND firstname = bla2 (col2 from index)". This way, it will first locate the segment for "Vluggen", and only has to scan until it finds "Eddy" within that segment. That's what the DB is optimized to do.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.