sql server and tuning with indexes

clustered indexes ~ only one per table; for tables with PK/ID SQL Server defaults to creating a clustered index on that column; designer can change that, however, by de-selecting ‘clustered index’ checkbox. its possible for a column that’s NOT the ID/PK to have a clustered index … and then the ID/FK column becomes a unique index. the structure of the index = the physical order of data in the database.

non-clustered ~ can have up to 249 non-clustered indexes on each table; when a table already has a clustered index, when creating a new index, SQL Server defaults to creating a non-clustered index. functions similarly to the index of a book.

unique ~ columns indexed on a unique index cannot have duplicate values. not really an index, but a property. so, clustered and non-clustered indexes can be unique. by default, any column with a PK/ID key has a unique clustered index.

where to build indexes ~
on columns used in these queries, and frequently accessed by WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses; only add indexes if you know that they will be used by the queries run against the table.

MS-SQL Server and database replication

so … to get what this topic’s all about, think of your public library. you’ve visited the library, right? it has multiple locations, right? ok … i’m gonna assume affirmative … assume you’re not a complete neanderthal. anyyyyywayyyy … think library. and searching for your favourite book or DVD or whatever. how’d they do it? how’d they keep it all sync’d? i mean … all those books … spread across town in all those different book depositories we call libraries. how’d they keep track of what books comes … what books go … where a book’s located?

database … and … data REPLICATION. that’s how it happens. it can take place in virtual real time or in static time, and updating of replicated data can occur immediately or in a queued process. so … let’s get to the official-type stuff. WTF is replication? essentially, its the process of distributing a database’s data and database objects to other database engines that exist across an organization or enterprise. clearly, the benefits seem obvious: (1) flexibility with respect to data manipulation, sharing and collaboration; (2) concurrent use of data~which likely leads to increased efficiency, scope, productivity of said enterprise or organization; (3) facilitating ease of mobile users.

how does replication work? to understand this … we must understand the paradigm used to discuss replication theory: the publisher~ distributor~ subscriber paradigm. publisher refers to the source server … subscriber refers to the receiving server … distributor refers to the system database, which can reside on its own, on the subscriber side or on the publisher side. one-to-one OR one-to-many can describe the relationship between the publisher and subscriber.

check out the three replication models. (1) snapshot – just like it sounds ~ a snapshot of data @ a particular moment in time; no updates; (2) transactional – real-time; transactions stored on distributor and send to subscriber; can replicate entire table or filter; (3) merge – most complex; offers most flexibility and autonomy; connection not required for update … just to merge update data (for syncing dbase). associated with conflicts.

so … how to choose which model? how to tackle replication? well, start by considering the following: does the database have a compound primary key? do we know the when~what~where of rec’d publications? what level of transactional consistency do we require? what about latency? i.e. how long does updated data wait to get replicated/received by subscribers? latency decreases network speed and increases congestion … so less is more. and, then, what about site autonomy? i.e. do sites cascade from one another? or do they exist reasonably independently of one another? and what of security? system/server performance? how will data get updated? is there a schedule? what of data source(s) availability? and the subscriber’s capacity to update data? does the replication or update require a 2PC process? if so … preparation and commitment of servers must be anticipated (to avoid data conflicts, transaction inconsistencies).

methods

instructions … distinct sequences of logic that an object can manipulate in order to perform its function, carry out its service, communicate with users and other program objects. methods can only have access to data known to their particular object, and provide a mechanism for accessing (for both reading and writing) the private data stored in an object or a class..

in programming code, methods consist of a sequence of statements required to perform an action, a set of input parameters to customize those actions, and possibly an output value (called return value) of some kind. constructors, accessors, static (i.e. class-level) methods, instance methods, and destructors all play a role in object programming.

source code: a walk-thru

//2: parameter declaration~specifies the information a method is given when it is executed

//3: method call~calls the method println (), which belongs to the object known as System.out, and which prints a line of text

//4: variable declaration~declares a variable called proverb, and assigns the string “Practise makes perfect!” to it

//5: method call~prints the string referred to by the variable proverb to the terminal window

//6: another method call~calls the method length() on the string object to determine how many characters the string contains

method declarations~a sequence of statements describing the actions the program and its objects must perform.

class declaration~a language construct to define a class; methods define classes by describing their actions

variables~named locations in the computer’s memory which hold values during program execution. methods often use variables told hold intermediate results. when we assign a value to a variable, we store the value in variable so that the value can get later referenced, via the variable.

naming conventions

