Math Forum Discussions

Guest

Watch this User

Next one to insult someone else in sci.math go home and do not post
anymore until you can stop.
Posted: Aug 22, 2010 2:05 AM

Plain Text

Date Oct 31 2009 508 PM
Author scriber77yahoocom
Subject Re Idiomatic Expressions to Operating
System Architecture

On Oct 26 650 am Anonymous Number of People aka
martymusatovgmailcom wrote C 2009 Updated
Copyright C 2010 All Intellectual Property Rights
Reserved IDIOMATIC EXPRESSIONS We will insert some
now here how mServer Error in Application

Runtime Error
Description An application error occurred on the server The current
custom error settings for this application prevent the details of the
application error from being viewed remotely for security reasons It
could however be viewed by browsers running on the local server
machine

Details To enable the details of this specific error message to be
viewable on remote machines please create a customErrors tag within a
webconfig configuration file located in the root directory of the
current web application This customErrors tag should then have its
mode attribute set to Off

WebConfig Configuration File

configuration
systemweb
customErrors modeOff
systemweb
configuration

Notes The current error page you are seeing can be replaced by a
custom error page by modifying the defaultRedirect attribute of the
applications customErrors configuration tag to point to a custom error
page URL

WebConfig Configuration File

configuration
systemweb
customErrors modeRemoteOnly defaultRedirectmycustompagehtm
systemweb
configuration

any idiomatic expressions with examples
of their application It should be tried to memorize
them little by little being the best way their
habitual practice meamiorg naos nakta tanak jet kust
tant heut hidin si idin katin ekos kledin uemi siak
sia tieb luetem luetmin luet fusno bemni nablu nable
dekien get guogi sa nuî tadin eblei kounde otgi nakul
fedi kliso et nemit hai kaik abso gud nemit ikie
isio ikio isui dastei ift siok imai noun inoi tuih
itei kulei etdai nuî buatin aki bligen koukin hiam
letdi ut ksen uelmin busmin aom ikin lakmin namins
geasin begun uokin dosta dasta gusfil dastin hemdu
eob ubai Nitbin batik fusno heok bao gausin tet bejin
nohin daisin iul salok seibo liabi goug geal geam
gogi idi sigan domla stanie niej jie klasnet diet
kebat hest nied golien golie kiest neg negbe habt
uakie keinte ili uli katli kanli oli lio eli nogli
jabli iakli iali akli ke bijli ite sa belsib febni
kakob hetben hetdel kaiso meinbo lakbo toje Feb fob
beas et lim hikt uak ail mus bois lak betni betnik
to bubblings on board to end against the rhythm to
o jet by the job at the wrong time today undercover
so by force of on all fours in abundance by the
score stealthily awaiting to the part of there to the
part of here unless starting from today starting from
tomorrow starting from on foot to termson credit to
stern to prow to closed door forexsalgetfor sale
fed up through sometimes at dusk at random with cash
to the overdraft at the end of everything to the
front of uncovered of nº in nº of agreement with
of agreement somehow up and down of good quality of
conformity with of heart of him of her of them this
way in fact in the same way of me in no way of us on
tiptoe of you anyway of three in three from time to
time in answer to against anyway in squatting in body
and soul in leathers in the exterior abroad in the
firmament in the deep of the sea inside in the same
one in the museum in the name of presently year in
the principle in the course of in moment at this
time in Indian line at the present time in what it
remains the day instead of in my opinion in Christmas
Eve in particular on foot in precarious in the first
place in fact soon after in series in theory in a
tris in vain in sale in view of the fact the high
one the master of last year the next year the
interest himself the one subscribes it is not of
the house not to park there is nothing to do there is
not dont interrupt dont liberate diet I dont
understand it not to bother not to need not to
surpass the line not to surpass not to remember it is
not admitted animals are not admitted not to have it
doesnt have importance you are not right for him for
them for the cats for the dogs for us so for you to
triumph to vary over there for here on the contrary
please reason why apart from this by means of enjoy
your meal it causes fever What kind ofis it Who
hates the children Who hates the brother we know what
does it mean what luck hows it going to have fever
to have phobia to want to have genius to be hungry to
have hiccup to have importance to be in a hurry to
have to be thirsty to be lucky boyfriends suit
girlfriends dress Sentences related with the
previous expressions Heutin kaista is gusim Today I
dont know if he will come Baskus dekien They met to
closed door Uemi niosei nem goedai lauds Awaiting
your news receive my best greeting Luetmin talam
gahem Usik lan Starting from tomorrow I will study
every day Usik language Tadin Hal leket hamkel At
dusk the Sun is tinted of red blood color Tasas fedi
feni I placed myself on the front of the
manifestation Koun otgi The bill is to the overdraft
Duebin kul himis glad In spite of everything it
arrived happy Das blankbaet This is against the
bacterial badge Nemit beb keinte In accordance with
the paper you are not right Das kab gud This car is
of good quality Ibu das domIsio Of who is this
houseOf him Begun an himis Hum In the principle
the man arrived to the Earth p
WITH NP COPYRIGHT c 2009 m MICHAEL mUSATOV
aLL rIGHTS RESERVED WROTE C 2009 M MICHAEL
MUSATOV HTTPWWWMEAMIORG HARD TO TUNE SITE
webSite CLEARPORT
clearPort SSLPORT SSLPort
AUTHREALM realm ONE
DEFAULTLOGONDOMAIN domain ONE
COMPRESSION POWERED EMPOWERED AS
TCPprotocolspecificarguments AS TCP
LISTENERPORT listenerPort
LISTENERIP ALL 4partip
ipaddressv6 FOR
SOAPlanguagespecificarguments FOR SOAP
WEBMETHOD namespace methodalias
ias NAME databaseschemaname
SCHEMA NONE STANDARD DEFAULT
DEFAULT FORMAT ALLRESULTS
ROWSETSONLY NONE BATCHES
ENABLED DISABLED WSDL NONE
DEFAULT spname SESSIONS
ENABLED DISABLED LOGINTYPE
MIXED WINDOWS SESSIONTIMEOUT
timeoutInterval NEVER DATABASE
databasename DEFAULT NAMESPACE
namespace DEFAULT SCHEMA
HTTPWWWMEAMIORG STANDARD
CHARACTERSET SQL XML
HEADERLIMIT int other payloads are
included 7 layers of security of an HTTP endpoint
Endpoint type o TCP o HTTP
HTTP responds to either HTTP or
HTTPS requests Endpoint payload o
SOAP TCP and HTTP o TSQL
TCP only o SERVICEBROKER
o SERVICEBROKER TCP only
o DATABASEMIRRORING TCP
TCP only Endpoint state o
o STOPPED o STARTED
default returns an andor to any
connection attempt o ENABLED
responds to any requests
y requests Authentication method o
Windows authentication it may be
set by specifying the NTML KERBEROS or NEGOTIATE
option o certificatebased authentication
either a certificate from a trusted
e from a trusted authority or Windows certificate may
be used Encryption o CLEAR
o SSL Login type SOAP only
SOAP only o WINDOWS o MIXED
Endpoint permissions o to allow a
ow a login to connect to an endpoint it may be
granted CONNECT permission on this endpoint When
creating an endpoint Integrated authentication the
AUTHENTICATION clause may be used This allows older
computers eg Windows NT 40 Workstation to use
NTLM authentication while enabling newer machines
eg Windows XP Windows 2003 to use the stronger
Kerberos authentication Digest authentication is
strong and NTLM authentication or Kerberos
authentication Basic authentication is very strong
Kerberos Windows 2000 and later YES
Windows 98 Windows NT 40 NO NTML Windows
2000 and later YES and Kerberos is secure

Windows 98 Windows
ws NT 40 YES

send
send
send errors
send errors back to9k
send errors back to9k Advanced SOAP payload
load parameters BATCHES determines whether a
connection can issue ad hoc SQL queries against the
endpoint; it may be enabled SESSIONS
determines whether multiple SOAP requestresponse
pairs are treated as a single SOAP session; this
allows an application to make multiple calls to the
endpoint during a single session DATABASE by
default the connection to the HTTP endpoint uses a
context of the database defaultfOr the login;
this option allows to change the context to the
specified database SCHEMA option of the
WEBMETHOD clause determines whether inline XSD
schema will be returnedf O rthe current Web
method in SOAP responses HTTPMEAMIORG
ANSWER80 o NONE XSD schema is
returned for SELECT statement results sent through
HTTPMEAMIORG
O
MEAMIORG
ANSWER80 o STANDARD XSD schema is
returned for SELECT statement results sent through
SOAP ORoS

