Thanks again. I believe the term I was looking for is "offrow pages". Here's a recent (Feb 2018) link that talks about it briefly then takes the undocumented DBCC IND command really deep into the RowID and beyond SQL Server Row Data Linking to Off Row Data[^] in case you're interested. I enjoyed it but was a little got lost :)

Thanks for the article reference, it is good reading. It is great background and wonderfully answers "what is a page split." I do wonder, however, how much of that has changed in SSrv 2008+. I thought, for example, that VarChar columns in SQL Server can be moved off to different storage location to prevent splits due a column changing its size. (row overflow? or is that just for blobs?) I didn't find something to corroborate my thought when I did a quick search, so if someone could let me know whether I'm on/off base in the previous paragraph it would be great. Just specifically about VarChar (not text/blob; that's a different world I'm rarely in).

One has twice the indices... well duh. I should have thought of that... but I didn't :laugh: Thanks for sticking with me until I got it!

Just so you know (in the event that I ask a question here again) those inserts would be against the same database server which supports thousands of real-time users. I was experimenting because it seemed needlessly inefficient to have a clustered char(36) as the key. :wtf: Turns out that all my questions may be moot (aside from my own learning) :sigh: For review exec sp_helpindex <table name> gives the following description

table #1
clustered, unique, primary key located on PRIMARY guid column

table #2
clustered located on PRIMARY the int column
nonclustered, unique, primary key located on PRIMARY guid column

There is no detectable difference on insert speed when table size is <100k. There is a difference, but it seems trivial, when the table contains more than two million rows (14 second difference when inserting 300k rows in each table) P.S. Love the magnetic tape reference. P.P.S. > a clustered table with 300 columns and a semi-random natural key Yikes!

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)

For SQL Server Management Studio (SSMS) you can go to Tools | Options Within Options: Query Results | SQL Server | Results to Grid When you can select a row, copy it and you get the headers (plus the selected rows). Unless, you only have one column or you have an empty result set - then it doesn't work. You can either (1) change your result to text [CTRL]+"D" and see the headings (also Tools | Options | Query Results | SQL Server | General, select Results to Grid) or (2) you can select the "Include the query in the result set" (though presumably you already have the query). Hope that helps, -Chris C.

> I believe you're mixing up indexes with keys. Perhaps - let me try again; maybe I didn't convey this well. The index isn't just for lookup speed, it is a unique index (same functionality as a unique constraint unless I'm missing something... I'm more of an 'app dba' than a 'real DBA') to enforce referential integrity because... referential integrity. The addition of the Identity field was due to my concern about page splits. The guid will remain the primary key for the table. The guid is generated within a vendor's application so there isn't much I can do about that at the moment. I know (or at least 'think') that fill factor is only applied at rebuild or creation. This troubled me since guids are random (in the sense that where a newly generated guid falls numerically in relation to an existing list of guids is a random position). Inserts will fill up any fill factor <100 and then splits start (and the splits are equivalent to fill factor = 100 as the table grows). So I came up with the idea of a Clustered Identity as the 'physical sorting field' (numerically increasing won't generate page split) because it seems a better idea than (1) guid inserts causing page splits, and (2) better than making the table a heap (without a clustered index). Someone mentioned the volume being low. Yes, last month was 3,000 but this will be expanded from 1 to 5 categories. This could cause 3,100 entries next month or 31,000 entries. I don't have the time of day of the inserts but it is all first shift and (I think) mostly at the start of the month. If I can cause less work for the db, it feels like I should. One new piece of information that I'll share: testing this I don't see as much of a negative impact from a pk clustered guid as I expected.

Good question... 3rd party software is the reason.

My original question was, "What should I name the clustered index column which isn't the primary key?" but then I started putting in why I was doing it, and changed the subject to include 'is this a good idea?' I have a table and the primary key is a GUID, which is generated from vendor code so I cannot do what I would normally do: create an int for the clustered index and use that as the pk. My idea is to create the table like this:

CREATE TABLE [dbo].[table_name](
[need_a_good_name_here] [int] IDENTITY(-1,-1) NOT NULL, -- Adding this field
[guid] [char](36) NOT NULL PRIMARY KEY NONCLUSTERED,
...
) ON [PRIMARY]

CREATE CLUSTERED INDEX Ix_Table_Name ON [dbo].[table_name] ([need_a_good_name_here] ASC)

Feel free to comment on the following: 1. I don't want the guid to be the clustered index because there will be inserts. There were 3,000 inserts the first month, and I'm expanding from one category to four five. No way to know the distribution of inserts (except 1st shift / business days) and I don't know the frequency of the other categories. Fill factor == 100, not sure I can get it changed. 2. I have the identity starting at -1 and decreasing by 1 to make it obvious that it isn't the pk. Don't know how much this will help, but I do what I can. 3. I don't like the names ix_need_a_good_name_here, need_a_good_name_here_ix, need_a_good_name_here_id, because if I saw them I would assume that was the primary key. Suggestions? 4. Should I just not worry about splits? 5. Remember my mention of vendor code... I can't use SQL Server NEWSEQUENTIALID() which would make #1 a non-issue. All feedback appreciated!