classes ~ nouns, in mixed case with the first letter of each internal word capitalized. Try to keep your class names simple and descriptive. Use whole words-avoid acronyms and abbreviations (unless the abbreviation is much more widely used than the long form
interfaces ~ capitalized like class names.
methods ~ verbs, in mixed case with the first letter lowercase, with the first letter of each internal word capitalized.
constants ~ upper case letters and words separated by underscore
variables ~ mixed case with a lowercase first letter. Internal words start with capital letters. Variable names should not start with underscore _ or dollar sign $ characters, even though both are allowed. Variable names should be short yet meaningful. The choice of a variable name should be mnemonic- that is, designed to indicate to the casual observer the intent of its use. One-character variable names should be avoided except for temporary “throwaway” variables. Common names for temporary variables are i, j, k, m, and n for integers; c, d, and e for characters.

classes and encapsulation

Encapsulation refers to the design concept that each object carries its own data and provides a set of services that are involved in calling the objects methods. objects provide services by sending and receiving messages from other objects. encapsulation means users can access an object’s services and functionality, without having ready access to the object’s underlying data itself. encapsulation facilitates software design and development using a ‘block method’ as well as object reuse, information hiding, and control over each object’s ability to view other objects.

classes facilitate the encapsulation process, by defining object program logic and data fields. essentially, classes definitions describe the structure of executing objects ~ that is, they act as templates for the creation of new instances (known in the jargon as ‘instantiation’ ) of the class object. methods that exist at the object level, as opposed to the instance level, can access the data of all object instances in the class. methods that exist at the level of an object’s instance can only access that particular data. the goal of object-oriented programing becomes achieving a balance between the degree of linkages among the various classes of any program and the degree of consistency within each individual class.

object-oriented programming: a primer

a programming language model, object oriented programming focusses on “objects” rather than “actions” and data rather than logic. historically, programming has centred itself around a procedural focus: viewing a program as logical procedure that takes input data, processes it, and produces output data. and, then, viewing the programming challenge writing the logic, as opposed to defining the data.

object-oriented programming turns things on their side by turniing the emphasis of software and software programing from intense focus on logical procedures to intense focus on the programming objects we wish to manipulate with said logical procedures. so, programmers speak of attributes, methods and triggers, where previously they spoke of input/output and procedural logic.

for programmer, objects, their states, actions and the events (the word ‘trigger’ pops up here) that affects these states and actions become the focus of design and development. the notions of state and action seem intuitive enough that i will omit explaining them. however … what do we mean by “event” …. ?

the term “event(s)” specifically refers to events that affect system function, as opposed to those that describe a transaction that’s part of system function – i.e. ‘customer wanting to buy the merchandise’ describes the event, and not ‘customer provides his credit card’ — (its part of the purchase transaction, triggering by the initial purchase request). programmers work with temporal, state and external events.

i’m mantissa …. & i’m a GEEK

um … yeah. i dunno, really, what this blog’s all about. it looks like its gonna be an expression of my nerdy side. yes, well, now i let the cat outta the bag – i am a self professed nerd-geek. among other things i am addicted to CSS and HTML/XHTML.

its, sadly, not uncommon for me to sit in front of my ibook for 3, 4, 5 hours even, tinkering with this or that in a blogger template. i have always thought the blogger-provided templates hopelessly inadequate. fortunately blogger has always made it easy to customize … making … the sky the limit. and fueling yet another addiction. uh-huh – as if any of us needed anything more addictions in our lives.

so this blogs sort of an expression of the GEEK in me. (~a GEEK? who knew?) all the shit i’ve learned about the internet … html … css, blah blah i’ve learned just by blogging here for long enough. i suppose this blog allows me to indulge that insatiable urge i have to tinker with CSS and (X)HTML. i suppose, in the end, blogging really equals narcissism, doesn’t it? well, i hope this blog will not just be me playing ‘narcisse’ … i hope that someone, somewhere will stumble upon this little rag and find it entertaining, or helpful, or something like that. or maybe just the designing and writing it, for me, raises my learning curve, if no one f inds this place.

so, meet your hostess: me: a former nurse, a former speech writer, a programmer/web developer student, an amateur graphic artist, a functioning junkie of sorts (don’t we all have so many addictions?), and a blogger. i have immersed myself in the world of computers and software since DOS 3.x was the current OS. (that makes me feel old …. harumphhfff!) note – this is BEFORE the era of the GUI! yikes – all those command prompts to remember! well, back then i could not imagine the beauty of MAC …. OSX … GUI has taken over the planet – yeaay!

and so here am i, making sense of it all. my current fascinations? presently learning C# programming language in my studies. me HATES C#!!! lemme just say ‘C is for cookie and that’s good enough for me ….’ C#~grrrrrrrr. i spend my spare time learning the CSS/(X)HTML stuff and combing through, picking about blogger templates to see hacking and customization possibilities. these days, trying to get more daring and creative with my templates – we’ll see. also i love surfing the web for kewl website and then viewing their source codes to see how they did it.

as i step further and further into the world of IT professionals, it amuses me, the uncanny similarity between the pedagogy of nursing and IT. assimilate some basic concepts in class; and then its all kinesthetic from there. learn to do by doing – its the only way. and … that’s what i should be doing right now.