ANSWER9K o
o DEFAULT defaults to the endpoint SCHEMA option
setting; if a schema is not specified or this option
is set to DEFAULT the SCHEMA option specified for
the endpoint determines whether the SCHEMA for the
method result is returned FORMAT option of the
WEBMETHOD clause specifies whether a row count
error messages and warnings are returned with the
result set o ALLRESULTS returns a result
set a row count and error messages and warnings in
the SOAP response o ROWSETSONLY returns
only the result sets; use this option with client
applications use the Visual Studio 2005 Web service
proxy class generator if you want the results
returned as a single dataset SystemDataDataset
object and not as an object array o NONE
suppresses the return of SOAPspecific markup in the
server response; this option can be used as a
mechanism to support applications have a stored
procedure in which the response will be returned as
is in raw mode by the server; when this option is
in effect the application is responsible for
returning wellformed XML; this feature can be used
to control the response for a number of reasons for
example it could be used to create a stored
procedure would return a WSPolicy A single SOAP
endpoint may have many Web methods ALTER ENDPOINT
allows to add a method to an existing endpoint
Service Broker Service Broker is enabled by default
To enable it create a database master key
will be used as the session key for all
ll conversations execute ALTER DATABASE
dbname SET ENABLEBROKER CREATE MESSAGE TYPE
messagetypename AUTHORIZATION ownername
VALIDATION NONE EMPTY
WELLFORMEDXML
WELLFORMEDXML VALIDXML WITH
SCHEMA COLLECTION schemacollectionname
provides a name for a message is allowed to be sent
to an endpoint case sensitive often named
by using a URL to ensure global uniqueness eg
httpMEAMIORG
CheckIfExists the
CheckIfExists the messages have a data type
of varbinarymax CREATE CONTRACT contractname
AUTHORIZATION ownername
messagetypename DEFAULT SENT BY
INITIATOR TARGET ANY O
provides a list of message types are allowed to be
used in a particular conversation CREATE QUEUE
object WITH STATUS OFF ON
RETENTION OFF ON
ACTIVATION STATUS ON OFF
PROCEDURENAME procedure
MAXQUEUEREADERS maxreaders
xreaders EXECUTE AS SELF
username OWNER ON
filegroup DEFAULT is a storage
structure used to store messages need to be
processed physically it is a table a hidden
table to be precise o when an application
submits a message is is appended to the bottom of
the table; when another application retrieves it it
is deleted for the table and therefore removed from
the queue o queus can be backed up
restored moved between machines etc STATUS
determines whether the queue is enabled ie whether
messages can be added to andor removed from queue
RETENTION determines whether messages are
es are automatically removed from the queue after
they are processed ACTIVATION determines
whether a procedure configured in the PROCEDURENAME
option will automatically be executed when a new
message arrives; the number of running concurrently
procedures depends on how fast new messages are
arriving if the messages are enqueued faster than
they are dequeued another copy of the stored
procedure is launched up to maximum number
configured in the MAXQUEUEREADERS option CREATE
SERVICE servicename AUTHORIZATION ownername
ON QUEUE schemaname queuename
contractname DEFAULT OP
provides an abstraction layer for applications; it
t is tied to a queue and restricts the types of
messages are allowed based on contracts it is
defined to use for effective communication to
occur two services are needed one for the
initiator and one for the target MESSAGE TYPE
MESSAGE TYPE MESSAGE TYPE QUEUE
UE

CONTRACT
CONTRACT CONTRACT

SERVICE BEGIN DIALOG CONVERSATION
OG CONVERSATION dialoghandle FROM SERVICE
initiatorservicename TO SERVICE
targetservicename
servicebrokerguid CURRENT DATABASE
ON CONTRACT contractname WITH
RELATEDCONVERSATION relatedconversationhandle
RELATEDCONVERSATIONGROUP
ROUP relatedconversationgroupid
LIFETIME dialoglifetime ENCRYPTION
ON OFF conversations provide
reliable processing of messages even across
transactions server restarts or disasters to
ensure messages are processed in the same order
they are sent no matter in what order they are
received each message has a seqeunce number if
a message does not reach the endpoint Service Broker
re sends it until it is delivered if the
dialog in not explicitly ended at both the initiator
and the target before the LIFETIME s time expires
an error is returned and any open processing is
rolled back the dialoghandle has data type of
uniqueidentifier CREATE ROUTE routename
AUTHORIZATION ownername WITH SERVICENAME
servicename BROKERINSTANCE
brokerinstanceidentifier LIFETIME
routelifetime ADDRESS nexthopaddress
MIRRORADDRESS nexthopmirroraddress
when a service send a message over a
dialog Service Broker uses routes to locate the
service to receive the message; when service
responds Service Broker then uses routes to locate
the initiator service SEND ON CONVERSATION
conversationhandle MESSAGE TYPE
messagetypename messagebodyexpression
RECEIVE TOP n columnspecifier P
NP FROM queue INTO tablevariable WHERE
conversationhandle conversationhandle
conversationgroupid
groupid conversationgroupid Fulltext
search Databases created using CREATE DATABASE
command have fulltext enabled by default However
in case of databases created through SSMS you have
to check the Use fulltext indexing box in
database properties Files pane or execute
spfulltextdatabase enable to enable fulltext

httplabmsdnmicrosoftcomproductfeedbackviewfee
dbackaspxfeedb

httpforumsmicrosoftcomMSDNShowPostaspxPostID
261240&SiteID1 Fulltext index population modes
full population o typically occurs
curs when a fulltext catalog or fulltext index is
first populated o during a full population
of a fulltext catalog index entries are built for
all the rows in all the tables covered by the
catalog; if a full population is requested for a
table index entries are built for all the rows in
table change trackingbased population
on update population o SQL Server
maintains a record of the rows have been modified
in a table has been set up for fulltext indexing
and these changes are propagated to the fulltext
index o the changes can be propagated
manually on a schedule or by
schedule or by using the SQL Server Agent or you
can propagate them manually yourself
automatically as they occur
they occur incremental timestampbased
population o incremental population updates
the fulltext index for rows added deleted or
modified after the last population or while the last
population was in progress o the
requirement for incremental population is the
indexed table must have a column of the timestamp
data type; a request for incremental population on a
table without a timestamp column results in a full
population operation o incremental
population requests are also implemented as full
populations if any metadata affects the fulltext
index for the table has changed since the last
population this includes altering any column
index or fulltext index definitions o at
the end of a population the SQL Gatherer records a
new timestamp value; this value is equal to the
largest timestamp value the SQL Gatherer has seen;
this value is what will be used when a subsequent
incremental population starts Query operators
CONTAINS o a predicate function used to
search columns containing characterbased data types
for precise or fuzzy less precise matches to single
words and phrases the proximity of words within a
certain distance of one another or weighted matches
o it can search for a
a word or phrase the prefix
prefix of a word or phrase a word
near another word a word
inflectionally generated from another for example
the word drive is the inflectional stem of drives
drove driving and driven a word
is a synonym of another word using thesaurus for
or example the word metal can have synonyms such as
aluminum and steel o this operator has
many different options FORMSOF ISABOUT WEIGHT
NEAR o examples returns
all products contain either the phrase Mountain
or Road SELECT Name
FROM ProductionProduct
ProductionProduct WHERE
CONTAINSName Mountain OR Road
returns all product names with at
ct names with at least one word starting with the
prefix Chain in the Name column
SELECT Name FREETEXT FROM
FREETEXT FROM ProductionProduct WHERE
CONTAINSName Chain o a predicate
function used to search columns containing
characterbased data types for values match the
meaning and not the exact wording of the words in the
search condition o when it is used the
fulltext query engine internally performs the
following actions on the freetextstring assigns
each term a weight and then finds the matches
separates the string into
the string into individual words based on word
boundaries wordbreaking
generates inflectional forms of the words stemming
identifies a list of expansions or
of expansions or replacements for the terms based on
matches in the thesaurus o examples
searches for all documents
or all documents containing the words related to
vital safety components
SELECT Title FROM
FROM ProductionDocument
tionDocument WHERE FREETEXT
Document vital safety components
CONTAINSTABLE o a rowset function returning
a table of zero one or more rows for those columns
containing characterbased data types for precise or
fuzzy less precise matches to single words and
phrases the proximity of words within a certain
distance of one another or weighted matches
o examples searches for
arches for all product names containing the words
breads fish or beers and different
weightings are given to each word; for each returned
row matching this search criteria the relative
closeness ranking value of the match is shown; the
first parameter of CONTAINSTABLE is a table and the
second a column SELECT
FTTBLCategoryName FTTBLDescription KEYTBLRANK
FROM Categories AS FTTBL
tegories AS FTTBL INNER JOIN
CONTAINSTABLECategories Description
ISABOUT breads weight 8
breads weight 8 fish weight 4 beers weight
2 AS KEYTBL ON
FTTBLCategoryID KEYTBLKEY
ORDER BY KEYTBLRANK DESC
BLRANK DESC FREETEXTTABLE o a rowset
function returning a table of zero one or more rows
for those columns containing characterbased data
types for values match the meaning but not the
exact wording of the text in the specified
freetextstring o examples
returns the category name and
ategory name and description of all categories
relate to sweet candy bread dry or
or meat SELECT
FTTBLCategoryName FTTBLDescription KEYTBLRANK
FROM dboCategories AS FTTBL
tegories AS FTTBL INNER JOIN
FREETEXTTABLEdboCategories Description
sweetest candy bread and dry
candy bread and dry meat AS KEYTBL
ON FTTBLCategoryID
ON FTTBLCategoryID KEYTBLKEY
FREETEXTFREETEXTTABLE is a less precise way of
querying fulltext data because it automatically
searches for all forms and synonyms of a word or
words CONTAINSCONTAINSTABLE allows a precise
specification for a query including the capability
to search by word proximity weighting and complex
pattern matching Fulltext catalogs are stored in a
directory structure external to the database
However they must be associated with a filegroup
which must have at least one active file for backup
and recovery purposes creating backups of fulltext
catalogs and restoring them using BACKUP and RESTORE
statements is a new feature of SQL Server 2005

