Welcome to our blog post on SQL Server concepts and ideas. In this article, we will discuss some common questions and answers related to SQL Server. Whether you are a beginner or an experienced SQL Server user, this post will provide you with valuable insights.
TCP/IP Port for SQL Server
SQL Server runs on port 1433 by default. However, you can change this port number from the Network Utility TCP/IP properties on both the client and the server.
Clustered vs Non-clustered Index
A clustered index is a special type of index that determines the physical order of records in a table. It can only be created on one column and the leaf nodes of a clustered index contain the actual data pages.
On the other hand, a non-clustered index does not affect the physical order of the rows on disk. It can be created on multiple columns and the leaf nodes contain index rows, not the actual data pages.
Index Configurations
A table can have various index configurations:
- No indexes
- Clustered index
- Clustered index and multiple non-clustered indexes
- Multiple non-clustered indexes
Collation Sensitivity
Collation sensitivity refers to how the database treats different characters. There are different types of collation sensitivity:
- Case sensitivity (e.g., A and a)
- Accent sensitivity (e.g., a and á)
- Kana sensitivity (e.g., Hiragana and Katakana)
- Width sensitivity (e.g., half-width and full-width characters)
OLTP (Online Transaction Processing)
In OLTP systems, relational database design follows data modeling principles and ensures data integrity. The data is broken down into simple structures (tables) that satisfy normalization rules. This approach allows for efficient transaction processing.
Primary Key vs Unique Key
Both primary key and unique key enforce uniqueness on a column. However, there are some differences:
- A primary key creates a clustered index by default, while a unique key creates a non-clustered index.
- A primary key does not allow NULL values, but a unique key allows one NULL value.
DELETE vs TRUNCATE Commands
The DELETE command removes rows from a table based on a condition specified in the WHERE clause. On the other hand, the TRUNCATE command removes all rows from a table, resulting in an empty table.
TRUNCATE is faster and uses fewer system and transaction log resources compared to DELETE. TRUNCATE deallocates the data pages used by the table, while DELETE removes rows one at a time and records each deletion in the transaction log.
It’s important to note that TRUNCATE resets the identity column of the table, whereas DELETE does not.
We hope this article has provided you with a better understanding of SQL Server concepts. If you have any questions or thoughts, please let us know in the comments section.