Click here to Skip to main content
14,921,745 members
Home / Discussions / Database
   

Database

 
AnswerRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
Victor Nijegorodov21-Apr-18 3:34
MemberVictor Nijegorodov21-Apr-18 3:34 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
JChrisCompton23-Apr-18 3:00
MemberJChrisCompton23-Apr-18 3:00 
AnswerRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
Eddy Vluggen23-Apr-18 5:19
professionalEddy Vluggen23-Apr-18 5:19 
AnswerRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
Jörgen Andersson23-Apr-18 21:58
professionalJörgen Andersson23-Apr-18 21:58 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
JChrisCompton24-Apr-18 11:21
MemberJChrisCompton24-Apr-18 11:21 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
Eddy Vluggen25-Apr-18 0:41
professionalEddy Vluggen25-Apr-18 0:41 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
JChrisCompton25-Apr-18 4:40
MemberJChrisCompton25-Apr-18 4:40 
Yes, it says "The fill-factor option is provided for... [when] an index is created or rebuilt..."

"I think" was intended to mean "I think fill-factor has no influence except during create/rebuild".
The issue that concerns me is page splits.

From memory of a MSFT SQL Server Performance Tuning class I took five years ago, I think tables with a clustered index are a B+ tree with doubly linked leaf nodes. (class and school were long ago :–)

When an insert happens, and the bottom node of the tree is full, then a split happens.
When SSvr splits a node, my understanding is that it just makes a new node - the fill-factor doesn't enter in to it. If I specify my fill factor to be "only fill 1/3" it doesn't matter - a new node is created (instead of 2 extra nodes being created then each adjusted to 1/3 full). This is done without rebalancing, because it is the fastest solution.

Is my mental model right, or are there things in newer versions that makes 'real life' different?
(maybe some auto tuning feature I've missed)
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
Eddy Vluggen25-Apr-18 5:33
professionalEddy Vluggen25-Apr-18 5:33 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
JChrisCompton30-Apr-18 5:30
MemberJChrisCompton30-Apr-18 5:30 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
Eddy Vluggen30-Apr-18 5:58
professionalEddy Vluggen30-Apr-18 5:58 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
JChrisCompton30-Apr-18 7:35
MemberJChrisCompton30-Apr-18 7:35 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
Eddy Vluggen30-Apr-18 8:13
professionalEddy Vluggen30-Apr-18 8:13 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
Jörgen Andersson25-Apr-18 2:15
professionalJörgen Andersson25-Apr-18 2:15 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
JChrisCompton25-Apr-18 5:47
MemberJChrisCompton25-Apr-18 5:47 
GeneralRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
Jörgen Andersson25-Apr-18 10:49
professionalJörgen Andersson25-Apr-18 10:49 
PraiseRe: Naming my clustered index column which isn't the PK (and is a non-PK clustered Identity a good idea?) Pin
JChrisCompton26-Apr-18 4:17
MemberJChrisCompton26-Apr-18 4:17 
QuestionEXCEPTIONS INTO Equivalent for SQL Server Pin
User 1205041720-Apr-18 4:59
MemberUser 1205041720-Apr-18 4:59 
AnswerRe: EXCEPTIONS INTO Equivalent for SQL Server Pin
Eddy Vluggen23-Apr-18 5:21
professionalEddy Vluggen23-Apr-18 5:21 
GeneralRe: EXCEPTIONS INTO Equivalent for SQL Server Pin
User 1205041723-Apr-18 5:24
MemberUser 1205041723-Apr-18 5:24 
Questionhow to access temp table field in subquery? Pin
joost.versteegen11-Apr-18 21:43
Memberjoost.versteegen11-Apr-18 21:43 
AnswerRe: how to access temp table field in subquery? Pin
Victor Nijegorodov11-Apr-18 22:18
MemberVictor Nijegorodov11-Apr-18 22:18 
AnswerRe: how to access temp table field in subquery? Pin
Mycroft Holmes11-Apr-18 23:42
professionalMycroft Holmes11-Apr-18 23:42 
GeneralRe: how to access temp table field in subquery? Pin
joost.versteegen11-Apr-18 23:53
Memberjoost.versteegen11-Apr-18 23:53 
AnswerRe: how to access temp table field in subquery? Pin
Richard Deeming19-Apr-18 2:34
mveRichard Deeming19-Apr-18 2:34 
QuestionExporting Select statement values into csv with escape characters Pin
indian1439-Apr-18 12:16
Memberindian1439-Apr-18 12:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.