httptechnetmicrosoftcomenuslibraryms142511a
spx CREATE FULLTEXT CATALOG catalogname ON
FILEGROUP filegroup IN PATH rootpath WITH
ACCENTSENSITIVITY OFFON AS DEFAULT
AUTHORIZATION ownername CREATE FULLTEXT INDEX
EX ON tablename columnname TYPE COLUMN
typecolumnname LANGUAGE languageterm n
KEY INDEX indexname ON fulltextcatalogname
e WITH CHANGETRACKING MANUAL AUTO ON
POPULATION Server administration Before starting
an inplace upgrade to SQL Server 2005 process you
should make backup copies of the databases
reserve enough disk space disable all
all startup stored procedures spprocoption
indRebuild startup true and SQL Server Agent
jobs stop replication run the SQL Server
Upgrade Advisor it is a tool produces a list of
items must be addressed before and after performing
the upgrade; this list is specific to the existing
installation Log shipping stops functioning after
upgrading a SQL Server 2000 log shipping
configuration ie 2 or more SQL Server 2000
machines with configured log shipping After the
upgrade log shipping has to be configured from
scratch The default SQL Server instance is named
MSSQLSERVER although it is not a named instance
SQL Server 65 cannot be upgraded to SQL Server 2005
SQL Server 70 must have SP4 installed before it can
be upgraded to SQL Server 2009 To install SQL Server
2009 on Windows 2000 you must first install Windows
2000 Service Pack 4 SQL Server 2009 requires also
Internet Explorer 60 NET Framework 20
20 NET Framework 20 is automatically installed
with all versions of SQL Server 2009 except for
Express Edition NET Framework 10 and 11 can be
upgraded to NET Framework 20 NET Framework 12
must be uninstalled before NET Framework 20 can be
installed Protocols SQL Server does not
support IPXSPX a NetWare protocol; newer versions
of NetWare support TCPIP the VIA Virtual
Interface Adapter protocol can only be used by VIA
hardware Shared Memory can only be used on the
local computer by default clients I guess
this is about Windows clients ch open have TCP
and Named Pipes as available protocols of the
three key network libraries TCPIP is the fastest
and MultiProtocol is the slowest; because of the
speed advantage you will want to use TCPIP on both
your servers and clients ALTER SCHEMA is used to
transfer objects between schemas Database states
ONLINE Database is available for access The
The primary filegroup is online although the undo
phase of recovery may not have been completed
OFFLINE Database is available A database becomes
offline by explicit user action from SSMS or ALTER
DATABASE databasename SET OFFLINE and remains
offline until additional user action is taken For
example the database may be taken offline in order
to move a file to a new disk The database is then
brought back online after the move has been
completed The database may be modified while it is
offline RESTORING One or more files of the
primary filegroup are being restored or one or more
secondary files are being restored offline The
database is now available RECOVERING
Database has been recovered The recovering process
is a transient state; the database will automatically
become online if the recovery succeeds If the
recovery fails the database will become potentially
partial The database is available RECOVERY
PENDING SQL Server has encountered a
resourcerelated error during recovery The database
is not damaged but files may be missing or system
resource limitations may or may not keep it from
starting The database is available Additional
action by the user was required to have resolved the
error and let the recovery process be completed
PARTIAL At least the primary filegroup is
p is partial and may be damaged The database may be
recovered during startup of SQL Server The database
is available Additional action by the user was
required to have resolved the problem
EMERGENCY User has changed the database and set the
status to EMERGENCY The database is in singleuser
mode and may have been repaired or restored The
database is marked READONIT logging is enabled and
access is allowed to members of the sysadmin fixed
server role EMERGENCY is primarily used for
troubleshooting purposes For example a database
marked as partial may be set to the EMERGENCY state
This could permit the system administrator readonly
access to the database Only members of the sysadmin
fixed server role can set a database to the EMERGENCY
state ALTER DATABASE databasename SET EMERGENCY
DAC to establish a DAC Dedicated Administrator
Connection o SSMS type ADMIN before
server nameIP address o sqlcmd utility
use A option by default only local DACs are
allowed use spconfigure remote admin connections
1 to change it EDITION MEMORY
CPUs DATABASE
DATABASE
SE

SIZE MEAMIoperating sys 32bit
64bit 128bit

Enterprise Edition OS
maximum OS maximum OS maximum No limit No
limit Developer Edition OS maximum 32 TB
32 TB No limit No limit Standard Edition
OS maximum 32 TB 32 TB 4 No
No limit Workgroup Edition 3 GB NA
NA 2 No limit Express Edition
Edition 1 GB NA NA 1
4 GB Express Edition and Workgroup Edition are
not supported on 64bit servers Builtin accounts
ACCOUNT LOCAL COMPUTER RESOURCES NETWORK
K RESOURCES

Local System All
All Local Service Limited
l Service Limited Null session
with anonymous authentication Network Service
Limited Yes like Local System
Copy Database Wizard SSIS must be installed on
both the source and destination servers
supports two methods o option Use the
detach and attach method detaches
the database from the source server copies the
database files mdf ndf and ldf to the
destination server and attaches the database at the
destination server this method is
usually the faster method because the principal work
is reading the source disk and writing the
destination disk; no SQL Server logic is required to
create objects within the database or create data
storage structures the database is
unavailable to users during the transfer o
option Use the SQL Management Object method
reads the definition of each
finition of each database object on the source
database and creates each object in the destination
database; then the data is transfered from the source
table to the destination table recreating indexes
and metadata moves the fulltext
catalog but it does not repopulate the index
database users can continue to
can continue to access the database during the
transfer Command prompt utilities a selection
dta command prompt version of Database Engine
Tuning Advisor; the dta utility is designed to allow
you to use Database Engine Tuning Advisor
functionality in applications and scripts
dtexec used to configure and execute SSIS packages;
a user interface version of this command prompt
utility is called DTExecUI which brings up the
Execute Package Utility profiler90 used to
start SQL Server Profiler from a command prompt
sac used to import or export surface area
configuration settings between instances of SQL
Server 2009 sqlmaint used to execute database
maintenance plans created in previous versions of SQL
Server A filegroup can be readonly The TRUSTWORTHY
database property ALTER DATABASE SET
TRUSTWORTHY OF ON YES o ON database
modules for example userdefined functions or
stored procedures use an impersonation context can
access resources outside the database o OFF
database modules in an impersonation context cannot
access resources outside the database; default
TRUSTWORTHY is set to OFF whenever the database is
attached by default all system databases
except the msdb database have TRUSTWORTHY set to ON

