Meta tags:
Headings (most frequently used words):
sql, services, server, editions, studio, data, service, and, management, microsoft, contents, history, supplemental, packages, architecture, storage, retrieval, programmability, see, also, references, further, reading, external, links, milestones, currently, mainstream, specialized, discontinued, buffer, concurrency, locking, native, client, snac, clr, agent, machine, learning, broker, replication, analysis, reporting, notification, integration, full, text, search, sqlcmd, visual, azure, business, intelligence, development,
Text of the page (most frequently used words):
the (371), sql (302), server (278), and (176), #microsoft (118), data (106), for (103), database (91), services (75), with (64), are (59), retrieved (58), can (54), edition (46), query (41), edit (40), which (40), studio (39), management (38), that (37), service (36), also (34), used (31), not (31), from (29), other (29), stored (29), 2007 (27), text (26), 2008 (26), windows (25), december (25), page (24), tools (24), search (23), system (23), index (22), 2005 (22), 2000 (22), includes (22), access (21), express (21), editions (21), net (19), version (19), using (18), client (18), visual (18), engine (18), reporting (18), replication (18), full (18), 2025 (17), software (17), memory (17), has (17), including (17), table (16), code (16), com (16), 2011 (16), analysis (16), support (16), available (15), released (15), databases (15), use (14), different (14), november (14), 2012 (14), storage (14), development (14), features (14), but (14), main (14), either (14), relational (13), azure (13), web (13), notification (13), row (13), types (13), september (13), pages (13), allows (13), queries (13), tables (13), enterprise (13), same (13), this (12), was (12), all (12), linux (12), business (12), intelligence (12), release (12), article (12), any (12), such (12), when (12), process (12), developer (11), basic (11), integration (11), clr (11), transaction (11), based (11), broker (11), they (11), will (11), standard (11), procedures (11), multiple (11), platform (10), history (10), native (10), application (10), provides (10), into (10), more (10), files (10), supports (10), defined (10), some (10), wikipedia (9), network (9), control (9), applications (9), object (9), concurrency (9), objects (9), file (9), machine (9), processor (9), words (9), out (9), their (9), time (9), called (9), sybase (9), about (8), foundation (8), systems (8), fast (8), 2022 (8), managed (8), language (8), july (8), architecture (8), buffer (8), size (8), operations (8), user (8), create (8), both (8), versions (8), its (8), each (8), then (8), these (8), sequence (8), changes (8), while (8), one (8), read (8), rows (8), locks (8), lock (8), toggle (7), mobile (7), view (7), statement (7), corporation (7), learning (7), list (7), manager (7), compact (7), what (7), msdn (7), functionality (7), single (7), supported (7), performance (7), 2016 (7), new (7), created (7), well (7), adds (7), over (7), via (7), columns (7), than (7), only (7), message (7), include (7), limited (7), after (7), does (7), shared (7), written (6), analytics (6), press (6), framework (6), ado (6), core (6), log (6), column (6), comparison (6), isbn (6), inside (6), introducing (6), april (6), overview (6), 2019 (6), xml (6), been (6), users (6), results (6), indexes (6), interface (6), execute (6), run (6), set (6), like (6), itself (6), subscriber (6), publisher (6), across (6), being (6), replaced (6), add (5), languages (5), statistics (5), terms (5), may (5), august (5), articles (5), february (5), networks (5), 2018 (5), xbox (5), unit (5), power (5), programming (5), servers (5), script (5), processing (5), model (5), plan (5), procedure (5), key (5), original (5), information (5), mining (5), snapshot (5), 2010 (5), track (5), plans (5), sqlcmd (5), introduced (5), execution (5), word (5), two (5), indexer (5), binary (5), component (5), performed (5), subscribers (5), designed (5), made (5), result (5), update (5), type (5), messages (5), along (5), operation (5), enhancements (5), unlike (5), have (5), retrieval (5), updated (5), identified (5), locking (5), structure (5), clustered (5), extent (5), subsection (5), contents (4), commons (4), website (4), wikidata (4), category (4), statements (4), english (4), games (4), learn (4), source (4), team (4), api (4), small (4), others (4), functions (4), odbc (4), partition (4), value (4), external (4), links (4), further (4), reading (4), docs (4), querying (4), discontinued (4), archived (4), agent (4), product (4), localdb (4), 2017 (4), specific (4), later (4), tool (4), runs (4), select (4), gui (4), many (4), command (4), exposes (4), rank (4), example (4), case (4), match (4), capabilities (4), part (4), sent (4), writing (4), accessed (4), configured (4), might (4), instance (4), contain (4), packages (4), proprietary (4), tasks (4), them (4), however (4), executing (4), connection (4), nodes (4), resource (4), allow (4), extension (4), back (4), number (4), cached (4), concurrent (4), processors (4), exclusive (4), cache (4), span (4), free (4), large (4), views (4), pack (4), parallel (4), embedded (4), 1989 (4), hide (4), move (4), sidebar (4), under (3), additional (3), registered (3), non (3), wikimedia (3), license (3), official (3), link (3), cs1 (3), media (3), studios (3), linkedin (3), high (3), 2009 (3), internet (3), explorer (3), 2006 (3), store (3), transact (3), pro (3), installer (3), msde (3), device (3), kit (3), apis (3), project (3), legacy (3), image (3), integrity (3), related (3), optimization (3), optimizer (3), components (3), cloud (3), oriented (3), 7356 (3), introduction (3), settings (3), library (3), connect (3), cannot (3), provider (3), merge (3), transactional (3), preview (3), driver (3), choosing (3), march (3), warehouse (3), end (3), evaluation (3), download (3), updates (3), environment (3), reports (3), general (3), prior (3), known (3), write (3), scripts (3), feature (3), within (3), among (3), schema (3), provide (3), included (3), managing (3), work (3), primary (3), executed (3), through (3), line (3), parameters (3), fts (3), uses (3), linguistic (3), divided (3), processes (3), unstructured (3), filter (3), daemon (3), once (3), specialized (3), character (3), 1000 (3), needs (3), warehousing (3), first (3), mechanism (3), notifications (3), event (3), send (3), report (3), protocol (3), exposed (3), algorithm (3), copy (3), entire (3), between (3), subset (3), security (3), defines (3), own (3), scale (3), started (3), session (3), running (3), tabular (3), metadata (3), named (3), runtime (3), most (3), operating (3), invoked (3), values (3), associated (3), name (3), necessary (3), resources (3), decides (3), contains (3), join (3), would (3), programmability (3), old (3), ensure (3), clients (3), currently (3), disc (3), heap (3), allocation (3), computer (3), cluster (3), space (3), map (3), maximum (3), tds (3), datacenter (3), intended (3), appliance (3), mpp (3), current (3), marking (3), topic (2), developers (2), safety (2), contact (2), privacy (2), policy (2), you (2), organization (2), inc (2), rendered (2), last (2), 2026 (2), categories (2), url (2), maint (2), deprecated (2), archival (2), targeting (2), containing (2), potentially (2), dated (2), 2023 (2), american (2), short (2), description (2), zenimax (2), technologies (2), labs (2), pixel (2), skype (2), entertainment (2), npm (2), devices (2), havok (2), github (2), company (2), transfer (2), fine (2), clipchamp (2), activision (2), blizzard (2), united (2), states (2), 2004 (2), 1994 (2), 2002 (2), where (2), today (2), stores (2), groups (2), build (2), technet (2), typescript (2), powershell (2), vbscript (2), vba (2), start (2), open (2), office (2), 365 (2), dynamics (2), laptop (2), hardware (2), employee (2), corporate (2), scott (2), hood (2), ceo (2), satya (2), nadella (2), people (2), outline (2), active (2), testing (2), foxpro (2), linq (2), communication (2), entity (2), dynamic (2), directx (2), cli (2), profiler (2), sdk (2), app (2), designer (2), macro (2), normalization (2), models (2), jdbc (2), trigger (2), unique (2), null (2), concepts (2), document (2), wikibooks (2), klaus (2), delaney (2), kalen (2), programmer (2), retiring (2), method (2), ssas (2), 978 (2), homepage (2), 2013 (2), june (2), reference (2), documentation (2), streaminsight (2), 2021 (2), trial (2), limit (2), scalability (2), requirements (2), january (2), cite (2), references (2), see (2), developing (2), solutions (2), controls (2), projects (2), ide (2), availability (2), cross (2), export (2), perform (2), act (2), visually (2), analyze (2), optimize (2), since (2), schemas (2), onwards (2), were (2), comes (2), received (2), breaks (2), filters (2), noise (2), variants (2), inverted (2), computed (2), returned (2), interact (2), creates (2), ifilters (2), hosted (2), occur (2), monitors (2), exe (2), searched (2), matching (2), string (2), range (2), higher (2), given (2), near (2), special (2), operators (2), various (2), three (2), methods (2), recent (2), installed (2), rdl (2), pdf (2), olap (2), modes (2), underlying (2), clustering (2), series (2), trees (2), tracked (2), synchronized (2), synchronization (2), manually (2), pre (2), master (2), who (2), side (2), encryption (2), format (2), queue (2), implemented (2), program (2), usually (2), routes (2), consists (2), following (2), tcp (2), python (2), allowing (2), without (2), scheduling (2), backup (2), common (2), automatically (2), added (2), top (2), redirected (2), hosts (2), connections (2), context (2), classes (2), internal (2), sqlos (2), rather (2), deadlock (2), base (2), implements (2), asynchronous (2), sets (2), stream (2), snac (2), creating (2), monitoring (2), manage (2), processed (2), possible (2), linked (2), print (2), instructions (2), because (2), need (2), cost (2), whether (2), low (2), actually (2), split (2), faster (2), concurrently (2), retrieving (2), variant (2), due (2), requested (2), actions (2), there (2), yield (2), optimistic (2), similar (2), moved (2), respective (2), levels (2), granularity (2), per (2), basis (2), leaves (2), level (2), less (2), dmvs (2), threads (2), acquire (2), grants (2), another (2), pessimistic (2), held (2), wait (2), buffered (2), how (2), complete (2), checksum (2), physical (2), partitions (2), numbered (2), order (2), spread (2), according (2), tree (2), leaf (2), storing (2), indexed (2), allocated (2), holds (2), share (2), those (2), varchar (2), makes (2), addition (2), collection (2), strings (2), symmetric (2), rounding (2), round (2), down (2), layer (2), initially (2), developed (2), protocols (2), samples (2), supplemental (2), logical (2), retired (2), personal (2), desktop (2), optimized (2), terabytes (2), massively (2), continue (2), starting (2), limitations (2), must (2), workloads (2), presented (2), offering (2), workgroup (2), xvelocity (2), mainstream (2), rtm (2), ghz (2), onto (2), milestones (2), ago (2), appearance (2), upload (2), bahasa (2), account (2), donate (2), menu (2), cookie, conduct, legal, contacts, disclaimers, apply, site, agree, trademark, profit, creative, attribution, sharealike, parsoid, edited, utc, hidden, unfit, hatnote, templates, nonexistent, mdy, dates, matches, https, org, php, title, microsoft_sql_server, oldid, 1369402327, yupi, yammer, appnexus, xandr, xamarin, winternals, webtv, vxtreme, volometrix, vivaty, visio, vermeer, undead, twisted, tellme, takelessons, swiftkey, sunrise, atelier, simplygon, riskiq, revolution, rare, proclarity, play, powerset, playground, placeware, perceptive, pando, onfolio, obsidian, nuance, communications, nokia, ninja, theory, mojang, metaswitch, massive, incorporated, maluuba, lionhead, linkexchange, jellyfish, inxile, hotmail, heat, major, league, baseball, group, groove, greenbutton, great, plains, giant, forethought, firefly, fasa, farecast, double, danger, consumers, connectix, compulsion, colloquis, bungie, blue, ribbon, soundworks, beam, azyxxi, aquantive, altspacevr, altamira, acquisition, acompli, 6wunderkinder, acquisitions, ftc, shah, alcatel, lucent, commission, mikerowesoft, lindows, 2001, apple, litigation, vista, _nsakey, bob, iloo, clippy, bundling, criticism, scroogled, mojave, experiment, champagne, want, campaigns, japan, india, campus, estates, retail, research, outercurve, garage, game, engineering, digital, crimes, divisions, winhec, pdc, mix, inspire, ignite, conferences, translator, outlook, msn, channel, copilot, bing, properties, mvpl, navision, attain, video, tay, teams, neo, hub, duo, surface, lifecam, hololens, kinect, products, unions, global, lgbtqia, allies, césar, cernuda, svp, richard, rashid, joe, belfiore, vps, cpo, kathleen, hogan, phil, spencer, harry, shum, clo, brad, smith, cto, kevin, cfo, amy, coo, carolina, dybeck, happe, guthrie, takeshi, numoto, cmo, senior, leadership, emma, walmsley, john, stanton, charles, scharf, carlos, rodriguez, penny, pritzker, sandi, peterson, mark, mason, catherine, macgregor, teri, hugh, johnston, reid, hoffman, chairman, board, directors, paul, allen, bill, gates, founders, package, wix, vcpkg, nuget, clickonce, setup, delivery, xunit, windbg, debugger, playwright, onefuzz, codeview, debugging, sourcesafe, delta, extensible, wdm, hlk, umdf, kmdf, wdf, wdk, drivers, workflow, presentation, identity, cardspace, maui, forms, razor, mvc, ajax, asp, winui, uwp, xna, silverlight, frameworks, xaml, vpl, verona, lean, ironruby, ironpython, jscript, javascript, java, dexterity, dafny, winrt, bosque, xamlpad, windiff, generator, ilasm, webmatrix, frontpage, sharepoint, roslyn, robotics, quickc, qbasic, quickbasic, pascal, msbuild, assembler, fxcop, expression, blend, environments, calculus, algebra, referential, federated, distributed, topics, sharding, administration, rewriting, xqj, dictionary, cursor, relation, surrogate, superkey, pacelc, design, principle, foreign, candidate, orientation, crud, cap, theorem, codd, rules, armstrong, axioms, acid, blockchain, multi, newsql, nosql, graph, wide, 2nd, book, elk, 7203, 5867, ben, gan, itzik, 2197, 2196, tuning, lance, delano, rajesh, george, 7645, 8923, wrox, starter, sqlmag, october, removed, render, rptproj, aschenbrenner, 1st, vienna, 4302, 0865, apress, blog, basics, extents, downloads, chapter, 2015, weblog, improved, home, increased, 10gb, licensing, datasheet, faq, 2105, compare, notes, installing, options, installation, guidance, center, lextrait, vincent, beacon, v10, latest, xleratordb, modeling, bids, utilizing, customized, extensions, structures, renamed, ssdt, cubes, editor, optional, commit, repositories, diagnostics, mac, git, central, browse, upon, observe, alter, existing, adding, modifying, configuring, administering, editors, graphical, replaces, ssmse, graphically, ssms, intellisense, debug, continued, 2014, predecessor, earlier, osql, isql, functionally, equivalent, pertains, identical, although, extra, versatility, prompt, during, deployment, scripting, handed, constituent, inbuilt, thesaurus, find, queried, against, accurateness, scans, extract, meaningful, blob, extracted, hands, etc, frequently, useful, remaining, associating, found, invokes, gatherer, msftesql, msftefd, indexing, operator, efficient, inexact, indicated, means, accurate, inflectional, verb, tense, lower, exact, proximity, searches, specified, considered, ssis, extracting, sources, transforming, aggregation, duplication, merging, exporting, transformed, destination, workflows, import, etl, originally, post, bundled, generating, driven, registers, occurs, informing, occurrence, smtp, soap, filesystem, longer, officially, 2003, variety, formats, excel, html, archive, tiff, png, jpeg, gif, emf, bmp, csv, builder, ssrs, generation, gathered, administered, custom, cube, algorithms, linear, logistic, neural, regression, naive, bayes, decision, dmx, mdx, holap, rolap, molap, publishes, replicates, periodically, directionally, modified, differently, conflict, resolved, policies, rowguid, synced, synchronizes, real, replicate, synchronize, entirety, present, agents, caches, follows, authentication, checking, authorization, certificates, kerberos, ntlm, plain, body, contract, conversation, put, acts, internally, insert, delete, receives, addresses, located, programs, queues, contracts, parts, communicates, exchange, reliable, messaging, queuing, analysts, configure, remote, push, executions, trained, scoring, operates, having, computers, come, distributions, commonly, science, microsoftml, revoscaler, revoscalepy, sqlagent, alerts, administrators, notified, email, typical, maintenance, backups, handling, subscriptions, performing, sub, restore, check, shipping, schedules, jobs, handles, automated, boots, assortment, essential, presents, accesses, doing, avoid, already, setting, parameter, several, consequence, datatype, xquery, true, integrates, threading, satisfied, detection, resolution, define, udt, persist, compiled, verified, relating, class, assemblies, triggers, natively, implementation, mirrored, receiving, plug, ins, directly, bypassing, generic, layers, ole, keywords, altering, entering, editing, consume, leverage, sending, errors, eval, loop, extend, instruction, manipulation, definition, ddl, dml, repl, procedural, parameterized, issued, accept, input, output, call, times, resolve, actual, every, reduces, traffic, somewhat, improves, selectively, provided, tries, take, looks, load, referred, should, selectivity, worthwhile, finally, costly, total, mean, generated, temporarily, invocations, unused, discarded, mode, expressed, dialect, shares, specifies, figures, steps, retrieve, ways, give, selecting, chooses, expected, shortest, declaratively, whenever, opposed, overwriting, additionally, maintained, though, requests, blocked, older, request, ids, tempdb, multiversion, applied, even, administrator, grained, simultaneously, requires, lightweight, latches, spinlocks, robust, intensive, busy, worker, takes, remedial, measures, cases, kill, entangled, roll, implement, maintains, manages, mediated, blocks, deadlocks, mutual, exclusion, attempt, changed, long, locked, latter, ram, minimize, disk, amount, copies, subsequent, reads, writes, referenced, whereby, done, background, thread, again, matched, damaged, tampered, meantime, buffers, default, providing, node, reachable, sorted, keys, unordered, indices, situations, advantages, heaps, units, sequentially, marked, byte, header, owns, contained, change, logging, uniform, mixed, exceeds, possibly, pointer, varbinary, composite, udts, virtual, bytes, exabyte, secondary, optionally, ldf, ndf, mdf, constraints, variable, length, textual, floats, integers, arithmetic, depending, arguments, gives, fix, blobs, char, decimal, float, integer, primitive, typed, communicated, 1984, packets, encased, transport, dependent, consequently, pipes, books, online, getting, guide, stress, read80trace, ostress, pssdiag, utility, best, practices, analyzer, packs, sqlxml, northwind, pubs, sample, published, featured, datacenters, 256, virtually, unlimited, premium, had, workload, limits, disconnected, proxy, licensed, working, did, admin, afterwards, wid, partnership, dell, datawarehouse, formerly, pdw, hundreds, aps, minimal, demand, specifically, 180, days, stop, specially, certain, ssee, test, production, early, charge, public, respectively, hand, binaries, dll, footprint, markedly, reduced, compared, batches, connectivity, proper, scaled, replacement, superset, capability, advanced, note, focusing, self, semantic, quality, pivot, option, hosting, tco, stand, alone, differs, fewer, instances, hot, still, 524, address, 640, cpu, cores, petabytes, onward, x64, minimum, recommended, big, clusters, intelligent, enhanced, experience, installations, platforms, docker, ubuntu, suse, red, hat, columnar, aka, hierarchical, filestream, spatial, finishes, revision, codename, shiloh, date, 1998, conversion, 1995, collaboration, independently, adaptive, 1993, entry, began, port, ashton, tate, begins, extends, day, entirely, descriptive, responds, bit, often, pronounced, sequel, function, markets, least, dozen, aimed, audiences, ranging, facing, structured, www, chinese, french, german, italian, japanese, korean, portuguese, brazil, russian, spanish, indonesian, months, stable, years, family, encyclopedia, item, printable, switch, parser, get, shortened, permanent, here, talk, tiếng, việt, українська, türkçe, ไทย, தமிழ், svenska, slovenčina, srpskohrvatski, српскохрватски, русский, română, português, polski, norsk, bokmål, nederlands, melayu, монгол, latviešu, 한국어, қазақша, 日本語, italiano, indonesia, հայերեն, hrvatski, हिन्दी, עברית, ગુજરાતી, galego, français, فارسی, español, ελληνικά, deutsch, dansk, čeština, català, български, azərbaycanca, العربية, community, portal, help, contribute, random, events, navigation, jump, content,
Text of the page (random words):
28 northwind and pubs sample databases updated samples for sql server 2000 tools stress testing and performance analysis tools read80trace and ostress pssdiag data collection utility notification services up to service pack 1 security tools best practices analyzer 1 0 reporting services up to service pack 2 reporting services report packs sql server 2000 driver for jdbc up to service pack 3 sqlxml 3 0 up to service pack 3 documentation 29 sql server 2000 books online sql server 2000 system table map resource kit sql server 2000 getting started guide architecture edit the protocol layer implements the external interface to sql server all operations that can be invoked on sql server are communicated to it via a microsoft defined format called tabular data stream tds tds is an application layer protocol used to transfer data between a database server and a client initially designed and developed by sybase inc for their sybase sql server relational database engine in 1984 and later by microsoft in microsoft sql server tds packets can be encased in other physical transport dependent protocols including tcp ip named pipes and shared memory consequently access to sql server is available over these protocols in addition the sql server api is also exposed over web services 11 data storage edit data storage is a database which is a collection of tables with typed columns sql server supports different data types including primitive types such as integer float decimal char including character strings varchar variable length character strings binary for unstructured blobs of data text for textual data among others the rounding of floats to integers uses either symmetric arithmetic rounding or symmetric round down fix depending on arguments select round 2 5 0 gives 3 microsoft sql server also allows user defined composite types udts to be defined and used it also makes server statistics available as virtual tables and views called dynamic management views or dmvs in addition to tables a database can also contain other objects including views stored procedures indexes and constraints along with a transaction log a sql server database can contain a maximum of 2 31 objects and can span multiple os level files with a maximum file size of 2 60 bytes 1 exabyte 11 the data in the database are stored in primary data files with an extension mdf secondary data files identified with a ndf extension are used to allow the data of a single database to be spread across more than one file and optionally across more than one file system log files are identified with the ldf extension 11 storage space allocated to a database is divided into sequentially numbered pages each 8 kb in size a page is the basic unit of i o for sql server operations a page is marked with a 96 byte header which stores metadata about the page including the page number page type free space on the page and the id of the object that owns it the page type defines the data contained in the page this data includes data stored in the database an index an allocation map which holds information about how pages are allocated to tables and indexes and a change map which holds information about the changes made to other pages since last backup or logging or contain large data types such as image or text while a page is the basic unit of an i o operation space is actually managed in terms of an extent which consists of 8 pages a database object can either span all 8 pages in an extent uniform extent or share an extent with up to 7 more objects mixed extent a row in a database table cannot span more than one page so is limited to 8 kb in size however if the data exceeds 8 kb and the row contains varchar or varbinary data the data in those columns are moved to a new page or possibly a sequence of pages called an allocation unit and replaced with a pointer to the data 30 for physical storage of a table its rows are divided into a series of partitions numbered 1 to n the partition size is user defined by default all rows are in a single partition a table is split into multiple partitions in order to spread a database over a computer cluster rows in each partition are stored in either b tree or heap structure if the table has an associated clustered index to allow fast retrieval of rows the rows are stored in order according to their index values with a b tree providing the index the data is in the leaf node of the leaves and other nodes storing the index values for the leaf data reachable from the respective nodes if the index is non clustered the rows are not sorted according to the index keys an indexed view has the same storage structure as an indexed table a table without a clustered index is stored in an unordered heap structure however the table may have non clustered indices to allow fast retrieval of rows in some situations the heap structure has performance advantages over the clustered structure both heaps and b trees can span multiple allocation units 31 buffer management edit sql server buffers pages in ram to minimize disk i o any 8 kb page can be buffered in memory and the set of all pages currently buffered is called the buffer cache the amount of memory available to sql server decides how many pages will be cached in memory the buffer cache is managed by the buffer manager either reading from or writing to any page copies it to the buffer cache subsequent reads or writes are redirected to the in memory copy rather than the on disc version the page is updated on the disc by the buffer manager only if the in memory cache has not been referenced for some time while writing pages back to disc asynchronous i o is used whereby the i o operation is done in a background thread so that other operations do not have to wait for the i o operation to complete each page is written along with its checksum when it is written when reading the page back its checksum is computed again and matched with the stored version to ensure the page has not been damaged or tampered with in the meantime 32 concurrency and locking edit sql server allows multiple clients to use the same database concurrently as such it needs to control concurrent access to shared data to ensure data integrity when multiple clients update the same data or clients attempt to read data that is in the process of being changed by another client sql server provides two modes of concurrency control pessimistic concurrency and optimistic concurrency when pessimistic concurrency control is being used sql server controls concurrent access by using locks locks can be either shared or exclusive an exclusive lock grants the user exclusive access to the data no other user can access the data as long as the lock is held shared locks are used when some data is being read multiple users can read from data locked with a shared lock but not acquire an exclusive lock the latter would have to wait for all shared locks to be released locks can be applied on different levels of granularity on entire tables pages or even on a per row basis on tables for indexes it can either be on the entire index or on index leaves the level of granularity to be used is defined on a per database basis by the database administrator while a fine grained locking system allows more users to use the table or index simultaneously it requires more resources so it does not automatically yield higher performance sql server also includes two more lightweight mutual exclusion solutions latches and spinlocks which are less robust than locks but are less resource intensive sql server uses them for dmvs and other resources that are usually not busy sql server also monitors all worker threads that acquire locks to ensure that they do not end up in deadlocks in case they do sql server takes remedial measures which in many cases are to kill one of the threads entangled in a deadlock and roll back the transaction it started 11 to implement locking sql server contains the lock manager the lock manager maintains an in memory table that manages the database objects and locks if any on them along with other metadata about the lock access to any shared object is mediated by the lock manager which either grants access to the resource or blocks it sql server also provides the optimistic concurrency control mechanism which is similar to the multiversion concurrency control used in other databases the mechanism allows a new version of a row to be created whenever the row is updated as opposed to overwriting the row i e a row is additionally identified by the id of the transaction that created the version of the row both the old as well as the new versions of the row are stored and maintained though the old versions are moved out of the database into a system database identified as tempdb when a row is in the process of being updated any other requests are not blocked unlike locking but are executed on the older version of the row if the other request is an update statement it will result in two different versions of the rows both of them will be stored by the database identified by their respective transaction ids 11 data retrieval and programmability edit the main mode of retrieving data from a sql server database is querying for it the query is expressed using a variant of sql called t sql a dialect microsoft sql server shares with sybase sql server due to its legacy the query declaratively specifies what is to be retrieved it is processed by the query processor which figures out the sequence of steps that will be necessary to retrieve the requested data the sequence of actions necessary to execute a query is called a query plan there might be multiple ways to process the same query for example for a query that contains a join statement and a select statement executing join on both the tables and then executing select on the results would give the same result as selecting from each table and then executing the join but result in different execution plans in such case sql server chooses the plan that is expected to yield the results in the shortest possible time this is called query optimization and is performed by the query processor itself 11 sql server includes a cost based query optimizer which tries to optimize on the cost in terms of the resources it will take to execute the query given a query then the query optimizer looks at the database schema the database statistics and the system load at that time it then decides which sequence to access the tables referred in the query which sequence to execute the operations and what access method to be used to access the tables for example if the table has an associated index whether the index should be used or not if the index is on a column which is not unique for most of the columns low selectivity it might not be worthwhile to use the index to access the data finally it decides whether to execute the query concurrently or not while a concurrent execution is more costly in terms of total processor time because the execution is actually split to different processors might mean it will execute faster once a query plan is generated for a query it is temporarily cached for further invocations of the same query the cached plan is used unused plans are discarded after some time 11 33 sql server also allows stored procedures to be defined stored procedures are parameterized t sql queries that are stored in the server itself and not issued by the client application as is the case with general queries stored procedures can accept values sent by the client as input parameters and send back results as output parameters they can call defined functions and other stored procedures including the same stored procedure up to a set number of times they can be selectively provided access to unlike other queries stored procedures have an associated name which is used at runtime to resolve into the actual queries also because the code need not be sent from the client every time as it can be accessed by name it reduces network traffic and somewhat improves performance 34 execution plans for stored procedures are also cached as necessary t sql edit main article t sql t sql transact sql is microsoft s proprietary procedural language extension for sql server it provides repl read eval print loop instructions that extend standard sql s instruction set for data manipulation dml and data definition ddl instructions including sql server specific settings security and database statistics management it exposes keywords for the operations that can be performed on sql server including creating and altering database schemas entering and editing data in the database as well as monitoring and managing the server itself client applications that consume data or manage the server will leverage sql server functionality by sending t sql queries and statements which are then processed by the server and results or errors returned to the client application for this it exposes read only tables from which server statistics can be read management functionality is exposed via system defined stored procedures which can be invoked from t sql queries to perform the management operation it is also possible to create linked servers using t sql linked servers allow a single query to process operations performed on multiple servers 35 sql server native client a k a snac edit sql server native client is the native client side data access library for microsoft sql server version 2005 onwards it natively implements support for the sql server features including the tabular data stream implementation support for mirrored sql server databases full support for all data types supported by sql server asynchronous operations query notifications encryption support as well as receiving multiple result sets in a single database session sql server native client is used under the hood by sql server plug ins for other data access technologies including ado or ole db the sql server native client can also be directly used bypassing the generic data access layers 36 on november 28 2011 a preview release of the sql server odbc driver for linux was released 37 sql clr edit main article sql clr microsoft sql server 2005 includes a component named sql clr common language runtime via which it integrates with net framework unlike most other applications that use net framework sql server itself hosts the net framework runtime i e memory threading and resource management requirements of net framework are satisfied by sqlos itself rather than the underlying windows operating system sqlos provides deadlock detection and resolution services for net code as well with sql clr stored procedures and triggers can be written in any managed net language including c and vb net managed code can also be used to define udt s user defined types which can persist in the database managed code is compiled to cli assemblies and after being verified for type safety registered at the database after that they can be invoked like any...
|