In general SQL Server Central [^] no matter what your expertise / level. They have a log of good 'stairways' you can start with the Stairway to T-SQL DML – a SQL Server tutorial[^] DML == Data Manipulation Language (the aspect of the language dealing with the data: SELECT, INSERT, UPDATE and DELETE) I'd recommend you review all of DML, not just select... but that's my opinion. HTH, -Chris C.

I ran across something about reserve instances, it sounds relevant, but to be honest I'm more of a dev than a devOp person these days (at this job; everything subject to change!). Also, the 'better than per-second billing' in Azure where the round down to the nearest minute may be important if you're servers blip in and out of existance (345 seconds would be billed as just five minutes). Full disclosure I do have an Azure personal preference because it is so easy to spin up and use sql server and .net. HTH, -Chris C.

I worked with a Linux person that badmouthed Azure's Linux offering because it took several minutes to spin up a VM. He was working toward "servers as cattle not servers as pets" and wanted to be able to replace the prod environment quickly as soon as a problem was detected. I actually faced a MSFT rep and showed her the difference between spinning up a CentOS machine on Azure [~2 minutes] vs. Digital Ocean (I think) [45 seconds]. That's from memory, but the difference was between one and three minutes. Timing is just for the machine to be available - I used the interface, using powershell may be faster. (They never gave me the keys to AWS so that's all I had - don't know the AWS time.) She didn't have a good response - but she was new and that was a year ago so YMMV. Hope that's helpful, -Chris C.

Chris Quinn wrote:

I'm fascinated with the differences between "Ring a ring of roses" in the UK and "Ring around the Rosie" in the US.

American version I learned:

Ring around the rosie Pocket full of posie Ashes, ashes, We all fall down

First two lines: a ring around a red blister, which was filled with puss HTH, -Chris C.

> In the case of medical professional, ... when they want to actively "write" stuff, > they'll go back to their office, dock the tablet and use a real keyboard. they will dictate it into the device and someone in a displaced time zone will type it up and have it ready for them to review when they come in the next morning :-)

Agree with most of that. I've actually had more trouble printing in Chrome than IE8 or FF. The interface is great - but the results are iffy. Maybe it is because I'm still on XP or something my desktop people have done. I tried to print pages 3-5 of an article Thursday and ended up with 7 pages and only a few words on each page. I'll be on Win7 in a few months and will revisit.

Sorry I wasn't able to post this earlier - I lost connectivity then had to go out of town (where there's no internet except a borrowed computer I can read email on) We aren't even at feature detection, because we aren't doing some of the cool stuff yet (in public apps). All of this frustration is over 'almost pixel perfect' HTML5 CSS3 sites

Thanks, well said. > If they expect everything to look snappy in IE7 too, then you're screwed. That's where we were for this release, and what I'm trying to avoid in the future. For anyone that still needs to estimate what it would take a competant designer with a lot of CSS experience to make a site that looks good in Chrome/FF look pixel perfect in IE7 a good estimate is +100-150%. And, yes I mean multiply your IE7 estimate by 2 or 2.5 And, no I'm not the one that did it (I just did the web application).

I suspect you were kidding (and P.S. it did make me grin) IE6 isn't a problem as it is no longer patched by msft. Encouraging users to stay on IE6 would not be a public service. The current 'boat anchor around our neck' is IE7 as it needs its own full section of CSS support and I'm told is difficult to match up to modern browsers (with modern defined as IE9+, FF, Chrome) FWIW

> You should also prioritize in which order you fix problems. Very good point, that's a fight waiting to happen, I'll add that

I decided to post here instead of the web forum to get more opinions. My boss has asked me to come up with a 'browser support strategy' that covers both internal projects and external projects. The reason for this is mostly: in a just released a public web site we are getting feedback like "this doesn't look right on my Kindle Fire" there is too much white space at the top (or whatever). The problem is that we could chew up the rest of our careers responding to this if we aren't careful - currently the person in charge says 'we should support all mobile devices out there' and we have spent hours on stuff that (in my opinion) we shouldn't have. We are getting some loaner devices so we can at least see for ourselves what the problem is (rather than rely on an email description of what's wrong - that's an improvement. We have to support IE7 because of the number of users on it within our organization - I'd love to ditch it but people are only moving off about 0.5% per month and it has a 30% share of current page hits. All help is appreciated. I searched and haven't find anything relevant except a graded browser support idea that I found at yuilibrary.com. My current thought is to have categories like: A = we will fix it if something looks wrong, QA will test these browsers B = it only has to look roughly the same as tier A, we will only fix it if something is missing or doesn't work, QA does NOT test these browsers C = These are considered rare browser platforms and users should not expect them to work. If money is provided to support a specific broser in the category we can do it, but it will not be supported ongoing (if it works now and breaks in a month we will not fix the break). F = These are antiquated browsers which are not supported for any reason Do any of you have a browser list that you target support for? Any recommendations? (Aside from drop IE7 - we're working on that battle) Comments on how to word my ABCF scale and what should be in it are welcome also. Thanks, -Chris C. EDIT: Example of Tier "F" would be IE6, IE5.5, etc.)