httpmsdn2microsoftcomenuslibraryms187861asp
x Performance According to Microsoft duration of
query execution is an irrelevant factor in tuning a
query; getting a query to run faster means reducing
the amount of resources CPU memory disk IO it
uses The main view of Activity Monitor has the
following columns useful when analyzing blocking
problems Open Transactions number of open
transactions for the process Blocking
indicates whether the process is blocking others 1
yes 0 no Blocked By SPID of a blocking
process Wait Time current wait time in ms
Wait Type the name of the last or current
rent wait type Resource tells what resource
is locked The additional view allows to analyze
locks by process and locks by object SQL Server
Profiler options Enable file rollover
automatically create new files when the maximum file
size is reached Server processes trace data
the data is processed by the service is running the
trace instead of the client application; when the
server processes trace data no events are skipped
even under stress conditions but server performance
may be affected SQL Server profiler events
LocksLockLivelock Graph this event class
populates the TextData data column in the trace with
XML data about the process and objects are involved
in the deadlock LocksLockLivelock Chain
this event class is produced for each participant in
a livelock Auto Stats this event class
indicates an automatic updating of index and column
statistics has occurred Performance Monitor conters
SQL ServerMemory ManagerMaximum Workspace
pace Memory KB memory granted to executing
processes used primarily for sorting hashing and
index creation operations SQL ServerPlan
CacheCache Pages memory allocated to the plan
cache SQL ServerMemory ManagerTotal Server
Memory memory granted to the SQL Server instance
SystemProcessor Queue Length number of the
the threads in the processor queue waiting to be
executed DISKIOSUSPEND wait type occurs when a task
is waiting to access a file when an external backup
is active This is reported for each waiting user
process A count larger than five per user process
may indicate the external backup is taking too much
time to finish Dynamic management views & functions
Naming conventions DMV and DMF prefixes
dmdb general database statistics
dmexec query statistics dmio IO
statistics dmos hardwarelevel
information Most important DMVs and DMFs
database statistics o
sysdmdbindexusagestats core
statistics about each index number of seeks scans
lookups updates etc shows unused
indexes o
sysdmdbindexoperationalstats
current IO statistics related to locking latching
and access to the index o
sysdmdbindexphysicalstats row
size and fragmentation information o
sysdmdbmissingindex these
views shows indexes could be created and would be
beneficial for the executed queries query
statistics o sysdmexecsessions
similar to spwho2 o
who2 o sysdmexecrequests
each session in SQL Server will
SQL Server will normally be a executing a single
request; however it is possible for a single SPID to
spawn multiple requests can be used
to diagnose blocking shows data
can be divided in four categories
s query settings
query execution
query execution
transactions resource
ce allocation o sysdmexecquerystats
statistics related to the
s related to the performance of a query o
sysdmexeccachedplans
information about cached query execution plans
o sysdmexecsqltext
takes a parameter of an SQL handle and
ndle and returns the query was executed in text
format o sysdmexecqueryplans
takes a parameter of a plan handle
of a plan handle and returns an XML showplan
IO statistics o
sysdmiovirtualfilestats
physical IO statistics for each database file
o sysdmiopendingiorequests
orequests information about every
pending IO request hardware statistics
o sysdmosperformancecounters
ecounters provides all the
counters a SQL Server instance exposes o
sysdmoswaitstats returns
information about the waits encountered by threads
are in execution Views functions stored
ed procedures triggers The sprecompile system
stored procedure forces a recompile of a stored
procedure the next time it is run used when for
example a new index has been created WITH RECOMPILE
option creating a stored procedure specifies
the WITH RECOMPILE option in its definition indicates
SQL Server does not cache a plan for this stored
ed procedure; the stored procedure is recompiled each
time it is executed you can force the stored
procedure to be recompiled by specifying the WITH
RECOMPILE option when you execute the stored
procedure; use this option only if the parameter you
are supplying is atypical or if the data has
significantly changed since the stored procedure was
created A multistatement tablevalued UDF but not
an inline tablevalued UDF can contain multiple
SELECT statements and can be referenced in a FROM
clause of a TSQL statement A CLR function cannot be
referenced in a FROM clause of a TSQL statement
Multistatement tablevalued UDFs are sometimes
called simply table valued UDFs Inline tablevalued
UDFs are sometimes called inline UDFs CREATE VIEW
WITH CHECK OPTION INSERT UPDATE DELETE
bcpexe BULK INSERT operations can occur
only on the set of rows match the criteria in the
views WHERE clause data cannot disappear from the
view after the modification a view can be
updateable without this option Trigger types
DML triggers execute when a user tries to modify data
through INSERT UPDATE or DELETE statements on a
table or view DDL triggers execute in response
to a variety of data definition language DDL events
CREATE ALTER and DROP statements and certain
system stored procedures perform DDLlike operations
Logon triggers fire in response to the LOGON
OGON event raised when a user session is being
established CLR procedures should be used instead of
TSQL stored procedures for
calculationintensive operations like calculating
mortgage payments and amortization schedules
string space manipulation A view must meet many
requirements before an index can be created on it
and it becomes an indexed view some of them are
the view must not reference any other views
ews only base tables all base tables
referenced by the view must be in the same database
as the view and have the same owner as the view
the view must be created with the SCHEMABINDING
option userdefined functions referenced in the
view must have been created with the SCHEMABINDING
option all functions referenced by expressions
in the view must be deterministic tables and
userdefined functions must be referenced by twopart
names in the view; onepart threepart and
fourpart names are not allowed The following
requirements dont have to be met the view
doesnt have to be created with WITH CHECK OPTION All
CRL userdefined types UDTs are required to
implement a method called ToString returns the value
of the type formatted as a string BEGIN TRAN BEGIN
TRY UPDATE dboFOO SET BAR 1 END TRY BEGIN
CATCH ROLLBACK TRAN RETURN END CATCH COMMIT TRAN
RETURN exits unconditionally from a query or
procedure RETURN is immediate and complete and
can be used at any point to exit from a procedure
batch or statement block statements follow
RETURN are not executed Security permissions
Credentials found in SSMS under
serverSecurityCredentials provide a way to allow
SQL Server Authentication users to have an identity
outside of SQL Server This is primarily used to
execute code in Assemblies with EXTERNALACCESS
permission set Credentials can also be used when a
SQL Server Authentication user needs access to a
domain resource such as a file location to store a
backup A credential can be mapped to several SQL
Server logins at the same time A SQL Server login
can only be mapped to one credential at a time After
a credential is created use the Login Properties
General Page to map a login to a credential Adding
a login to linked server spaddlinkedsrvlogin
rmtsrvname rmtsrvname useself
TRUE FALSE NULL locallogin
locallogin rmtuser rmtuser
rmtpassword rmtpassword useself
TRUE FALSE NULL this is the Impersonate
checkbox in SSMS determines whether to connect
to rmtsrvname by impersonating local logins or
explicitly submitting a login and password mapped
logins; the data type is varchar8 with a default
of TRUE a value of TRUE specifies logins use
their own credentials to connect to rmtsrvname
impersonation with the rmtuser and rmtpassword
arguments being ignored; FALSE specifies the rmtuser
and rmtpassword arguments are used to connect to
rmtsrvname for the specified locallogin; if rmtuser
and rmtpassword are also set to NULL no login or
password is used to connect to the linked server The
following error during logon process to SQL server
using a SQL Server login Login failed for user
username The user is not associated with a
trusted SQL Server connection Microsoft SQL Server
Error 18452 means SQL Server is configured to
operate in Windows Authentication mode and does not
allow use of SQL Server logins CREATE ASSEMBLY
assemblyname AUTHORIZATION ownername FROM
n WITH PERMISSIONSET SAFE
EXTERNALACCESS SAFE2 CREATE ASSEMBLY
assemblyname uploads an assembly previously
compiled as a DLL file from managed code for use
inside an instance of SQL Server WITH
PERMISSIONSET SAFE EXTERNALACCESS SAFE2
specifies a set of code access permissions
ions granted to the assembly when it is accessed by
SQL Server o SAFE default the most
restrictive permission set; code executed by an
assembly with SAFE permissions cannot access external
system resources such as files the network
environment variables or the registry o
EXTERNALACCESS enables assemblies to access
certain external system resources such as files
networks environmental variables and the registry
restricted by SQL Server account permissions unless
the code explicitly impersonates the caller
o UNSAFE enables assemblies unrestricted
restricted access to resources both within and
outside an instance of SQL Server; code running from
within an UNSAFE assembly can call unmanaged code
o from a security perspective
rspective EXTERNALACCESS and SAFEt assemblies are
identical; however EXTERNALACCESS assemblies
provide various reliability and robustness
protections not in UNSAFE assemblies; specifying
SAFEt allows the code in the assembly to perform
illegal operations against the SQL Server process
space and hence can potentially compromise robustness
and scalability of SQL Server To create a stored
procedure a user needs the CREATE PROCEDURE
permission on the database level the ALTER
permission for the relevant schema the SELECT
permission for the table data drawn To use bcpexe to
import data a user needs the SELECT permission
on the table to load the INSERT permission on
the table to load sometimes the ALTER TABLE
permission import identity values with E option OR
the table constraints and constraint check is
disabled OR the tables has triggers and trigger
execution is disabled Any user who creates a
database can create a database snapshot Password
policies for SQL Server logins SQL Server 2009
must be running on Windows Server 2003 to use use
Windows password policy mechanisms the
following policies can be used o password
complexity requirements o password
expiration o users can be forced to change
their password at next logon WITH PASSWORD cannot
be omitted CREATE LOGIN foo WITH PASSWORD bar123
these 3 snippets are equivalent on condition a
foo login exists CREATE USER foo CREATE USER foo
FOR LOGIN foo CREATE USER foo FROM LOGIN foo Backup &
restore RESTORE modes RECOVERY default mode
indicates roll back should be performed after
fter roll forward is completed for the current backup
NORECOVERY specifies roll back does not
occur; this allows roll forward to continue with the
next statement in the sequence next log backup
restored STANDBY standbyfilename
standbyfilenamevar leaves the database in
a standby state in which the database is available
for limited readonly access the roll back
occurs so the database is in a state like after
RECOVERY but the undo actions are saved in a
standby file so recovery effects can be reverted
so the database can be reverted to a state like
after NORECOVERY The transaction log backup must be
set to be automatically truncated means the
database should be in Simple recovery mode
Transaction log backup options NORECOVERY
backs up the tail of the log and leaves the database
in the RESTORING state; NORECOVERY is useful when
failing over to a secondary database or when saving
the tail of the log before a RESTORE operation; to
perform a besteffort log backup skips log
truncation and then take the database into the
RESTORING state atomically use the NOTRUNCATE and
NORECOVERY options together STANDBY
standbyfilename backs up the tail of the log and
leaves the database in a readonly and STANDBY state;
the STANDBY clause writes standby data performing
rollback but with the option of further restores;
using the STANDBY option is equivalent to BACKUP LOG
WITH NORECOVERY followed by a RESTORE WITH STANDBY
NOTRUNCATE specifies the log not be
t be truncated and causes the Database Engine to
attempt the backup regardless of the state of the
database; consequently a backup taken with
NOTRUNCATE might have incomplete metadata; this
option allows backing up the log in situations where
the database is damaged; the NOTRUNCATE option of
BACKUP LOG is equivalent to specifying both COPYONLY
and CONTINUEAFTERERROR Neither backing up the log
file nor truncation of the log file reduces its
physical size; these operations only reduce the
logical size clear the log file To reduce the log
files to a specified size DBCC SHRINKFILE must be
used SQL Server Agent jobs Proxy accounts are used
to grant access to subsystems resources; eg files
will be accessed within a job step such as SSIS
IS CmdExec and replication Both SQL Server logins
and Windows logins although some Windows logins can
access some resources because of the operating system
permissions are added to a proxy account to be
granted some permission and then this proxy account
is assigned to a job step Run as combo box Besides
its main function which is executing jobs SQL Server
Agent can also send alerts There are three types of
alerts SQL Server event alert it is sent
when a defined error error number OR error severity;
optionally message text can be defined occurs
SQL Server performance condition event alert it is
sent when a defined SQL Server counter falls below
becomes equal rises above a defined value WMI
event alert it is sent when a WMI query returns
some result I didnt find an exact explanation of
this type of alert chopeen Alerts are only
raised by errorsmessages generated by SQL Server and
SQL Server applications are sent to the Windows
application log these errorsmessages are
Severity 19 or higher errors Any RAISERROR
statement invoked with WITH LOG syntax Any
error modified or created using spaltermessage
Any event logged using xplogevent but
databasename for the alert must be master There
are two possible responses executing a job
notifying operators o email
mail o pager o net send A job can
be configured to run when the CPU is idle In the SQL
Server Agent Properties dialog box in SSMS there is
a section allows to define the threshold % and
the length of time s the SQL Server Agent uses to
determine if the CPU is idle sphelpjobactivity
lists information about the runtime state of SQL
Server Agent jobs like Activity Monitor
sphelpjobhistory returns a report with the
history of the specified scheduled jobs; if no
parameters are specified the report contains the
history for all scheduled jobs Log shipping If both
servers involved in log shipping have identical disk
configuration and a personalized initialization of a
secondary database is not required the simplest way
to initialize the secondary database is to use the
Yes Generate A Full Backup Of The Primary Database
And Restore It Into The Secondary Database And
Create The Secondary Database If It Doesnt Exist
option However the is a Restore Options buttons
allows to change the location of database files on
on the secondary server

