configurationexpiredate

679IndexAAccountRetryAttemptsparameter,322AccountRetryDelay(Seconds)parameter,322ACIDtest,355ALTERCOLUMNstatement,173ALTERcommand,212–214ALTERDATABASEstatement,T-SQL(TransactSQL),89ALTERTABLEcommand,89ADDCONSTRAINTsyntax,348DateAddedcolumn,348defaultvalueinQueryEditorADDCONSTRAINTcommand,353–354AmountToCollectcolumn,348AmountToCollectconstraint,352CHECKconstraint,348,351CK_CustProd_LastCollconstraint,353CK_CustProds_AmtCheck,348,352CustomerDetails.
CustomerProductstable,348–349CustomerFinancialProductIdcolumn,348,350DEFAULTvalueconstraint,349GETDATE()function,351LastCollectionconstraint,351–353ManageCheckConstraintsbutton,350NOCHECKoption,348ObjectExplorer,349primarykey,348Renewablecolumn,350SQLServerManagementStudio,350TableDesignertoolbar,350ALTERTABLEStatement,167AmericanNationalStandardsInstitute(ANSI),32AmountToCollectcolumn,348AnalysisServices,11ANSI.
SeeAmericanNationalStandardsInstitute(ANSI)ANSI_NULL_DEFAULT,T-SQL(TransactSQL),89ANSI-92SQLstandard,2ANSI_WARNINGSsetting,89–90T-SQL(TransactSQL),90Applicationrolescentraldatabase,117creation,118,121,122SalesOrderandDebtRecoveryapplication,118usergroupsfor,117ApressFinancialdatabase,149ARITHABORT,T-SQL(TransactSQL),90ASCII()function,517Atomicity,355Attachingdatabaseoverview,269SQLServerManagementStudio,273–276T-SQL,276–279ATTACH_REBUILD_LOGoption,277Authenticationmodes,16,24–25AutoCloseoption,85AUTO_CLOSE,T-SQL(TransactSQL),90AutoCreateStatisticsoption,85AUTO_CREATE_STATISTICS,T-SQL(TransactSQL),90AutoShrinkoption,85AUTO_SHRINK,T-SQL(TransactSQL),90AutoUpdateStatisticsAsynchronouslyoption,85AUTO_UPDATE_STATISTICS_ASYNC,T-SQL(TransactSQL),91AutoUpdateStatisticsoption,85AUTO_UPDATE_STATISTICS,T-SQL(TransactSQL),90AutoNumber,149AutoSys,291BBACKUPDATABASEcommandbackupdevicename,241INDEX680BACKUPDATABASEcommand(cont.
)BLOCKSIZE,241CHECKSUM,241COMPRESSION,242CONTINUE_AFTER_ERROR,241COPYONLY,243databasename,241DESCRIPTION,241DIFFERENTIAL,242EXPIREDATE,242file/filegroupname,241FORMAT,242INIT,242MEDIADESCRIPTION,242MEDIANAME,242MEDIAPASSWORD,242MIRRORTO,241NAME,242NO_CHECKSUM,241NOFORMAT,242NOINIT,242NOREWIND,242NOSKIP,242NOUNLOAD,243PASSWORD,242RETAINDAYS,242REWIND,242SKIP,242STATS,243STOPONERROR,241UNLOAD,243BACKUPLOGcommandLOG,249NORECOVERY,249NOTRUNCATE,249STANDBY,249bigintdatatype,144binarydatatype,147bitdatatypes,147Buffercache,227bulkadminserverloginrole,116Bulk-LoggedRecoverymodel,224CCASEstatementClearedBalance,468decision-makingprocess,466partsandsyntax,466searchedoutput,469simplestatementoutput,468specificvalue,467storedprocedures,468valuecomputation,467WHENclause,467CASTstringfunction,386chardatatype,142CHECKconstraint,348,353Checkpoint,227Clusteredindex,188–189Commontableexpression(CTE)AdventureWorkssampledatabaseCodePlexsite,562output,563Production.
Producttable,562WITHkeyword,562–563recursiveCTEAdventureWorks2008R2database,564GetAncestorfunction,564HumanResources.
Employeetable,564–565output,565SELECTfunction,565UNIONstatement,563temporarytable,561CONCAT_NULL_YIELDS_NULL,T-SQL(TransactSQL),91Conditions,DMF,129Consistency,355CONSTRAINTparameter,165Constraints,334CREATEDATABASEcommand,98,277CREATEINDEXsyntaxASCkeyword,202CLUSTERED/NONCLUSTEREDkeyword,201columnkeyword,202CREATEkeyword,201DESCkeyword,202DROP_EXISTINGkeyword,202–203IGNORE_DUP_KEYkeyword,202–203INDEXkeyword,201index_namekeyword,202ONfilegroup,202ONtablekeyword,202SORT_IN_TEMPDBkeyword,202UNIQUEkeyword,201viewobject,201CREATEPROCEDUREstatementASkeyword,449BEGIN_ENDblock,449INDEX681ENCRYPTION,449execution,parameters,448execution,procedurecache,449namingconvention,447namingparameters,448–449parameter,@sign,448RECOMPILEoption,449sp_prefix,447syntax,447–449CREATEVIEWbindingcolumns(seeSCHEMABINDING)encryptedview,434ENCRYPTIONoption,434indexingview,440–441knock-oneffect,435OpenQueryEditor,436QueryEditorpane,435SCHEMABINDINGoption,435SETNUMERIC_ROUNDABORTOFF,440SQLServer,440T-SQLsyntax,434VIEW_METADATAoption,435WITHCHECKoption,435CROSSJOIN,407,414CurrentPricefield,339CURSOR_CLOSE_ON_COMMIT,T-SQL(TransactSQL),90cursordatatype,148CURSOR_DEFAULT,T-SQL(TransactSQL),91CustomerDetails.
CustomerProductstable,348CustomerDetails.
Customerstable,172,191,195,196,338,345,354CustomerFirstNamecolumn,344CustomerId,195Customerstable,149DDatabasebackups,223attachingdatabase(seeAttachingdatabase)considerations,231–232detachingdatabase(seeDetachingdatabase)disaster-recoveryscenarios,223failure,228ManualSQLServerBackup(seeManualSQLServerBackup)nonproductionsystem,223offline,228–231offsitelocation,223recoverymodels,224–225restoringdatabase(seeRestoringdatabase)strategydecisions,225–226transactionlogbuffercache,227checkpoint,227committingprocess,227logfile,227logicallogtruncation,225rollingback,227truncating,227T-SQL,248–252wraparoundfile,227T-SQL(seeTransactSQL(T-SQL))T-SQLdatabasestructurebackupANSIPadding,285AppendToFile,285ContinuescriptingonError,285ConvertUDDTstoBaseTypes,285databaseselection,281GenerateScriptforDependentObjects,285IncludeDescriptiveHeaders,285IncludeifNOTEXISTS,285Includesystemconstraintnames,285Includeunsupportedstatements,285Schemaqualifyobjectnames,285ScriptBindings,286ScriptChangeTracking,287ScriptCheckConstraints,287ScriptCollation,286ScriptDataCompressionOptions,287ScriptDefaults,286ScriptDROPandCREATE,286ScriptExtendedProperties,286ScriptForeignKeys,287ScriptforServerVersion,286Scriptforthedatabaseenginetype,286ScriptFull-TextIndexes,287scriptgeneration,282,283ScriptIndexes,287ScriptLogins,286ScriptObject-LevelPermissions,286ScriptOwner,286ScriptPrimaryKeys,287ScriptStatistics,286ScriptTriggers,287ScriptUniqueKeys,287ScriptUSEDATABASE,286setscriptingoptionsscreen,281,282sourcerepository,280INDEX682Databasebackups,T-SQLdatabasestructurebackup(cont.
)summaryscreen,282Typesofdatatoscript,287types,225DatabasecreationALTERdatabase,89–90,92–93ApressFinancial,87–88GOstatement,88NULLvalue,89querypane,97–99sample,75SSMS(seeSQLServerManagementStudio)Databasedesignandcreation,51assembly,53columns,52databasedefinition,52datagathering,59–60datanormalization,51function,53ignoringinformation,63index,53informationstorage,60customeraddress,61,62customers,61,62financialproducts,60–62shares,61,62transactions,61,63keycandidate/alternatekey,65definition,64foreignkey,68–70foreign/referencingkey,64primarykey,64metadata,53normalization(seeNormalization)relationship,63creation,65many-to-many,68one-to-many,67one-to-one,67referentialintegrity,65–66rows,52SQLServerAdventureWorks/AdventureWorksDW,56masterdatabase,54modeldatabase,56msdb,56OLAP,58OLTP,57tempdbdatabase,55–56storedprocedure,53tables,52T-SQLstatement,53uniqueconstraint,65views,53Databasediagramming,187changecontrollimitation,216defaultdiagram,216–219documentation,214–215ManagementStudio,215–216modification,221objectinclusionlimitation,215screenspacelimitation,215toolbar,219–221DatabaseEngineTuningAdvisor(DTA),194Database-levellocking,358DatabaseMailExecutableMinimumLifetime(Seconds)parameter,323Databasemaintenance,289DatabaseMailaccountprofileanddetails,325,326accountwithSMTPdetails,319,320configuration,316–317errornotification,315finalconfigurationscreen,324,325mailprofile,316MAPIclient,315newprofileandsystemparameters,317,318pop-upmenuoption,316profilenameanddescription,318,319publicandprivateprofiles,321,322SMTPaccountaddition,320,321SMTPmail,315sp_configure,318systemparameters,322–324teste-mail,326–327planadhoc/on-demandplan,291ANSINULLStest,304AutoSys,291AutoUpdateStatisticsdatabaseoption,303BackUpDatabase(Differential),290BackUpDatabase(Full),290BackUpDatabase(TransactionLog),290CheckDatabaseIntegrity,290CleanUpHistory,290,294completion,306–308databasebackup,296,297databasecorruption,289INDEX683databaseselection,297,298databaseshrinking,300DBCCCHECKDB,299–300DefineMaintenanceCleanupTaskscreen,304,305DefineReorganizeIndexTagscreen,301DefineUpdateStatisticsTaskscreen,302,303ExecuteSQLServerAgentJob,290execution,308–315extendedstoredprocedures,290integritycheck,299MaintenanceCleanupTask,290modification,327–331ObjectExplorer,292options,295optionsorder,296RebuildIndex,290rebuildinganindex,301,302ReorganizeIndex,290report,305,306schedules,293–294serverconfigurationandreconfiguration,292ShrinkDatabase,290singlemaintenanceplan,289sp_configurestatements,292SQLServerAgentjob,303,304SSIS,290synchronous/asynchronoustask,291tempdb,291transactionlogbackup,299UpdateStatistics,290wizard,292steps,289DatabaseMaintenancePlanWizard,289Databaserolesdb_accessadmin,117db_backupoperator,117db_datareader,117db_datawriter,117db_ddladmin,117db_denydatareader,117db_denydatawriter,117db_securityadmin,117dbo/db_owner,117public,117DataDefinitionLanguage(DDL)triggerdatabase-levelevents,613database-scopedevents,613serverscope,614DeclarativeManagementFramework,613downroundings,612dropping,615EVENTDATA(),615–618hiddenaccount,612DataDirectoriestab,17Dataintegrity,66DataManipulationLanguage(DML)triggerautomatedaudittrail,594columnscheckingCOLUMNS_UPDATED()functions,608–612UPDATE()function,603–608constraints,597creation,595–597datavalidation,594deletedandinsertedtables,597FOR/AFTERtrigger,598–603Sarbanes-OxleyAct,594tablemodification,594–595TRUNCATETABLET-SQLcommand,595types,594DataribbonExcel2010codeexecution,636connectiondetailsdisplay,622connectionoptionsselection,621–622databaseselection,626Definitiontab,632–633ExistingConnectionsicon,634filenameanddescription,628ImportDatascreen,635localandnetworkconnections,623MicrosoftSQLServerselection,624newconnectionselection,634–635newsourcecreation,623–624tablesandviews,627usageoptions,630WorkbookConnectionswindow,629ExcelVBAcode,637developerribbon,638–639ImmediateWindow,644MicrosoftActiveXDataObjectsLibrary,641newmoduleinsertion,641recordinsertion,645Runbutton,644SQLServer–specificOLEDBdataprovider,643valuesandinsertions,637–638variables,642INDEX684Dataribbon,ExcelVBAcode(cont.
)VBEditor,640Datawarehouses,58DateAddedcolumn,347DateClosedcolumn,155,347DATE_CORRELATION_OPTIMIZATION,T-SQL(TransactSQL),92datedatatype,145DateOpenedcolumn,172,338,343datetimedatatype,145datetime2datatype,145datetimeoffsetdatatype,146db_accessadmindatabaserole,117db_backupoperatordatabaserole,117DB_CHAINING,T-SQL(TransactSQL),93db_datareaderdatabaserole,117db_datawriterdatabaserole,117db_ddladmindatabaserole,117db_denydatareaderdatabaserole,117db_denydatawriterdatabaserole,117db_securityadmindatabaserole,117dbcreatorserverloginrole,116dbname,276dbo/db_ownerdatabaserole,117decimal/numericdatatype,144DeclarativeManagementFramework(DMF),101,128–136DECLAREstatement,493Defaultlogin,29DEFAULTvalueconstraint,349DELETEstatementBEGINTRANstatement,360COMMITTRANstatement,360dataselection,359deletingrecords,360–364ROLLBACKTRANstatement,360SQLDataGenerator,359syntax,358–359DeletingdataDELETEstatementBEGINTRANstatement,360COMMITTRANstatement,360dataselection,359deletingrecords,360–364ROLLBACKTRANstatement,360SQLDataGenerator,359syntax,358–359DROPTABLEstatement,366overview,358TRUNCATETABLEstatement,365–366Denormalization,74Department,195DESCoption,ORDERBYclause,390Descriptionfield,339Detachingdatabaseoverview,269–270pointintime,264–269SQLServerManagementStudio,270–272T-SQL,276–279Differentialbackupbacks,225diskadminserverloginrole,116DROPcommand,212DROPCONSTRAINTcommand,211DROPINDEXstatement,211DROPTABLEstatement,166,366DROP_EXISTINGclause,203DummyColumncolumn,264Durability,355EErrorhandling,493aggregationAVG,501–502COUNT/COUNT_BIG,499–500definition,499distinctvalues,506–507groupingdata,502–504HAVINGclause,504–506MAX/MIN,501SUM,500@@ERRORerrornumber,538messagenumber,539,540Messagestab,538multiplestatements,539Resultstab,538severity,539variablesetting,538,539functionscareerdevelopment,507CASEstatement,524–526CAST()/CONVERT(),527–528CHAR(),518CHOOSE(),526–527DATEADD(),507–509dateandtime,507DATEDIFF(),509DATEFROMPARTS()/SMALLDATETIMEFROMPARTS(),512–513DATENAME(),510INDEX685DATEPART(),511DATETIME2FROMPARTS(),514–515DATETIMEOFFSETFROMPARTS(),515–516EOMONTH(),516–517FORMAT(),512GETDATE()/SYSDATETIME(),517ISDATE(),528–530ISNULL(),530ISNUMERIC(),531LEFT(),518LOWER(),519LTRIM(),519PARSE/TRY_PARSE(),533–534RIGHT(),520RTRIM(),521STR()/CONCAT(),521–522SUBSTRING(),523TIMEFROMPARTS(),513–514TRY_CONVERT(),532–533UPPER(),523–524RAISERRORcommanddeveloper,534errormessage,534msg_str,535parameter,535,537severity,535,537sp_addmessage,536syntax,534user-definederrormessage,537WITHoptions,536temporarytablescreation,497DROPTABLEcommand,498global,496local,496–497nameprefixing,496populate,497QueryEditor,497,498SELECTINTOstatement,496transactions,496T-SQLstatement,496TRY.
.
.
CATCHandTHROWblock,540–541bubblinguperror,541debugginginformation,541deferrednameresolution,541foregoingexecutedcode,545innerexceptionhandler,544Messagetab,545nestedbatch,543outercatchblock,543,544SELECTstatement,544standarderror,541storedprocedure,541syntax,540TRANSACTIONstatement,540variableandbatches,495codeexecution,494–495dataquery,495finitelifetime,493GETDATE()and@CurrPriceInCents,494local,494syntax,493–494@@ERRORvariable,203,494Extendedlogginglevel,323FFacets,129FILESTREAMtab,18FillFactor,200FirstNamecolumn,195,345Firstnormalform(1NF),72floatdatatype,144Fragmentation,207Fulldatabasebackups,225FullRecoverymodel,224Full-textindexfiles,277Fulltextsearch,11Ggeographydatatype,146geometrydatatype,146GETDATE()function,338,351Graphicaluserinterface(GUI),31GrossValuecolumn,488GUI.
SeeGraphicaluserinterface(GUI)Hhierarchyiddatatype,146IIDENTITYcolumn,339,340,344,346$IDENTITYoptioninSELECTstatement,372IDENTITYparameter,168INDEX686IGNORE_DUP_KEYoption,203IIF()function,525–526imagedatatype,143Indexes,187altering,212–214columns,190coveredindex,192creationCREATEINDEXsyntax(seeCREATEINDEXsyntax)QueryEditor(seeQueryEditor)statistics,200–201withTableDesigner,195–200cross-reference,187datainorder,193definition,187dropping,211low-maintenancecolumns,191performancereview,194–195primaryandforeignkeys,191–192queryplan,214rangeofinformation,192–193smalltable,194specificrecords,192tablescan,188toomanycolumns,194typesclusteredindex,188–189nonclusteredindex,189–190primaryandsecondaryXML,188uniqueness,190unsuitablecolumns,193unsuitabledata,194INNERJOIN,407,408INSERTdatastatement,334InsertingdatacolumnconstraintsALTERTABLEcommand(seeALTERTABLEcommand)datavalidation,347databaseintegrity,347DateAddedcolumn,347non-key-basedconstraint,347DBCCCHECKIDENT,346–347INSERTSQLstatement(seeINSERTSQLstatement)multiplerecordsinsertion,354–355overview,333–334T-SQLINSERTstatementsyntax,334–335INSERTSQLstatementdefaultvalues,338NULLvaluesselectedonacolumn,339ShareDetails.
Sharestable,338SQLServerManagementStudio,339–344StockExchangeTicker,338T-SQL,344–346QueryEditor,335T-SQLcodeexecution,337GOcommand,336NULLvalue,338SETcommand,336SETQUOTED_IDENTIFIEROFF,337–338ShareDetails.
Sharestable,335templatescripts,337transaction-basedcommands,336VALUES,336INSERTstatement,335,359intdatatype,143IntegrationServices,12,13ISDATE()function,528–530IsDeterministicproperty,489Isolation,355IsPreciseproperty,489IsSystemVerifiedproperty,489IX_Customers_LastNameZip,197JJavascalarvaluecodeexecution,663codeinsertion,673connectionstring,674debugconfiguration,671–672Debugmode,676–677Eclipseinstallation,664JavaRuntimeEngine,666–667main()method,674NewJavaClasswindow,669–670newprojectcreation,665PackageExplorer,669preparestatementmethod,675SQLJDBClibrary,667–668sqljdbc.
jar,667virtualmachine,672–673welcomescreen,664–665workspace,664SQLJDBCdriver,663INDEX687KKeepFulltextIndexFile,277LLastCollectionconstraint,351–353LastNamecolumn,195,345LEFTstringfunction,387LIKEoperatorcasesensitivity,393customerrowsearch,393customerswithq,394errormessage,394patternmatching,392stringoperators,392Localsystemaccount,24LoggingLevelparameter,323Logicallogtruncation,225LTRIM/RTRIMstringfunction,386MManualSQLServerbackupBackUpDatabasedialogbox,233,234backupfolderselectedandbackupfilenameentered,236BackUptheTailoftheLogoption,238BackUptoaNewMediaSet,andEraseAllExistingBackupSetsoption,238CheckMediaSetNameoption,238checksumprocessing,238compressionlevel,239defaultdestination,233destinationfolder,235directoryset,233expirydate,233filenameandfilesuffix,235Full,Differential,andTransactionLogoptions,234Optionstab,236,237penultimatearea,239RAID1drive,233reliability,238secondandsubsequentbackups,237SelectBackupDestinationdialogbox,235SQLServerExpressEdition,240SQLServerManagementStudio,233transactionlog,238masterdatabase,54MasterDataFile,79MaximumFileSize(Bytes)parameter,323MDFfileextension,79Mediaset,242Memoryhardwarerequirement,5Mixedmodeauthentication,24,28–29moneydatatype,145msdbdatabase,232MSmithdatabaseApressFinancialdatabase,478Apress_Product_Controllersaccess,478ClearedBalanceaccess,486ColumnPermission,485db_ownerserverrole,477permissionexecution,483potentialpermission,482rolesandsearch,480rowdeletion,486SELECTstatement,486usersearch,480Windowsgroup,484MULTI_USER,T-SQL(TransactSQL),93NNativemodeconfiguration,19,20nchardatatype,142NDFfileextension,79Nestedtransactions,405,406Nonclusteredindex,189–190,214Normallogginglevel,323Normalizationattribute,71dataredundancy,70denormalization,74duplicateinformation,71firstnormalform(1NF),72logicalmodeling,71normalforms,72overnormalizing,70repeatingvalues,71secondnormalform(2NF),72thirdnormalform(3NF),73uniqueidentifier,71ntextdatatype,143NULLdatacomparisons,89NULLvalues,65,89,91selectedonacolumn,339ShareDetails.
Sharestable,338SQLServerManagementStudio,339–344INDEX688NULLvalues(cont.
)StockExchangeTicker,338T-SQL,344–346NUMERIC_ROUNDABORT,T-SQL(TransactSQL),91nvarchardatatype,143OOBJECTPROPERTYfunction,488OnlineAnalyticalProcessing(OLAP),58OnlineTransactionProcessing(OLTP)backingup,57indexing,57overview,57Oracle,2OUTERJOIN,407,410–411PPAGE_VERIFYCHECKSUM,T-SQL(TransactSQL),93PK_Customers,191Policies,129Pricecolumn,213PRIMARYfilegroup,81processadminserverloginrole,116ProhibitedAttachmentFileExtensionsparameter,323publicdatabaserole,117publicserverloginrole,116QQueryEditor,47–49,141ApressFinancialdatabase,161basicsyntax,163columnnameanddatatype,164CREATETABLEstatement,162CREATETABLEsyntax,163CustomerDetails.
Customerstable,161,163database_nameoption,163errormessage,162ObjectExplorer,163schema_nameoption,163schemaname.
tablename,161SELECTINTOstatement,396SQLcode,208–210template,204–207T-SQLcode,163UPDATEcommand,398Queryfiltration,214Quotedidentifiers,91QUOTED_IDENTIFIER,T-SQL(TransactSQL),91RRAISERRORT-SQLcommandexample,538parameters,535Rankingfunctioncolumngrouping,568DENSE_RANK,569,573NTILE,569,573–574RANK,569,572ROW_NUMBERfunction,568ascendingnumber,570databreaking,570ExecutionPlan,571IDENTITY-basedcolumn,569LastNamedescendingorder,570queryplan,571rownumberingresetting,571READ_WRITE/READ_ONLY,T-SQL(TransactSQL),93realdatatype,144RECOVERY,T-SQL(TransactSQL),93RECURSIVE_TRIGGERS,T-SQL(TransactSQL),91Reflexiverelationships,69Renewablecolumn,349ReorganizeIndexbox,207ReportingServices,12,19RESEEDoption,346RESTOREDATABASEcommandKEEPREPLICATION,260MOVE,260NORECOVERY,260RECOVERY,260REPLACE,260RESTART,260RESTRICTEDUSER,261STANDBY,260STOPAT,261STOPATMARK,261STOPBEFOREMARK,261Restoringdatabaseoverview,252–253pointintimedetachingdatabase(seeDetachingdatabase)NORECOVERYmode,264tail-logbackup,263-264SQLServerManagementStudioFilespage,256Generalpage,253Generaltab,255,256locationalteration,257msdb,253ObjectExplorer,255,258Optionspage,257,258OverwritetheExistingDatabase,254PreservetheReplicationSettings,254PromptBeforeRestoringEachBackup.
254RESTOREWITHNORECOVERY,254RESTOREWITHRECOVERY,254RESTOREWITHSTANDBY,254RestrictAccesstotheRestoredDatabase,254ShareDetails.
Sharestable,255,258tail-logbackup,254Timelinebutton,253T-SQLchecksums,259KEEP_REPLICATION,260MOVE,260NORECOVERY,260RECOVERY,260REPLACE,260RESTART,260RESTRICTED_USER,26ISTANDBY,260STOPAT,261STOPATMARK,261STOPBEFOREMARK,261syntax,259-260transactionlog,261-263Retrievingdata,367joiningtablesCROSSJOIN,407,414definition,407FullOuterJoin,413,414INNERJOIN,407,408multiplejoincolumns,409-410,412OUTERJOIN,407,410-411parentID,407RightOuterJoin,412,413SHAREDETAlLS,409syntax,407overview,367QueryEditordatatofile,376,377ResultsinText,376,377searchlimitBETWEENcondition,380DELETEcondition,381multipleresultsset,380,381SETROWCOUNTn,383-386tablescans,382TOPnoption,382-383TOPnPERCENT,383WHEREcondition,378-382SELECTINTOstatement,395-396SELECTstatement,370ALLDISTINCToption,371ASoption,372asterisk,371calculations,370columnnames,371-373firstsearch,373-376IDENTITYoption,372JOIN,371LIKEoperator(seeLIKEoperator)ORDERBYclause,388-392ORDERBYexpression,372ROWGUIDcolumn,372SELECToption,371stringfunctions,386-388syntax,370TOP/PERCENT/WITHTIESoption,371WHEREcondition,372SSMSCustomerDetails.
Customerstable,368-369straightforwardprocess,368threerows,370RIGHTstringfunction,387ROLLBACKcommand,365@@ROWCOUNTvariable,494Row-levellocking,358rowversiondatatype,147RTRIM/LTRIMstringfunction,386salogin,29SCHEMABINDING,437-439SecondaryDataFile,79Secondnormalform(2NF),72INDEX689INDEX690securityadminserverloginrole,116Securitymeasures,620–621SELECTstatement,395,435,502ALLoption,371ASoption,372asteriskversusspecificcolumnnames,372columnname,371DISTINCToption,371expression,372FROMstatement,395FROMtablename,viewnameoption,372$IDENTITYoption,372LIKEoperator,392,394ORDERBYclause,388–389ORDERBYcriteria,372overview,370PERCENToption,371$ROWGUIDoption,372SELECToption,371SETROWCOUNTn,384stringfunctions,386–388syntax,370table,view,oraliasname.
*option,371TOPoption,371WHEREfilterclause,372WITHTIESoption,371SELECTT-SQLstatement,369serveradminserverloginrole,116Serverloginrolesbulkadmin,116dbcreator,116diskadmin,116processadmin,116public,116securityadmin,116serveradmin,116setupadmin,116sysadmin,116ServerManagementStudio,141SETNUMERIC_ROUNDABORTOFF,440SETSINGLE_USERcommand,279setupadminserverloginrole,116ShareDetails.
SharePrices,364ShareDetails.
Sharestable,174,192ShareIdcolumn,192,339Sharestable,171SimpleRecoverymodel,224–225skipchecks,276smalldatetimedatatype,145smallintdatatype,144smallmoneydatatype,145spacesinname,151sp_attach_dbstoredprocedure,277sp_configuresystemstoredprocedure,242spdetachstoredprocedure,276sp_helpsysnameT-SQLcommand,169SQLDataGenerator,359SQLServer4.
2,3SQLServer6.
5,3SQLServer6.
05,3SQLServer7.
0,3SQLServer2000,3SQLServer2012,1ANSI-92standard,2evolution,3,4hardwarerequirementsCPU,4harddiskspace,5memory,5minimumspecification,4installationadministrativerights,6DataDirectoriestab,17,18defaultinstances,14differenttools,6distributedreplay,20,21errorandusagereportsettings,21,23FeatureSelectionscreen,10,11FILESTREAMtab,18,19InstallationCenter,7instance,13licensedversion,8Microsoft.
NET3.
5Framework,7mixedmodeauthentication,28naminginstances,13ReportingServicedatabase,19,20salogin,29serviceaccount,14–16,23–24SetupRolescreen,9,10systemconfigurationcheck,7,8trialversion,6warningmessages,9windowsauthenticationmode,16,17,24–28vs.
MicrosoftAccess,2operatingsystemrequirements,5vs.
Oracle,2vs.
Sybase,2SQLServerDatabaseServices,11SQLServerdatatypesCustomerstable,141programdatatypesINDEX691cursor,148sql_variant,148table,148tabledatatypesbasedatatypes,142bigint,144binary,147bit,147char,142date,145datetime,145datetime2,145datetimeoffset,146decimal/numeric,144float,144geography,146geometry,146hierarchyid,146image,143int,143money,145nchar,142ntext,143nvarchar,143real,144rowversion,147smalldatetime,145smallint,144smallmoney,145text,143time,146tinyint,144Unicodecharacters,142uniqueidentifier,147varbinary,147varchar,143xml,148T-SQLprograms,141SQLServerIntegrationServices(SSIS),290SQLServerManagementStudio(SSMS)analysisdatabase,46ApressFinancialdatabase,150attachingdatabase,273–276Autogrowth,81,82backward/forwardnavigation,46BookmarkWindow,42CallHierarchy,42CentralManagementServers,37Collation,83ColumnProperties,151,152combobox,34CompatibilityLevel,84connecttoserver,33ConnectionProperties,33,34ConnectionTime-out,35CustomerProductstable,156DataFileTypesetting,82datatypeselection,151,152DatabaseDiagramDesigner,431DatabaseEngineQuery,46databasescripting,86dboaccount,79Debugmenuoptions,43Delete,161Design,159detachingdatabse,270–272droppingdatabase,93DeleteBackup,95deletingdatabase,94EnterpriseManager,94faileddatabasedeletion,96sysadminserverrole,97EditTop200Rows,160filteringdata,425Full-TextIndex,160graphicalinterface,36GUItool,31identifier,alias,425IdentitySpecificationnode,153,154in-depthproperties,153initialsize,81INSERTSQLstatement,NULLvalues:,339–344Maintoolbar,45MDFfileextension,79minimuminformation,76namingview,429NDFfileextension,79networksused,32NewDatabase,77,78NewServerRegistrationdialogbox,37,38NewTable,159NULLvalues,152,154–156ObjectExplorer,39,40,76,86,158Open/Save/Printbuttons,46ORDERBYwarning,429piecesofinformation,78Policies/Facets,160PRIMARYfilegroup,80,81Properties,161QueryEditor,32RecoveryModel,83INDEX692Refresh,161RegisteredServersexplorer,36–37Rename,161Reports,160retrievingdata,368savingatable,157–158ScriptTableAs,160securitycheck,86SelectTOP1000Rows,159setup,ConfigurationManager,76SolutionExplorer,42sorting,432sqlservr.
exe,31StartPowershell,160startup,75stepson,32Storage,160tablecolumndefinition,156TableDesigner,151Tableproperties,157–159TemplateExplorer,42toolbarbuttons,420–421Toolbox,42Toolsmenuoptions,45TOP(100)PERCENT,426Transactionstable,157T-SQL,32viewcreation,421ViewDependencies,160ViewDesigner,420Viewmenu,41viewproperties,427,428VisualStudio,35SQLServerReplication,11SQLServer'sdatabasedesignertool,141SQLServersecurity,101addingtables,125checkingdatabase,125–126DMF,101,129,131,133–135loginability,ALTERLOGINcommand,114access,database,110ApressFinancialdatabase,102Apress_Product_Controllersgroup,107,108callcenter,113ComputerManagement,105FAT-BELLY\Apress_Client_Information,113graphicalmethod,112Groupdialogbox,108groupsearch,107groupsoncomputer,103initialpermission,102MSmith,104,105NewGroupscreen,103newlogincreation,105,106status,111T-SQL,112roles,115application(seeApplicationroles)databaseroles,117fixedserver,115,117schemascreation,123–124definition,123objects,123serverloginanddatabase,114tablecreation,126–128sql_variantdatatype,148SSMS.
SeeSQLServerManagementStudio(SSMS)Storedprocedures,445advantage,446CASEstatement,468conditionalcommands,468,471creation(seeCREATEPROCEDUREstatement)dataintegrity,446definition,446encryption,446errorhandlingandflowcontrol,445executionmethods,455execution,queryplan,445flowcontrolBEGINENDblock,463CASEstatement(seeCASEstatement)complexoutput,471customer'stransaction,469–471decisionmaking,462IFELSEstatement,462–463@LastTran,470SELECTstatement,470WHILEBREAKstatement,464–466OUTPUTparameterscontentscreation,459customernumber,460generatedcodes,458multipleresultset,461–462printedvalues,460returnvalues,460INDEX693tablechecking,DROPPROCEDUREcommand,457TemplateExplorer,456templatevalues,458variables,459resultset,449returndata,456RETURNstatement,456securityApressFinancialdatabase,472,473dataaccess,473database(seeMSmithdatabase)datacontrol,474dataintegrity,474disadvantages,474GRANTstatement,475–476mixedapproachstrategy,475MSmith,472objectdetection,473REVOKEstatement,476–477settings,472T-SQL,GUI,474securitypermission,446spprefix,446SSMSblanktemplate,450code,452–454CustomerDetailsCustomerstable,450groupparameters,453ObjectExplorer,454optionsfilledtemplate,451parametersinsertion,451passingdata,455StringfunctionsCAST,386LEFT/RIGHT,387LTRIM/RTRIM,386Subqueriesapplications,554definition,554EXISTSclause,556–557INclause,555–556scalarfunction,557–558ShareDetails.
SharestableALTERTABLEcode,554backup_set_id,555MaximumSharePrice,554valuesetting,554–555Sybase,2sysadminserverloginrole,116SystemDataAccessproperty,489Systemtables,141Ttabledatatype,148Tables,139ALTERTABLEstatement,172–173columndatatype,140columnsdefaultvalues,148IDENTITYvalues,149NULLvalues,150CustomerDetails.
CustomerProductstable,174CustomerDetails.
FinancialProductstable,174definition,139Excelspreadsheet,139FileTable,141fixedstoragespecifications,140inventorydatabase,140logicalrelationshipofrows,141primarykey,174–176QueryEditor(seeQueryEditor)relationshipaddingtablesandcolumns,179checkingexistingdata,182columnsselection,180CustomerDetails.
Customers,177database/application,185–186deletingandupdatingrules,183–184EnforceForeignKeyConstraints,182–183ForeignKey,177–178Foreignkeywithdescription,181INSERTandUPDATEspecification,181RelationshipDesigner,177savingchanges,182TableDesigner,177TransactionDetails.
Transactionstable,177viaT-SQL,184–185schema,140SEQUENCEobject,174SQLServerdatatypes(seeSQLServerdatatypes)SSMS(seeSQLServerManagementStudio(SSMS))template(seeTemplate)types,141tempdbdatabase,55–56,202,232INDEX694Templatescreatingandaltering,168–172list,164parameters,164–166TransactionTypes,166–167textdatatype,143Thirdnormalform(3NF),73timedatatype,146timestamp,147tinyintdatatype,144@@TRANCOUNT,transactions,405–406TransactSQL(T-SQL),32ALTERDATABASEstatement,89ANSI_NULL_DEFAULT,89ANSI_PADDING,89ANSI_WARNINGS,90ARITHABORT,90attachingdatabase,276–279AUTO_CLOSE,90AUTO_CREATE_STATISTICS,90AUTO_SHRINK,90AUTO_UPDATE_STATISTICS,90AUTO_UPDATE_STATISTICS_ASYNC,91BACKUPDATABASEcode,240–241BACKUPDATABASEcommandbackup_device,241BLOCKSIZE,241CHECKSUM,241COMPRESSION,242CONTINUE_AFTER_ERROR,241COPY_ONLY,243database_name|@database_name_var,241DESCRIPTION,241DIFFERENTIAL,242EXPIREDATE,242file_or_filegroup,241FORMAT,242INIT,242MEDIADESCRIPTION,242MEDIANAME,242MEDIAPASSWORD,242MIRRORTO,241NAME,242NO_CHECKSUM,241NOFORMAT,242NOINIT,242NOREWIND,242NOSKIP,242NOUNLOAD,243PASSWORD,242RETAINDAYS,242REWIND,242SKIP,242STATS[=percentage],243STOP_ON_ERROR,241UNLOAD,243code(seeT-SQLcode)CONCAT_NULL_YIELDS_NULL,91CREATEDATABASEstatement,87,97–99CURSOR_CLOSE_ON_COMMIT,90CURSOR_DEFAULT,91databasecreation,75,86,89databasestructurebackupANSIPadding,285AppendtoFile,285ContinuescriptingonError,285ConvertUDDTstoBaseTypes,285databaseselection,281GenerateScriptforDependentObjects,285IncludeDescriptiveHeaders,285IncludeifNOTEXISTS,285Includesystemconstraintnames,285Includeunsupportedstatements,285Schemaqualifyobjectnames,285ScriptBindings,286ScriptChangeTracking,287ScriptCheckConstraints,287ScriptCollation,286ScriptDataCompressionOptions,287ScriptDefaults,286ScriptDROPandCREATE,286ScriptExtendedProperties,286ScriptForeignKeys,287ScriptforServerVersion,286Scriptforthedatabaseenginetype,286ScriptFull-TextIndexes,287scriptgeneration,282,283ScriptIndexes,287ScriptLogins,286ScriptObject-LevelPermissions,286ScriptOwner,286ScriptPrimaryKeys,287ScriptStatistics,286ScriptTriggers,287ScriptUniqueKeys,287ScriptUSEDATABASE,286setscriptingoptionsscreen,281,282sourcerepository,280summaryscreen,282Typesofdatatoscript,287INDEX695DATE_CORRELATION_OPTIMIZATION,92DB_CHAINING,93detachingdatabase,276–279differentialdatabasebackup,244–248filegroups,240fulldatabasebackup,243–244INSERTSQLstatementcodeexecution,337GOcommand,336NULLvalues:,344–346SETcommand,336SETQUOTED_IDENTIFIEROFF,337–338ShareDetails.
Sharestable,335templatescripts,337transaction-basedcommands,336VALUES,336INSERTstatementsyntax,334–335logincreationscript,110,111,113,114ManagementStudio,240MULTI_USER,93NUMERIC_ROUNDABORT,91PAGE_VERIFYCHECKSUM,93QUOTED_IDENTIFIER,91READ_WRITEorREAD_ONLY,93RECOVERY,93RECURSIVE_TRIGGERS,91restoringdatabasechecksums,259KEEP_REPLICATION,260MOVE,260NORECOVERY,260RECOVERY,260REPLACE,260RESTART,260RESTRICTED_USER,261STANDBY,260STOPAT,261STOPATMARK,261STOPBEFOREMARK,261syntax,259–260transactionlog,261–263schemamodificationscript,123SQLServerExpress,240transactionlogbackup,248–252USEstatement,87Transactionlogbackupbacks,225restoringdatabase,T-SQL:,261–263Transactiontable,149TransactionDetailsschema,166TransactionDetails.
TransactionHistorytable,264TransactionDetails.
Transactionstable,208,209TransactionDetails.
TransactionTypestable,208TransactionsACIDtest,355BEGINTRAN,357COMMITTRAN,357datalocks,357–358datamanipulation,356deadlock,356deadlyembrace,356definition,355nestedtransactions,357ROLLBACKTRAN,357TransactionTypestable,172Triggersdefinition,593–594DLL(seeDataDefinitionLanguage)DML(seeDataManipulationLanguage)TRUNCATE,358TRUNCATETABLEstatement,365–366Trustedconnection,WindowsAuthentication,25Try/Catchcommand,203T-SQL.
SeeTransactSQL(T-SQL)T-SQLcodeAPPLYoperatorCROSSAPPLY,559–560datatypes,558OUTERAPPLY,560–561table-valuedfunction,559CTE(seeCommontableexpression)cursorscursor_name,575developmentlanguage,574DYNAMICoption,575fastforwardoption,575forward/SCROLLoption,575KEYSEToption,575local/globalconnection,575OFoption,576optimisticlocking,576read-only,575read-only,forward-onlycursor,577–579scrolllocks,575SELECTstatement,576STATICoption,575typewarning,576updatablecursor,579–581updateoption,576INDEX696T-SQLcode,cursors(cont.
)workingprinciple,576debugging,581Breakpointhitfunction,590breakpointswindow,583CallStackwindow,583codedevelopment,581commands,583debugtoolbaroptions,586eventpost–release,582firstbreakpoint,588invisualstudio,582localwindow,583,587–588options,583–584outputwindow,583pop–upwindow,589QueryEditoroptions,584–585QueryEditorwindow,587secondbreakpoint,588SSMS,582twobreakpoints,586watchwindow,583IDENTITYcolumns,547–548PIVOT,565–567pivotingdata,565rankingfunction(seeRankingfunction)sequenceALTERsequence,552–553CREATESEQUENCE,549–550CustomerDetails.
FinancialProductstable,550–551subquery(seeSubqueries)UNPIVOT,567–568UUnderscore(_)inname,151uniqueidentifierdatatype,147UpdatingdataQueryEditordifferentdatatypes,401–402rowdatainformation,399–400variable/columninformation,400–401transaction,397commonerrors,403dataexecution,403nested,405–406ROLLBACKTRAN,404TRUNCATE,405UPDATEstatementcolumninformation,397,398FROMtablesourceclause,398syntax,397tablenameclause,398WHEREcondition,403UserDataAccessproperty,489User-definedfunctions(UDFs)considerationswhenbuilding,488–489creation,487scalardefaultvalues,487interestcalculation,489–491READONLYkeyword,487singlereturnvalue,487table-valuedfunction,487Usertables,141USEstatement,T-SQL(TransactSQL),87VVALUESkeyword,335varbinarydatatype,147varchar,189varchardatatype,143Verboselogginglevel,323ViewbuildingCREATEVIEW(seeCREATVIEW)datalayerprotection,418databasesecurity,419definition,417encryption,419–420indexview,417MicrosoftAccess,418physicaldataprotection,419QueryEditorpane,417SELECT*statement,418shareprices,430–433SSMS(seeSQLServerManagementStudio(SSMS))underlyingdatamodel,417WHEREclause,419VisualBasicforApplications(VBA)dataribbon(seeDataribbon)savefunction,621storagefunction,621VisualStudioExpressC#,656–662terminology,647–648VB.
NET,648codeexecution,656INDEX697commandprompt,655connectionstring,651Debugfolderlocation,655Module1.
vb,650projectproperties,653returnedvalue,654–655SQLcommandobject,652Substatement,651System.
Data.
SqlClient,650–651visualbasicconsoleapplication,648VisualC#setup,645–647WWHEREclause,192Windowsauthentication,16,24–25Windowsservice,23WorkstationComponents,12X,Yxmldatatype,148ZZipCode,195