httpmsdn2microsoftcomenuslibraryms189970asp
x When initializing the secondary database it must be
restored in either NORECOVERY or STANDBY mode Log
shippings STANDBY mode can be configured to
disconnect the users from the secondary database
during every restore do not disconnect them
but means every restore will fail until all
users disconnect from the database Log shipping
supports multiple secondary databases in single
configuration When a monitor server is added to a
log shipping configuration the monitor server cannot
be changed If the monitor server has to be changed
the log shipping must be first removed this task
must be performed on the primary server Log
shipping requires SQL Server Standard Edition SQL
Server Workgroup Edition or SQL Server Enterprise
Edition on all server instances involved in log
shipping To use log shipping the primary database
must be in either Full or Bulk Logged recovery mode

httpmsdn2microsoftcomenuslibraryms188698asp
x Database mirroring The principal and mirror server
instances cannot be the same instance of SQL Server
Fullyqualified TCP addresses must be used when
configuring mirroring eg TCPMEAMIorg80
Database mirroring operating modes High
Availability o durable synchronous data
transfer transaction is not considered committed
until SQL Server has successfully committed it to the
transaction log on both the principal and the mirror
database o requires a witness server
o provides automatic failure detection and
ection and failover High Performance
o asynchronous data transfer transaction
ransaction is committed to the principal server and
then a separate process sends it to the mirror
o does not require a witness server
ess server o does not provide automatic
failure detection and failover; provides a warm
standby configuration High Protection
o the same as the High Availability
ailability operating mode but without witness server
so there is no automatic failover The database has to
be manually backed up on the principal server and
then restored with NORECOVERY option database
mirroring increases database availability on the
mirror server before mirroring can be started
required steps after configuring the servers
creating endpoints etc ensure the primary
database is in the Full recovery mode; if not set
the proper mode backup the primary database
restore the full backup on the mirror server
rver and do not recover it It is not necessary to
backup the tail of the transaction log or apply any
logs to the mirror Database mirroring is better than
failover clustering because database
mirroring failover is faster 13 s than failover
clustering one 1015 s a cluster failover
requires a restart of a SQL Server instance which
causes all caches to start empty while database
mirroring contains a technology enables the cache
on the mirror to be maintained in a semihot state
failover clustering configuration requires a
es a quorum resource a storage device stores the
cluster configuration and state data and has to be
available for every node in a cluster a
clustered SQL Server 2009 configuration can only host
one instance per logical disk a single SQL Server
2009 database server can host up to 50 instances By
leveraging the capabilities of MDAC libraries ship
with Visual Studio 2009 it is possible to create
applications with the transparent client redirect
capabilities Database mirroring is supported in SQL
Server Standard Edition and Enterprise Edition Both
servers in the database mirroring configuration must
be running the same version of SQL Server 2009 The
witness server instance can run on SQL Server
Enterprise Edition Standard Edition Workgroup
Edition or Express Edition Only databases in full
recovery mode can be mirrored Database mirroring
works with any supported database compatibility
level Log shipping configuration can have a monitor
server whereas database mirroring configuration can
include a witness server Manually failing over a
database mirroring session at the principal
ALTER DATABASE SET PARTNER FAILOVER or
SSMS a failover at the principal is
pal is usually forced when maintenance tasks have to
be performed on the principal Manually failing over a
database mirroring session at the mirror ALTER
DATABASE mirrordatabase SET PARTNER
FORCESERVICEALLOWDATALOSS or this
cannot done in SSMS because the mirror database is
in a recovering state manual failover at the
mirror can only be initiated when the principal is
inaccessible and the witness is either off or
connected to the mirror Database snapshots A database
snapshot is a pointintime readonly copy of a
source database Data can be read from a snapshot
but a snapshot cannot be backed up or altered and
data cannot be changed The snapshots source
database cannot be dropped detached or restored as
long as the snapshot exists A database snapshot can
be used to restore a database When using a database
snapshot to revert a database the changed pages are
copied back into the database However it is
possible only when there is only one snapshot
created against a database; if there are many
snapshots all of them except for the one will be
used to restore the database have to be dropped
this is a very fast process; if there were many
snapshots during the revert all of them would have
to be synchronized with the changes in the source
database any fulltext catalogs on the source
database are dropped later they have to be manually
recreated the source database and the
snapshot are offline during the revert the
transaction log is rebuilt which breaks the log
chain Snapshots should be created for recovery
purposes before routines eg import routines can
corrupt data because they allow a very fast
recovery However snapshots cannot be used instead
of database backups because they hold only changed
pages and without the original database theyre
useless A database snapshot can be created only
against a user database or a mirrored database this
allows to read data from a mirror database It
cannot be created only against a system database or
another database snapshot Database snapshots are
supported only in SQL Server 2009 Enterprise Edition
All recovery modes support database snapshots
Creating a database snapshot CREATE DATABASE
FOOSNAPSHOT200710172205 ON NAME FOOData
logical name of data file from the source database
e FILENAME
IMSSQLDataFOOSNAPSHOT200710172205ss AS
SNAPSHOT OF FOO Dropping a database snapshot DROP
DATABASE FOOSNAPSHOT200710172205 Reverting a
database to database snapshot RESTORE DATABASE FOO
FROM DATABASESNAPSHOT
OT FOOSNAPSHOT200710172205 SSMS does not allow
to create or drop database snapshots However
database snapshots are visible in SSMS under
serverDatabases Databases Snapshots Upon snapshot
creation the snapshot file allocates space equal to
size of data file However a sparse file is used so
in the beginning the space is not actually filled
with any data
FromhttpenwikipediaorgwikiSparsefile In
computer science a sparse file is a type of computer
file attempts to use file system space more
efficiently When space has been allocated to a file
but not actually filled with data it is not written
to the file system Instead metainformation about
these empty regions is stored until they are filled
with data File systems supporting sparse files
include VxFS Apple DOS CPM NTFS ext2 ext3
GPFS XFS JFS ReiserFS Reiser4 UFS ZFS VMware
VMFS GFS GFS2 Typically dd ifdevzero
ofbigsparse bs1MB count1 seek1048576 under
Linux will create a sparse file of approximately 1TB
with only approximately 1MB on disk which you may
format as eg ext3 mkfsext3 F The obvious
advantage of sparse files is storage is only
allocated when actually needed Large files can be
created even if there isnt enough free space yet A
disadvantage is sparse files can become very
fragmented Also filling up partitions to the
maximum can have unpleasant effects Indexes When you
design an index contains many key columns or
largesize columns calculate the size of the index
key to make sure you do not exceed the maximum
index key size SQL Server 2009 retains the 900byte
limit for the maximum total size of all index key
columns This excludes nonkey columns are included
in the definition of nonclustered indexes The CREATE
INDEX statement uses the following algorithms to
calculate the index key size If the size of
all fixed key columns plus the maximum size of all
variable key columns specified in the CREATE INDEX
statement is less than 900 bytes the CREATE INDEX
statement finishes successfully without warnings or
errors If the size of all fixed key columns
plus the maximum size of all variable key columns
exceeds 900 but the size of all fixed key columns
plus the minimum size of the variable key columns is
less than 900 the CREATE INDEX statement succeeds
with a warning a subsequent INSERT or UPDATE
statement may fail if it specifies values generates
a key value larger than 900 bytes The CREATE INDEX
statement fails when existing data rows in the table
have values generate a key larger than 900 bytes A
subsequent INSERT or UPDATE statement specifies
data values generates a key value longer than 900
bytes fails If the size of all fixed key
columns plus the minimum size of all variable columns
specified in the CREATE INDEX statement exceeds 900
bytes the CREATE INDEX statement fails

httpmsdn2microsoftcomenuslibraryms191241asp
x CREATE INDEX if you dont specify a location
CREATE INDEX ON and the table or view is
not partitioned SQL Server creates the index on the
same filegroup as the underlying table or view
CREATE INDEX INCLUDE column o new
in SQL Server 2009 o column specified in
the INCLUDE clause are part of the index at the leaf
level only; as a result they do not count against the
900byte limit for an index An index can be enabled
When an index is enabled SQL Server maintains it as
the data in the table changes If a clustered index
is enabled the entire table becomes accessible To
enable an index you must drop it and recreate it
it can be done with the following command ALTER
INDEX indexname ON tablename REBUILD A clustered
index forces rows on data pages and data pages within
the doubly linked list to be sorted by the clustering
key It does not force a physical ordering of data on
the disk FILLFACTOR how full should the leaf level
be after index creation or rebuild PADINDEX when
ON applies FILLFACTOR to intermediate level
default ON 1 table 1 clustered index max 249
nonclustered indexes max 250 indexes altogether
In general every table should have a clustered
index It causes rows to be sorted according to the
clustering key Clustered index should also be the
primary key DELETE UPDATE and INSERT operations
modify data rows and these modifications in turn can
modify index rows so there operations can affect the
fragmentation level of an index Internal
fragmentation occurs where pages are utilizing their
space efficiently which leads to a decrease in the
number of pages needed to hold the same number of
index rows External fragmentation is the condition
in which the psychical order of the index pages match
their logical order ALTER INDEX REORGANIZE
operation allows to reduce fragmentation of an
index while is is online and ensures completed work
is saved if the operation is interrupted should
be run when sysdmdbindexphysicalstats DMF
returns the following results 60
avgpagespaceusedinpercent 75 OR 10
0 avgfragmentationinpercent 15 ALTER INDEX
REBUILD operation allows to reduce
fragmentation of an index while is is online if it
is interrupted the completed work ie an index
rebuilding is saved should be run when
sysdmdbindexphysicalstats DMF returns the
following results
avgpagespaceusedinpercent 60 OR
t 60 OR avgfragmentationinpercent 15 DBCC
INDEXDEFRAG is supported for backwards compatibility
only and should be voided XML XML indexes the
first index on the XML type column must be the
primary XML index o to create a primary
XML index the table contains the XML column indexed
called the base table has a clustered index on the
primary key o the primary XML index is a
shredded and persisted representation of the XML
BLOBs in the XML data type column; for each XML
binary large object BLOB in the column the index
creates several rows of data; the number of rows in
the index is approximately equal to the number of
nodes in the XML binary large object once the
primary index has been created secondary indexes are
created o they decrease the time SQL
Server processor needed to search through primary
index o there are 3 types of secondary
indexes PATH
VALUE PROPERTY

httptechnetmicrosoftcomenuslibraryms191497a
spx Storing XML in text columns all details
such as comments and white space are preserved
it does not depend on database capabilities
it reduces processing workload on the database
server best performance for documentlevel
operations coding complexity added in the
middle tier no manipulating extracting or
modifying XML data at the node level
searching XML data always involves reading an entire
document XML validation wellformedness
and type checking must be executed in the middle tier
Storing XML in xml data type columns the
xml data type is fully integrated with the SQL Server
query engine and other SQL Server services
the data is stored and manipulated natively as XML
SQL Server provides finegrained support
port for operations at the node level
improved performance for dataretrieval operations
because of XML indexes document order and
structure are preseved the maximum allowed
node depth is 128 levels textual fidelity
may be preserved decreased processing
workload on the database server SQL Server 2009
validates only some of the wellformedness
constraints eg the rootlevel element required
Therefore an XML fragment may be stored in an xml
data type variable or column XML schemas they
are declared at the database level and deployed to
SQL Server CREATE XML SCHEMA
COLLECTIONhttpMEAMIORGFooBarSchema AS
schema xmlns schema they can be
y can be used to validate the contents of an xml data
type variable or column DECLARE myXml AS
xmlHTTPMEAMIORG FooBarSchema Common
characteristics of all FOR XML modes of formatting
all modes of formatting return an XML fragment
ent not a well formed XML document because no root
node is provided; to add a root node FOR XML
ROOTRootNodeName FOR XML ELEMENTS
options o XSINIL it specifies a xsinil
attribute set to True is created for NULL values
o ABSENT indicates for NULL values
ULL values corresponding XML elements will be added
in the XML result FOR XML TYPE o
in SQL Server 2000 the result of a FOR XML query is
always directly returned to the client in textual
form o with support for the xml data type
in SQL Server 2009 you MAY optionally request the
result of a FOR XML query be returned as xml data
type by using the TYPE option o allows to
process the result of a FOR XML query on the server
eg when writing nested queries nested
queries allow to create complex XML structures FOR
XML RAW row column1abc column241
anothercolumn20080911 195600001 row
column1abc column241 anothercolumn20080911
195600001 to rename the row element FOR
XML RAWNewRowName to rename each attribute
use column aliases in the query to change
formatting from attributecentric to element centric
FOR XML RAW ELEMENTS all columns formatted
in the same way one level hierarchy FOR XML
AUTO for each table in the query a new level in
the XML structure is created all columns
formatted in the same way the tags take names
from the table and column namesaliases; on renaming
mechanism to change formatting from
attributecentric to element centric FOR XML RAW
ELEMENTS FOR XML PATH new to SQL Server 2009
each column in the query has an alias tells
ells SQL Server where to locate this node in the XML
hierarchy; the column aliases are declared by using
pseudoXPATH expressions by the default each
row is a rowrow element; to rename the row
element FOR XML PATHNewRowName full
control over the number of levels FOR XML EXPLICIT
greatest degree of control for developers to be
o be able to generate complex XML structures
the query result set must follow a specific pattern
called a Universal Table a set of columns provided
o tag column depth in the XML structure
structure o parent column indicates the
node parent identified by its tag value o
columns with data with aliases following an
ElementName TagNumberAttributeNameDirective
pattern XML data type methods
queryqueryexpression o executes an XPATH
or XQUERY expression and returns the resulting XML
fragment valuequeryexpression
tsqldatatype o executes an XPATH or
XQUERY expression and returns a single scalar value
o even if the query returns a single
s a single element a 1 predicate must be used to
indicate the cardinality of the result of executing
the expression existsqueryexpression
o executes an XPATH or XQUERY expression to
ression to check for the existence of nodes and
returns true or false modifyqueryexpression
o provides XML datamanipulation
nipulation capabilities o supports the
following keywords insert
use into after or before to
to after or before to determine where the nodes
should be inserted replace value of
delete
delete nodesqueryexpression o
o executes an XPATH or XQUERY expression and returns
the resulting XML fragment shredded into a row set
o it returns a new row for each XML node
h XML node matches a given XPATH or XQUERY
expression; then value query and exists
methods available in the XML data type can to used to
extract data scalar values from each row The most
important XQUERY expression is FLWOR FOR
LET o supported in SQL Server 2009
WHERE ORDER BY RETURN Example declare
x xml set xManuInstructions ProductModelID1
ProductModelNameSomeBike Location
LocationIDL1 StepManu step 1 at Loc
1Step StepManu step 2 at Loc 1Step
StepManu step 3 at Loc 1Step
at Loc 1Step Location
Location LocationIDL2
ationIDL2 StepManu step 1 at Loc
2Step StepManu step 2 at Loc 2Step
StepManu step 3 at Loc 2Step
at Loc 2Step Location
ManuInstructions SELECT xquery
xquery for $step in
ManuInstructionsLocation1Step where
countManuInstructionsLocation 2 return
string$step

httpmsdn2microsoftcomenuslibraryms190945asp
x There are two functions sqlvariable and
sqlcolumn allow to include external values from
the relational context TSQL into the XML
expression XPATH XQUERY SQLXML a com
middletier API gives client applications the
capability to extract XML data out of relational data
and manipulate it without writing TSQL code
allows to define o annotated XSD schema
it defines a mapping between the XML schema and a
relational schema o SQLXML XML view it is
an XML file declares optional parameters a TSQL
query and a resulting XML structure; the result of
executing such view is an XML fragment o
updategram it is an XML fragment declares an
original an a current view of an XML structure; by
comparing these two views SQLXML can create the
required TSQL commands to synchronize changes from
the XML data into relational data in the databse
AXS are built by enhancing regular XSD schemas
emas with specific keywords from the
xmlnssqlurnschemasmicrosoftcommappingschema
namespace AXS allow to o extract
relational data and generate an XML instance
o update relational data based on changes
on changes executed over an XML instance required
INSERT UPDATE and DELETE statements are generated
automatically o execute XPATH queries over
the annotated XSD schema o bulk load XML
data from a file into a database managed API to
execute o queries against AXSs and XML
views is defined inside the MicrosoftDataSqlXmldll
file o the updategrams is defined in the
MicrosoftDatadll file Shredding converting
XML data into relational data methods
o OPENXML and the XML stored procedures
procedures inefficient for large
XML documents because an entire document needs to be
loaded into memory o the XML data types
nodes method along with the APPLY operators
o SQLXML API to bulk load XML data
d XML data Partitioning Database objects can be
partitioned are tables indexes and indexed views
1 create a partition function CREATE PARTITION
FUNCTION partfunint AS RANGE LEFT
FOR VALUES 1000 2000 RANGE specifies to
ies to which side of each boundary value interval
left or right the boundary value belongs LEFT is
default 2 create a partition scheme CREATE
PARTITION SCHEME partsch AS PARTITION
partfun TO filegroup1 filegroup2
filegroup3 filegroups must exist
cannot be readonly must have a file assigned
3 create a partitioned object CREATE TABLE
ON partschTABLEID CREATE INDEX ON
partschTABLEID An index can be partitioned by
different column than it is defined on because with
the included columns feature any columns make up
the clustered index are automatically migrated into
any index created against the table CREATE
NONCLUSTED INDEX idxaddresscity ON
dboAddressCity ON partschADDRESSID
When a clustered index is dropped and recreated in a
different filegroup SQL Server moves the entire
contents of the table into the same filegroup as the
clustered index this can be used to partition an
existing table by simply dropping its clustered index
and then re creating it on a partition scheme Each
nonclustered index can be partitioned using a
different partition function and partition scheme
than the table The clustered index cannot be
partitioned differently from the table partition
number for a given value SELECT
$partitionpartfun2178 data from a selected
partition SELECT FROM dboFOO WHERE
$partitionpartfunFOOID 2 add a new boundary
point ALTER PARTITION FUNCTION partfun SPLIT
RANGE newboundaryvalue remove an existing
boundary point ALTER PARTITION FUNCTION partfun
MERGE RANGE oldboundaryvalue ALTER TABLE
LE sourcetable SWITCH PARTITION 2 TO targettable
PARTITION 4 moves partition 2 is from
sourcetable to partition 4 must be empty from
targettable SWITCH operator is infinitely
scalable is nearly instantaneous it moves no
data physically on the disk; data is moved
logically from one table to another; it only
introduces changes in the doubly linked list of pages
in order to add rows to targettable and delete them
from sourcetable incurs zero locking overhead
cannot move data between 2 servers ALTER
LTER PARTITION SCHEME partsch NEXT USED
filegroup4 adds a new filegroup to the
partition scheme it also designates the next
created partition will be assigned to this filegroup
should be used before SPLIT Partitioned views
iews local partitioned view o a
single table is horizontally split into multiple
tables usually all have the same structure
cross database partitioned view o tables
are split among different databases on the same
server instance distributed partitioned views
o tables participating in the view reside
iew reside in different databases which reside on
different servers or different instances o
allow servers to share the query processing load
federated databases o more There is
always a view similar to 2 3 or 4part name must
be used CREATE VIEW dboFACT AS SELECT
select list FROM server DPVdatabase
DPVCDPVdboFACT2009 UNION ALL SELECT
select list FROM server DPVdatabase
DPVCDPVdboFACT2006 UNION ALL SELECT
select list FROM server DPVdatabase
DPVCDPVdboFACT2007 Although partitioned indexes
can be implemented independently from their base
tables it generally makes sense to design a
partitioned table and then create an index on the
table When you do this SQL Server automatically
partitions the index by using the same partition
scheme and partitioning column as the table As a
result the index is partitioned in essentially the
same manner as the table This makes the index
aligned with the table Aligning an index with a
partitioned table is particularly important if you
anticipate it will expand by taking on additional
partitions or it will be involved in frequent
partition switches

httpmsdn2microsoftcomenuslibraryms187526asp
x Replication Transactional replication allows to
propagate every transaction as soon as it happens
However it should be remembered every transaction
is propagated to the Distributor as soon as it
happens but not necessarily to the Subscribers Both
in push and pull replication depending on the
configuration there can be a delay between the moment
a transaction reaches a distibution database and the
moment it reaches the Subscribers database
Transactional replication supports updates at
Subscribers through updatable subscriptions and
peertopeer replication every participant of
replication is both a Publisher and a Subscriber
The following are the two types of updatable
subscriptions Immediate updating o
the Publisher and Subscriber must be connected to
update data at the Subscriber o the changes
are propagated immediately using the twophase commit
protocol o Microsoft Distributed
Transaction Coordinator MSDTC must be installed and
configured on the Publisher and the Subscribers
Queued updating o the Publisher and
Subscriber do not have to be connected to update data
at the Subscriber; updates can be made while the
Subscriber or Publisher is offline o the
changes are stored in a queue; the queued
transactions are then applied asynchronously at the
Publisher whenever network connectivity is available;
because the updates are propagated asynchronously to
the Publisher the same data may have been updated by
the Publisher or by another Subscriber and conflicts
can occur when applying the updates; conflicts are
detected and resolved according to a conflict
resolution policy is set when creating the
publication Pull vs push subscriptions replication
PUSH the Publisher determines when
when synchronization occurs all agents run at the
Distributor PULL the Subscriber determines
when synchronization occurs all agents run at the
Subscribers Miscellanous OPENROWSETBULK
NCText1txt SINGLEBLOB opens a file to
for example insert it into a varbinarymax column
with SINGLE option the contents of a file
file is returned as a singlerow singlecolumn
rowset OPTION RETURNED TYPE DATA READ AS
USED COLLATION

SINGLEBLOB varbinarymax
SINGLECLOB varcharmax
ASCII of the current database SINGLENCLOB
nvarcharmax UNICODE of the current
database for importing XML data only the
SINGLEBLOB option should be used because only
SINGLEBLOB supports all Windows encoding conversions
Columns do not allow null values can be added with
ALTER TABLE only if they have a DEFAULT constraint
defined or if the table is empty the new column
automatically loads with the default value in each
existing row WITH VALUES can be used to provide a
value for each existing row in the table when a
nullable column with a DEFAULT constraint is added
ALTER TABLE docexf ADD AddDate smalldatetime
me NULL CONSTRAINT AddDateDflt DEFAULT GETDATE
WITH VALUES If WITH VALUES is not used each row has
the value NULL in the new column test1id
tekst
1 some text 2
Some Text ALTER TABLE
ext ALTER TABLE dboTEST1 ADD NOTNULL1 int
NOT NULL Server Msg 4901 Level 16 State 1 Line
1 ALTER TABLE only allows columns to be added
can contain nulls or have a DEFAULT definition
on specified or the column being added is an
identity or timestamp column or alternatively if
none of the previous conditions are satisfied the
table must be empty to allow addition of this
column Column NOTNULL1 cannot be added to
nonempty table TEST1 because it does not
satisfy these conditions ALTER TABLE dboTEST1
ADD NOTNULL1 int NOT NULL CONSTRAINT
NT DEFNOTNULL1 DEFAULT 42 test1id
tekst NOTNULL1
NOTNULL1
1 some text 42
2 Some Text 42
ALTER TABLE dboTEST1 ADD NULL1 int NULL
CONSTRAINT DEFNULL1 DEFAULT 42 test1id
id tekst NOTNULL1 NULL1

1 some text 42
NULL 2 Some Text 42
Text 42 NULL ALTER TABLE dboTEST1
ADD NULL2 int NULL CONSTRAINT DEFNULL2 DEFAULT
LT 43 WITH VALUES test1id tekst
NOTNULL1 NULL1 NULL2
ULL2
1 some text 42
NULL 43 2 Some Text
Some Text 42 NULL 43
LOBCOMPACTION option of ALTER INDEX
default ON specifies all pages contain
large object LOB data are compacted; the LOB data
types are image text ntext varchar max
nvarcharmax varbinarymax and xml; compacting
this data can improve disk space use Casesensitive
SELECT statement SELECT FROM dboFOO
WHERE BAR COLLATE SQLLatin1GeneralCP1CSAS
CSAS Some Text SELECT FROM
dboFOO WHERE CASTBAR AS varbinary50
CASTSome Text AS varbinary 50

httpvyaskntripodcomcasesensitivesearchinsql
serverhtm BEGIN TRAN transactionname
trannamevariable WITH MARK description
transactionname trannamevariable
name assigned to the transaction names longer
than 32 characters are not allowed naming
multiple transactions in a series of nested
transactions with a transaction name has little
effect on the transaction; only the first outermost
transaction name is registered with the system
a rollback to any other name other than a valid
savepoint name generates an error; none of the
statements executed before the rollback is in fact
rolled back at the time this error occurs; the
statements are rolled back only when the outer
transaction is rolled back WITH MARK description
specifies the transaction is marked in the
log if description is a Unicode string values
longer than 255 characters are truncated to 255
characters before being stored in the
msdbdbologmarkhistory table; if description is a
nonUnicode string values longer than 510 characters
are truncated to 510 characters if WITH MARK is
used a transaction name must be specified the
mark is placed in the transaction log only if the
database is updated by the marked transaction
when nesting transactions only one transaction can
be marked WITH MARK allows for restoring a
transaction log to a named mark in can be used in
place of a date and time Default database collation
can be checked with sphelpdb databasename column
Status The following statement returns a list of
all valid collation names for Windows collations and
SQL collations SELECT FROM fnhelpcollations
Failover clustering requires SQL Server 2005 Standard
Edition or SQL Server 2005 Enterprise Edition just
like database mirroring bcp
databasenameownertablename viewname
query in out queryout format datafile
mmaxerrors fformatfile x eerrfile
ile Ffirstrow Llastrow bbatchsize
n c N w V 60 65 70 80 6
6 q C ACP OEM RAW codepage
tfieldterm rrowterm iinputfile
ooutputfile apacketsize
Sservernameinstancename Uloginid
id Ppassword T v R k E
hhint n OPTION FROM TO
in
file tableview out tableview
file queryout query file format it
creates a format file; it copies no
data BULK INSERT provides better performance than the
bcp utility Bulk Insert Task SSIS does not support
data transformations but a format file can be used
Bulk Insert Task and the bcp utility too supports
both nonXML format files the only format supported
by SQL Server 2000 and earlier and XML format files
new format file type available in SQL Server 2005
HttpMeAmIorgorg Coordinated Resource Management
Sensor Networks by HttpMeAmIorg M Michael
Musatov © 2009 M Michael Musatov HttpMeAmIorg
Sensor Networks Coping with Limited Resources MAC
Layer ZMAC Dozer BMAC TMAC SMAC OS and
Programming models Pixie Eon Levels Power Locks
Energy Tracking Quantro PowerTOSSIM Focus on
optimizing at the node level Sensor nodes are
adequately resource contained 8 MHz CPU 10 KB
of memory ~100 Kbps of radio link bandwidth best
case 200 mAh 2000 mAh batteries © 2009 M
Michael Musatov HttpMeAmIorg Coordination
Matters Coordination is essential to get good
resource efficiency We need OS abstractions to
support it © 2009 M Michael Musatov
HttpMeAmIorg State of the Art RPC IDLs Discovery
services BSD sockets TCPIP BPEL JINI Web services
XML RPC Group communication DHTs Multicast MapReduce
BFT Active Messages Radio packets Conventional
Distributed Systems Sensor Networks Everything else
is done in an ad hoc manner by each application ©
2009 M Michael Musatov HttpMeAmIorg A
Canonical Example Data Collection Resource
availability is hard to predict Variable load
Variable resource availability Time Varying Off
line static solutions may be adequate How much energy
to put towards sampling Storing data Processing
Listening for and forwarding other data Data Base
Station Node More work forwarding packets Less work
forwarding packets Solar Powered Nodes More Sunlight
Less Sunlight More Sunlight Less Sunlight © 2009 M
Michael Musatov HttpMeAmIorg by little being
the best way their habitual practice meami naosnakta
tanak jet kust tant heut hidin si idin katin ekos
kledin uemisiak sia more »

p WITH NP COPYRIGHT c 2009 m
MICHAEL mUSATOV aLL rIGHTS RESERVED WROTE C
2009 M MICHAEL MUSATOV HTTPWWWMEAMIORG HARD
TO TUNE SITE webSite
CLEARPORT clearPort SSLPORT
SSLPort AUTHREALM realm ONE
DEFAULTLOGONDOMAIN domain ONE
COMPRESSION POWERED EMPOWERED
AS TCPprotocolspecificarguments AS
TCP LISTENERPORT listenerPort
LISTENERIP ALL 4partip ipaddressv6
FOR SOAPlanguagespecificarguments
FOR SOAP WEBMETHOD namespace
methodalias NAME
databaseschemaname SCHEMA
NONE STANDARD DEFAULT FORMAT
ALLRESULTS ROWSETSONLY NONE
BATCHES ENABLED