queryselectedindexchanged

Symbols!
=operator,usingwithself-joins,202$(variablename)format,usingwithscriptingvariables,66%wildcardcharacter,using,176.
(period),followingMekeywordwith,388operator,filteringrowswith,178–179'(commentmarker),usingwithTryclause,396AAccess2SQLEXPRESSproject,displayingDatasheetviewforShipperstablein,27–28Accessdatabasefiles,ADO.
NETconnectionsto,451–453Accessdatabases,lookinguporderhistoryin,564–569Accesslinkedtables,connectingtoSSEfrom,28–29Accessprojects,connectingtoSSEfrom,27–28adhocqueriesexplanationof,168runningonserverinstances,169–171usingWHEREclausewith,175AddConnectiondialogboxinDatabaseExplorer,settingsin,516,517Addmethod,invokingforRowscollection,502Administratoraccount,advisoryaboutuseof,14ADO.
NETandconnectionstoSSEandAccessdatabasefiles,451–453DataAdapterrequirementof,486designatingparametersin,486gettinghelpwithconnection-stringconstructionin,450–453openingandclosingconnectionstoSQLServerdatabaseswith,448–450overviewof,445performingdatadefinitionanddatabasemaintenancewith,471–483programmingCommandobjectswith,454–455programmingConnectionobjectswith,447–453returningforward-only,read-onlyrowsetsin,462–470returningsinglevaluewithCommandobjectin,455–462ADO.
NETarchitecture,overviewof,446–447ADO.
NETclasses,segmenting,446ADO.
NETConnectionobjects,closing,457ADO.
NETProviders,overviewof,445–446.
adpfiles,explanationof,27advancedstyle,runningSSEInstallationWizardin,12AdventureWorkssampledatabasedownloading,24usingwithSSEandAccesslinkedtables,28–29AFTERtriggers.
Seealsotriggersdescriptionof,292rollingbackin,295–297syntaxfor,293–294aggregationgroupingfor,186–189performingwithGROUPBYclause,185aliasdatatype,using,120aliasnames,usingwithdatasourcesinsubqueries,209ALLkeywordusingwithresultsets,231–232usingwithSELECTstatements,158@alphalocalvariable,usingwithsubqueries,213ALTERAUTHORIZATIONONSCHEMAstatement,using,352ALTERpermission,effectof,326ALTERTABLEstatement,modifyingtableswith,128anchorelement,usingwithHTMLpages,427–428ANDkeyword,usingwithBETWEENoperator,179Applicationclass,eventsin,436applicationform,Designandformattedviewsof,397–399applicationrole,explanationof,309applicationvariablescreatingcontainingreports,438–439layingformforreadingof,440outputfromsampleforreadingof,441–442populatingandreadingfrom,435–436reading,440–441settingupsampleforreadingof,436–437approximatedatatypes,specificationsfor,112–114archivesolutiondemonstrating,301–302settingupfortriggers,298–301arrays,usingwithVBEdatatypes,375Index575ASkeyword,usingwithtriggers,294ASC,usingwithORDERBYclause,215ASP.
NETcontrolsin,419creatingandlinkingHTMLpageswith,427–429developmentconceptsrelatedto,409–411draggingButtoncontrolsto,417optionsforserver-sidecodein,416returningformatteddatefromCalendarcontrolin,419–423ASP.
NETDevelopmentWebServer,advantagesanddisadvantagesof,429ASP.
NETpagesformatting,417–418running,419usingMekeywordwith,418ASP.
NETprojectsaddingWebFormsto,416creatingandlinkingHTMLpageswith,427–429exploring,413–414message-for-the-dayapplication,424–427starting,411–413.
aspxfileextension,explanationof,413,427assignmentstatementsusingwithoutputparametersofstoredprocedures,278usingwithVBEdatatypes,381–385AttachDBFileNameargumentuseof,4usinginconnectionstrings,5authentication.
SeealsoSQLServerauthentication;WindowsauthenticationforSSEandVisualStudio2003connections,25supportfor,22AuthenticationModescreen,displaying,15AUTHORIZATIONclause,usingwithCREATESCHEMAstatement,350–351AutoFormattask,usingwithCalendarcontrolinASP.
NETproject,422auto-closefeaturedisablingandreenabling,95–96releasingdatabaseandlogfileswith,89significanceof,4,74storingdatabasefileswith,83turningoff,89Bbackingupandrestoringdatabaseswithfullandbulk-loggedmodels,99–100performingdifferentialbackupsandrestores,103–106performingfulldatabackupandrestore,102–103recoverymodelsfor,99–102backupandrestorescripts,cleaningup,106backupfiles,creatingfordatabases,93basicstyle,runningSSEInstallationWizardin,12.
batfileruns,scheduling,69–70batchesinT-SQL,terminatingwithGOkeyword,173–174batchingsqlcmdstatements,65–66,67–69bcpcommand-lineutilityversusBULKINSERTstatement,290BETWEENoperator,filteringrowswith,179–181bigintintegerdatatype,specificationsfor,110binary()datatype,using,117binarystringdatatypes,overviewof,117BindingNavigatorcontrol,modifyingforusewithDataGridViewcontrols,534–535BindingSourceclass,usingwithWindowsFormcontrols,542bitdatatype,overviewof,109Book1.
csvfile,usingSqlBulkCopyclasswith,510–514BooksOnlinewebsite,16,163BooleandatatypesinVBE,descriptionof,377bottomortoprows,listingwithORDERBYclause,218–219boxing,relationshiptoVBE,375branchofficesolutions,applyingSSEto,7browser,viewingpagesin,423BULKINSERTstatement,using,290–292bulk-loggedrecovery,advantageof,100businessanalysts,useofSSEby,6–7buttonclickeventprocedures,addingtoWindowsforms,387–389Buttoncontrols,draggingtoASP.
NETpages,417Button*_ClickproceduresapplyingTry.
.
.
Catchstatementin,393usingTry.
.
.
Catchstatementsinfunctionprocedureswith,394–395usingwithapplicationvariables,440usingwithsinglevaluesandCommandobjects,456–457usingwithSqlBulkCopyinstance,512–514inVBE,381–385inWindowsform,388Button5_Clickcode,483bytestreamsdecoding,114definitionof,114typesof,115Ccalculatedvaluesconceptualizing,160includinginlistitems,158Calendarcontrolrunning,423usingwithASP.
NETprojects,421–422CASCADEkeyword,usingwithUPDATEstatement,149INDEX576Catchclause,usingwithfilevaluesandresultsets,407certificatescreating,364andencryptionkeys,359–367changesconcurrentlytransferringmultipletypesof,504–505processingtotwotables,505–509CharandStringdatatypesinVBE,descriptionof,377–379charactercodes,relationshiptobytestreams,115characterdata,insertingintovarcharcolumns,124–126characterdatatypes,overviewof,115–116characterinputpatterns,specifyingwithCHECKconstraints,141–142charactermatches,filteringfor,175–177CHECKconstraintsblockingemptyandmissingcharacterinputwith,140–141descriptionof,107,139overviewof,139specifyingpatternsforcharacterinputwith,141–142usingmulticolumnCHECKconstraints,144–145Classestableaddingrowto,286contrastingupdateanddeleteto,150creatingviewsfor,252–253ClassGradestableaddingrowsto,286columnsandconstraintsin,151CREATETABLEstatementfor,147creatingviewsfor,252–253usingmulticolumnprimarykeyswith,143–144ClickeventproceduresforADO.
NETconnectionstoSSEandAccessdatabasefiles,452–453firinginVBE,381usinginWindowsforms,388usingwithDataSetobjects,486–487usingwithsinglevaluesandCommandobjects,456usingwithSQLServerdatabaseconnections,448–449Closemethod,effectof,4CLUSTEREDkeyword,usingincolumndeclarations,136clusteredversusnonclusteredmulticolumnprimarykeys,143cmd.
exewindow,copyingcontentfrom,67,68codepages,relationshiptobytestreams,115CollationSettingsscreen,displaying,15columnmetadata,reportingforviews,258–259columnnames,returningindatabasetables,81–82columnvalues.
Seealsodefaultcolumnvaluesaddingtotables,46assigningautomatically,46combiningexactandapproximatematchesfor,177modifyingforsystemcatalogviews,80–81retrievingforcurrentrowofDataReaderobject,462signinginrows,363sortingrowsby,216–218updatingin,478–483updatinginrowsthroughDataTables,503–504updatingthroughstoredprocedures,287–288columnscreatingtableswith,121–124designatingasPRIMARYkey,135–138insertingdataforsubsetof,128–131prohibitingnullvaluesin,131–132selectingRelationshipscontextmenuitemfor,47specifyingfixed-widthdatatypesfor,122–123specifyingvariable-widthdatatypesfor,123specifyingwithUnicodedatatypes,124combinedresultsets,reusing,231–232.
SeealsoresultsetsComboBoxcontrol,populatingwithpathnames,401–402ComboBoxselections,readingtextfilesbasedon,403–404ComboBox1,populating,400CommandclassinADO.
NET,descriptionof,447CommandobjectsprogrammingwithADO.
NET,454–455returningsinglevalueswith,455–460usingfordatamodification,499command-lineprompts,invokingsetup.
exefrom,12commentmarker('),usingwithTryclause,396complexpasswords,using,314.
Seealsopasswordscomputedcolumn,conceptualizing,129computedlistitems,calculating,159–162computedvalues,returningasoutputparameters,280–281connectingtoserverinstanceswithWindowsauthentication,38–41toSQLServerinstanceswithSQLServerauthentication,35–37toSQLServerinstanceswithsqlcmdutility,60–62toSSEfromAccesslinkedtables,28–29toSSEfromAccessprojects,27–28toSSEfromVisualStudio2003,25–27fromSSMS-basedquerytool,22–23viaSQLServerauthentication,62INDEX577ConnectionclassinADO.
NET,descriptionof,446Connectionobjectsexploring,37programmingwithADO.
NET,447–453usingwithconditionallyenabledcontrols,472connectionstringsgettinghelpforconstructinginADO.
NET,450–453usingAttachDBFileNameargumentwith,5connections,creatingwithdialogboxes,516–517constraintsCHECKconstraint,139–142inClassGradestable,151FOREIGNKEYconstraints,145–154multicolumnCHECKconstraints,144–145multicolumnconstraints,143–145typesof,107usingwithviews,248controlbehavior,modifyingwithsmarttags,532–538CONTROLpermission,effectof,326controlproperties,usingwithapplicationforms,398–399controls,populatingWindowsformcalculatorwith,386–387controlsforWebFormsconditionalenablingof,471–473examplesof,432CONVERTfunction,formattingdatetimevalueswith,163–164copyingwithCREATEDATABASEstatement,84andsearchingdatabaseswithWindowsExplorer,83selectedrowsfromtables,228tablesandsavingresultsetswithINTOclause,225–228valueswithtriggers,301–302copy-onlybackup,useof,100correlatedsubqueries.
SeealsosubqueriesincludinginWHEREclausesusingCTEsinsteadof,235–236CountryRegionNamecolumnvalues,obtainingforrows,182CREATEDATABASEstatementattachingdatabaseswith,89–95creatingdatabaseswith,42creatingtablecolumnswith,121–124using,84–85usingFORATTACHclausewith,90,98usingLOGONclausewith,87–88usingONclausewith,86–87CREATEFUNCTIONstatementscomputingoddandevennumberswith,276usingwithFNuser-definedfunctions,261–263usingwithIFuser-definedfunctions,263–264CREATELOGINstatementsusingwithSQLServerlogins,314–315usingwithWindowslogins,318CREATEPROCstatementcreatingusp_DeleteGradeClassesstoredprocedurewith,289specifyingusp_AddClassGradesstoredprocedurewith,286usingwithusp_UpdateLastNamestoredprocedure,287–288forusp_BULKINSERTstoredprocedure,291CREATEPROCEDUREstatementscomputingoddandevennumberswith,276invoking,269,274–275useofoutputparameterswith,278usingwithreturnstatusvalues,282CREATESCHEMAstatement,using,350–351CREATETABLEstatementsversusALTERTABLE,128recoveringvaluesfromoldtablesfornewtableswith,126–128specificationsavailableto,121usingCHECKconstraintswith,139–140,142usingwithClassGradestable,147usingwithDotNetTable2databasetable,506usingwithmulticolumnCHECKconstraints,144–145usingwithmulticolumnprimarykeys,143usingwithobjectsandownedschemas,354,355usingwithtriggers,298–299CREATETRIGGERstatementexampleof,293–294specifyingrollbackofDMLstatementswith,295–296CREATETYPEstatements,usingwithaliasdatatype,120CREATEUSERstatementsusing,322usingwithschemas,350,352CREATEVIEWstatementssyntaxof,249using,250–251crossjoinsdefinitionof,192mergingrowsindatasourceswith,198–200crosstabtables,saving,242–243crosstabspivotingrelationaldatato,240–241unpivoting,243–244cross-tabulatedandrelationaltables,convertingbetween,239–244.
Seealsotables.
csvfile,exportingfromExcel,511CTEcolumnvalues,lookingupvaluesbasedon,234–235INDEX578CTEs(commontableexpressions)mappinghierarchicalrelationshipswith,236–239reusingquerieswith,232–233usinginsteadofcorrelatedsubqueries,235–236currencydatatypes,overviewof,110currentdatabasecontext,runningqueriesfrom,166–167currentuser,date,andtime,assigning,133–135cursordatatype,using,118customdatabaseroles,assigningpermissionsto,345–348Customertableaddingdatato,48addingrowsto,48usingIdentitypropertysettingwith,46CustomerContacttable,Insertscriptfor,57–58customizeddataaccessmode,availabilityof,8DDAC(DedicatedAdministratorConnection),relationshiptoserverinstances,61dataaccessingwithviews,248–252addingtotables,124–128encrypting,360–363extractingfromviews,249insertingforsubsetoftablecolumns,128–131readinginWebapplications,431–435signing,363–367dataaccessmode,availabilityof,8databackup,definitionof,99dataconnectionscreatingforSSEfromVisualStudio2003,25–26testing,26usingforSSEandVisualStudio2003,26datadefinitionanddatabasemaintenance,performingwithADO.
NET,471–483datadisplays,creatingandusing,539datafiles,designatinginONclauseofCREATEDATABASEstatement,86–87.
SeealsoprimarydatafilesDataLinkPropertiesdialogbox,openingforSSEandVisualStudio2003connections,25datamanipulationversusBULKINSERTstatement,290performingthroughviews,302–305performingwithstoredprocedures,284–292datamodificationenablingwithDataAdapterobjects,499settingupfor,500–501usingCommandobjectsfor,499datasourcecontrols,reconfiguring,566datasourceitems,draggingtoWindowsForms,529–531DataSourceparameter,relationshiptoAttachDBFileNameargument,5datasources.
Seealsodisconnecteddatasources;inputdatasourcescomputingnumberofrowsin,180tracking,166typesofjoinsbetween,192usingcrossjoinswith,198–200usinginnerjoinswith,192–195usingouterjoinswith,195–198usingself-joinswith,200–204DataSourceswindowavailabilityinVBE,526–527creatingdatasourcefor,527–529datatypes.
SeealsoVBEdatatypesaliasdatatype,120approximatedatatypes,112–114assigningintegerdatatypestoprimarykeycolumns,136bitdatatype,109categoriesof,107–108characterdatatypes,115–116currencydatatypes,110cursordatatype,118dateandtimedatatypes,111decimalandnumericdatatypes,111–112integerdatatypes,109–110numbersanddates,108–114specifyingcolumnswithUnicodedatatypes,124sql_variantdatatype,118tabledatatype,120timestampdatatype,117Unicodecharacterdatatypes,116–117uniqueidentifierdatatype,117–118variable-widthdatatypes,123inVBE,374–379xmldatatype,118–120DataAdapterclassinADO.
NET,descriptionof,447DataAdapterobjectsavoidingspecifyingparametersforpropertiesof,499benefitof,498conceptualizing,485enablingdatamodificationwith,499featuresof,486databaseaccess,grantingtologins,333–336databaseconnections,creatingwithVWDEvisualdatabasetools,554–557databasecontext,runningqueriesfrom,166–167DatabaseDesigner.
SeeNewDatabaseDesignerDatabaseExplorervisualtoolsaccessing,515foraddingobjectstoSSEdatabases,519–522addingstoredprocedureswith,525–526descriptionof,516dialogboxesforcreatingconnections,516–517INDEX579DatabaseExplorervisualtools(continued)forexploringSSEdatabases,517–519formigratingandrevisingMSDEdatabase,522–524openingQueryDesignerfrom,524DatabaseExplorerwindow,makingdatabaseconnectionsfrom,554–557databasefileslocationof,83storing,83databasehelp,usingsp_helpdbsystem-storedprocedurefor,76–78databasemaintenanceanddatadefinition,performingwithADO.
NET,471–483databaserecoverymodels.
Seerecoverymodelsdatabaseroles,assigningpermissionsto,345–348databasesamples,installing,24–25databasetables.
Seetablesdatabases.
SeealsoSSEdatabasesaddinganddropping,42–44attachinganddetaching,88–89attachingwithCREATEDATABASEstatement,89–95backingup,93backingupandrestoring,98–106conceptualizing,74–75contentsof,74detachingfromservers,96,97enablingloginsforcreationof,332gettingmetadataabout,75–78namingwithCREATEDATABASEstatement,84–85overviewof,73searchingandcopyingwithWindowsExplorer,83typesof,75viewingandmodifyingwithVWDEvisualdatabasetools,558–560DataGridViewcontrolschangingsortorderin,496–497creatingparameterqueriesfor,540modifyingcolumnsappearingin,532–533populating,551restrictingupdatingwith,533–535DataReaderclassinADO.
NETdescriptionof,447using,462–464DataSetinstanceinADO.
NET,descriptionof,447DataSetobjectsconceptualizing,485creating,populating,andusing,486–489examiningcontentsof,489DatasheetviewforShipperstable,displayinginAccess2SQLEXPRESSproject,28–29DataTableobjectsaddingrowstodatabasetablesthrough,501–502descriptionof,447returningsortedvaluesto,489–491sortingandfilteringwithDataViews,493–498updatingcolumnvaluesinrowsthrough,503–504DataTablevalues,assigningtoListBoxcontrols,491–493DataViewsshowingvaluesin,495–496sortingandfilteringDataTableswith,493–498dateandtimedatatypes,overviewof,111DatedatatypesinVBE,descriptionof,377DATEADDfunction,usingwithSELECTstatements,164–165DATEDIFFfunction,usingwithSELECTstatements,164–165dates,assigningcurrentdates,133–135datetimedatatype,descriptionof,109,111datetimevaluesdisplayingwithSELECTstatements,162–165formattingwithCONVERTfunction,163–164DBAs(databaseadministrators),useofSSEby,7–8DBConcurrencyException,handling,547–553.
Seealsohandlingexceptions@dbnameparameter,assigningdatabasenamesto,77dboschema,creatingandusingviewsin,249–250.
Seealsoschemasdbo.
ufnIsOdduser-definedfunction,invocationsof,276–277DDLtriggers,descriptionof,292decimaldatatypesversusfloatdatatypes,113–114overviewof,111–112DECLAREstatements,usingwithreturnstatusvalues,283defaultcolumnvalues,designating,132–135.
SeealsocolumnvaluesDEFAULTkeyword,usingwithcolumns,132,133defaultparametervalues,specifyingwithstoredprocedures,273–274.
SeealsoparametersDefault.
aspxWebpageButton1_Clickprocedurefor,461displayinginDesigntabofVWDE,460DELETEkeyword,usingwithFOREIGNKEYconstraints,146DeleteObjectDesigner,opening,44DELETEtriggers,using,299–302deletedtemporarytablecopyingvaluesfrom,301–302relationshiptoDMLtriggers,293–295deletingrowsfromtableswithrelatedrowsinanothertable,288–290DENSE_RANKfunctiondescriptionof,220using,222–223INDEX580DENYstatementsdescriptionof,339fine-tuningrolepermissionswith,342–345departmentsolutions,applyingSSEto,7derivedtable,definitionof,241.
SeealsotablesDESC,usingwithORDERBYclause,215Designview,displayingforforms,397–399designers,guidelinesforuseof,41Detailscontrol,specifyingfordatasourceitemsinWindowsForms,530developers,useofSSEby,6–8dialogboxes,creatingconnectionswith,516–517differentialbackupscreating,103–104definitionof,99restoring,104–105selectingtorestorefromdisaster,105–106DimstatementsinApplication_Startprocedure,438usingwithfilevaluesandresultsets,406usingwithMessageForTheDaycodemodule,425usingwithVBEdatatypes,379–380DISABLETRIGGERstatement,exampleof,296DisableEnableUpdateDeleteControlsprocedure,usingwithforms,472–473disconnecteddatasources,workingwith,505.
SeealsodatasourcesDISTINCTkeywordusingwithSELECTstatements,158,183–184usingwithself-joins,201distinctvalues,showingwithGROUPBYclause,183–184distributedqueriesversusqueriesonsingleserver,169typesof,168DML(DataManipulationLanguage),significanceof,284DMLstatements,rollingback,295–296DMLtriggersdescriptionof,292temporarytablesassociatedwith,293Doloopsusingwithapplicationvariables,438usingwithComboBoxcontrol,402Do.
.
.
Loopstatement,usingwithfilevaluesandresultsets,406DotNetTabledatabasetablecolumnsin,500populating,507DotNetTable2databasetable,usingCREATETABLEstatementwith,506Doubledatatypedescriptionof,376–377usingwithWindowsforms,387down-levelclientsconnectingfrom,24–30supportfor,5DROPDATABASEstatementexampleof,85,87,88,94removingbackupdeviceswith,106DROPPROCEDUREstatement,usingwithstoredprocedures,270DROPUSERstatement,invoking,322DSN,referencingforODBClinkedtables,29E-Eswitch,usingwithsqlcmdutility,60–61ELSEclauseofIF.
.
.
ELSEstatement,usingwithtriggers,300Elseclause,usingwithWebForms,433ELSE.
.
.
IFclause,usingwithobjectsandschemas,357,358ElseIfcondition,usingwithContactEAddrcolumnvalues,476–477emailaddresses,verifyingwithregularexpressions,477ENABLETRIGGERstatement,exampleof,296–297Enabledproperty,usingwithcontrols,472encryptingdata,360–363encryptionkeysandcertificates,using,359–367ErrorandUsageReportSettingsscreen,displaying,16errorlevels,reporting,133eventsinApplicationclass,explanationsof,436Excel,bulkloadingdatafrom,509–514Excelworksheets,creatingtablefor,511–512exceptionhandling.
Seehandlingexceptions.
exefile,simulatinguserexperiencewith,390EXECstatementsrecoveringreturnstatusvaluesin,282useofoutputparameterswith,278usingwithoutputparameters,279–280,281usingwithsp_srvrolepermission,329usingwithstoredprocedures,271,272,273,275EXECUTEASstatementsusingwithloginprincipals,347usingwithobjectsandownedschemas,355usingwithpermissions,343,344–345usingwithSQLServerlogins,316EXECUTEpermission,grantingforstoredprocedures,289EXECUTEstatement,usingwithsystem-storedprocedures,76ExecuteReadermethod,usinginADO.
NET,462ExpressSSMS.
SeeSSMS-basedquerytoolexpressions,calculatingforstringvalues,161FFeatureSelectionscreen,displaying,13FiguresAccessprojectsusedtobrowseandedittablevalues,29adhocqueries,170INDEX581Figures(continued)AddConnectiondialogboxinDatabaseExplorer,556AddNewItemdialogboxinSolutionExplorer,416AddWHEREClausedialogbox,571ADO.
NETmanaginginputtomultipledatabasetables,509ADO.
NETsyntax,460ADO.
NETusedtoassignvaluestoformcontrolproperties,479AdvancedScheduleOptionsscreen,71AnotherPage.
htm,429AuthenticationModescreen,15avoiding-PswitchwithSQLServerauthentication,62browsingandeditingdatainDataGridViewcontrol,530buttonsforpermittingdatabaseupdates,550Calendarcontrol,422ChangeDataSourcedialogboxinVWDE,555CodeviewforWindowsform,389commandpromptwithsqlcmdsession,61commandsforcreatingitemsinObjectExplorerfolders,42ConnecttoServerdialogboxforSSMS,22ConnecttoServerdialogboxinSSMS,36,37,40connectingtoserverinstanceswithWindowsauthentication,39contextScriptmenucommand,57copyingfiles,97copyingsqlcmdoutputfromcmd.
exewindowtoExcel,69CREATEDATABASEstatement,85CREATEDATABASEstatementusedwithLOGONclause,88CREATEDATABASEstatementusedwithONclause,87customviewscreatedfromODBClinkedtables,30customizingreturnvaluefromCalendarcontrol,423dataconnectiontolinkVisualStudio2003toSSEdatabase,26DataLinkPropertiesdialogboxpointingtoAccessproject,28datamodificationcontrolsremoved,535datasourceandDataSet,549DataSourceConfigurationwizard,528datasourceinDataSourceswindow,529DataAdapterandDataTableobjects,504DatabaseExplorerforVWDEproject,557DatabaseExplorersettingcolumnproperties,521DatabaseExplorerspecifyingIDENTITYproperty,520DatabaseExplorerspecifyingviewswithoutT-SQLcode,522DatabaseExplorerusedwithdatabaseconnectionobjects,518databasesummaryonSSEinstance,92DataReaderobject,464DataTablevaluesreordered,490DataViewobjectscontrollingorderofrowsinDataGridViewcontrol,497DefineParameterswizardscreen,568detachingdatabases,97draggingcolumnnamesinforms,531editingsqlcmdstatementsinbatchfile,68enablingprotocolswithSQLServerConfigurationManager,20errormessageforDBConcurrencyExceptioninstance,553ExceptionAssistantdialogboxforWindowsform,390exporting.
csvfilefromExcel,511FeatureSelectionscreen,14feedbackforusersafterperformedactions,475field-basedechogeneratedbyVBE,408FillCombobuttonpopulatingComboBox1,400Form11sessionsforDBConcurrencyExceptioninstances,552formattingcontrols,398global.
asaxaddedtoASP.
NETproject,437GridViewcontrolpagingcontrolnumbers,563GridViewcontrolwithsortingenabled,564hierarchicalrelationshipsformetadata,83-iswitchinsqlcmdstatement,64ListBoxcontrolpopulation,470,492ListBoxcontrolupdatingSelectedValueproperty,537LookupSSEData.
aspxpagereturningorderhistorydata,572LookupSSEData.
aspxpagewithformattingsettings,573message-for-the-dayASP.
NETapplication,427ModifyConnectionmenuinDatabaseExplorer,523navigatingdatabasefilesinWindowsExplorer,84navigatingdatabaseobjectsonSSEinstances,40NewDataDesignerGeneralpage,43NewWebSitedialogboxinASP.
NET,412-oswitchinsqlcmdstatement,65ObjectExplorerandDocumentwindowinExpressSSMS,35ODBClinkedtablesinAccess.
mdbfiles,30OnePage.
htm,428OpenTablemenuitem,48OptionsdialogboxinVBE,381parameterqueryforDataGridViewcontrol,540,541INDEX582parameterspecifiedthroughListBoxselection,543permissionforSalesByPersonTerritoryRegion.
rptfile,437QueryBuilderdialogbox,567QueryDesigneropenedfromDatabaseExplorer,524QueryDesignerusedtojointableandview,558querytabareas,53queryviewsinQueryDesigner,559recoveringdatabasecontent,105recovery_modelandrecovery_model_desccolumns,102referencingdatafileandlogfile,95Relationshipscontextmenuitemselectedfortablecolumns,47Resultstotextformat,54runningsqlcmdstatementsinbatchfiles,68SaveProjectdialogboxinVBE,372ScheduledTaskWizard,70selectingitemsinListBoxcontrol,545settingSQLEXPRESSnamedinstance,18SolutionExplorer,373sp_helpdbsystem-storedprocedure,77,79SpecifyValuesforTemplateParametersdialogbox,58SQLServerdatabaseconnections,448SQLServerSurfaceAreaConfigurationtool,91sqlcmdstatementoutputfileincmd.
exewindow,69SqlCommandobjectinstantiation,455SSMSgraphicaldatabaseobjects,38storedproceduredesignerinDatabaseExplorer,526sys.
databasescatalogview,80SystemConfigurationCheckscreen,13TableDesignerbottompane,46TableDesignerinExpressSSMS,45TablesandColumnsdialogbox,48TemplateExplorer,59testingASP.
NETpages,419TextBoxcontrolsusedtocollectvalues,476ToolboxforaddingcontrolstoWindowsforms,387Try.
.
.
Catchstatementsform,393typingT-SQLstatementinquerytab,23Updatemethodinvocation,505Usedatabounditemscheckbox,537ViewDesigner,50,56ViewDesignersortingandfilteringdata,51VWDEIDE,413WebFormcontrols,432WebFormpopulatedwithGridViewcontrol,561Web-Formdatacollection,434websiteonlocalIIS,430filenames,populatingListBoxcontrolwith,402–403filepermissions,updatingforapplicationvariable,436filesystemDesignandFormattedviewsofapplicationformsin,397–399foldersin,399–404readingfixed-widthreportswith,404–408filevalues,readingwithresultsets,405–408FILEGROWTHkeywordusingwithLOGONclauseofCREATEDATABASEstatement,88usingwithONclauseofCREATEDATABASEstatement,86–87FILENAMEkeywordusingwithFORATTACHclauseofCREATEDATABASEstatement,90usingwithONclauseofCREATEDATABASEstatement,86filesattachingwithCREATEDATABASEstatement,98copying,96–97generatingwithresultsets,404–405runningstatementsfrom,62–64savingoutputto,64–65viewinginfilesystem,399–404filesofstatementsreferencingwith-iswitchinsqlcmdutility,63–64referencingwith:rkeywordinsqlcmdutility,64file-systemwebsite,deployingtoserver-basedwebsite,430–431Fillmethod,invokingforSqlDataAdapterobjects,487filteringforexactandapproximatecharactermatches,175–177forvaluesinranges,177–183Finallyclausehandlingexceptionswith,391usingwithADO.
NETconnectionstrings,451usingwithfilevaluesandresultsets,407firewalls.
SeeWindowsXPfirewallfixeddatabaserolesversuscustomdatabaseroles,345–346definitionof,337fixeddatabaseroles,assigningpermissionsbymeansof,332–338fixedserverroles,assigningpermissionsbymeansof,327–332fixed-widthdatatypes,specifyingfortablecolumns,122–123.
Seealsodatatypesfixed-widthreports,readingwithfilesystem,404–408floatdatatypeversusdecimaldatatype,113–114descriptionof,112specificationsfor,113INDEX583FNs(scalaruser-definedfunctions)creatingandusing,260–263descriptionof,259foldersexploringinfilesystem,399–404openinginSSMS-basedquerytool,37FORATTACHclausedesignatingprimaryfileswith,90–95usingwithCREATEDATABASEstatement,90,98FORLOGINclause,usingwithCREATEUSERstatement,322For.
.
.
Nextloop,usingwithapplicationvariables,441FOREIGNKEYconstraintsdescriptionof,107,139featuresof,145–146foreignkeyvalues,usingSELECTstatementsandinnerjoinswith,193foreignkeyscreating,48implementingmany-to-manyrelationshipswith,151–154supportingreferentialintegritywith,146–150formattingforms,398–399formsimprovingusefulnessof,398openingwithconditionallyenabledcontrols,471–473forrunningTry.
.
.
Catchstatements,392–393forward-only,read-onlyrowsets,returninginADO.
NET,462–470four-tablejoins,specifying,206–208Friendaccessmodifier,using,502FROMclausesspecifyingforsingledatasource,165usingwithcrossjoins,198usingwithfour-tablejoins,207usingwithinnerjoins,193–195usingwithSELECTstatements,156usingwithviews,258fulldatabackupandrestore,performing,102–103fullrecovery,advantageof,100functionprocedures,invokingTry.
.
.
Catchstatementsin,394–395functions.
Seeuser-definedfunctionsGGetFilesmethod,usingwithListBoxcontrol,402GetOrdinalmethod,usinginADO.
NET,462GetXXXmethodsusingwithcolumnvaluesforsingleSELECTstatement,465usingwithDataReaderobjectinADO.
NET,462global.
asax,addingforapplicationvariables,436GOkeywordexplanationof,125terminatingbatchesofT-SQLwith,173–174usingwithstoredprocedures,269GRANTstatementsargumentsof,340–341assigningpermissionstoroleswith,346assigningSELECTpermissionwith,339descriptionof,339fine-tuningrolepermissionswith,342–345GridViewcontrolsdisplayingdataonWebFormpageswith,560–564enablingsortingfor,564usingsmarttagwindowfor,563GROUPBYclausedefiningnestingin,184–185performingaggregationwith,185usingwithCTEs,234usingwithfour-tablejoins,207usingwithSELECTstatements,175,183usingwithsubqueries,213groupingforaggregation,186–189andnesting,184–186showingdistinctvalueswith,183–184guestuseradvisoryaboutenablingdatabaseaccesswith,335behaviorof,312enabling,333GUIcomponents,usingwithSQLServerExpress,4Hhandlingexceptions.
SeealsoDBConcurrencyExceptiondynamicallyadaptingtoruntimeerrors,395–397withaformforrunningTry.
.
.
Catchstatements,392–393byinvokingTry.
.
.
Catchstatementsinfunctionprocedures,394–395overviewof,390–392withTry.
.
.
Catchstatementsdirectly,393–394hardwareandsoftwarerequirementsforSSE,12HAVINGclause,relationshiptoSELECTstatements,185–186HEAPtypedescription,usingwithresultsets,227HelpiconinNewDatabaseDesigner,descriptionof,44HerCertandHisCertcertificates,creating,364hierarchicalrelationships,mappingwithCTEs,236–239HisCertandHerCertcertificates,creating,364hobbyists,useofSSEby,6–7.
htmfileextension,explanationof,427HTMLpages,creatingandlinkingwithASP.
NET,427–429INDEX584I-iswitch,usingwithsqlcmdutility,63,67iconsinNewDatabaseDesigner,descriptionsof,43–44identifierrulesforSQLServer,overviewof,82IDENTITYpropertyspecifyingwithDatabaseExplorer,520using,137–138usingwithCustomertable,46usingwithprimarykeys,136usingwithviews,258–259IDENTITY_INSERT,turningon,138Ifblock,usingwithapplicationvariables,439IFclause,usingwithobjectsandschemas,357Ifcondition,usingwithContactNamecolumnvalues,476IFstatementsusingwithEXISTSoperatorsforcreatingusers,322usingwithreturnstatusvalues,282–283IF.
.
.
ELSEIF.
.
.
ELSEIFstatement,usingwithobjectsandschemas,358IF.
.
.
ELSEstatement,usingwithtriggers,300If.
.
.
Else.
.
.
EndIfstatement,usingwithsortedvaluesinDataTables,491If.
.
.
ElseIf.
.
.
Else.
.
.
EndIfstatement,usingwithContactNameandContactEAddrcolumnvalues,477If.
.
.
Then.
.
.
Elsestatement,usingwithWebForms,433IFs(inlineuser-definedfunctions)creatingandusing,263–267descriptionof,259versusviews,259–260IIS(InternetInformationServer)versusASP.
NETDevelopmentWebServer,429Webresourcefor,430imagedatatype,using,117Immediatewindow,viewing,396IMPERSONATEpermission,using,326INoperator,filteringrowswith,181–183InColRangefunction,usingwithsinglevaluesandCommandobjects,459indexvalueofrows,computinginDataTable,488indexedview,descriptionof,248information,lookingupwithparameterqueries,539–541INFORMATION_SCHEMA.
VIEWScolumns,returningwithsys.
views,257–258inlineuser-definedfunctions.
SeeIFs(inlineuser-definedfunctions)innerjoinsdefinitionof,192andSELECTstatementsreferencingforeignkeyvalues,193specifying,196syntaxesfor,192–193usingFROMandWHEREclauseswith,193–194usingFROMclauseswith,194–195inputdatasources,trackingincombinedresultsets,230–231.
Seealsodatasourcesinputparameterscontrastingforuser-definedfunctionsandstoredprocedures,276–278returningwithstoredprocedures,271–278Insertscript,exampleof,57–58INSERTstatementspermittingrunningbyusers,338usingCHECKconstraintswith,140,142usingINSTEADOFtriggerswith,303usingtotriggersfordatamanipulationthroughviews,304–305usingwithmulticolumnprimarykeys,144usingwithsigneddata,365usingwithwidecharacterdatainvarcharcolumns,125INSERTtriggers,using,299.
Seealsotriggersinsertedtemporarytablecopyingvaluesfrom,301–302referencing,293–295relationshiptoDMLtriggers,293InsertWithSQLstatement,examplesof,507,508,509installingsampledatabases,24–25SSE,12–16InstanceNamescreen,displaying,13INSTEADOFtriggersdescriptionof,292using,300usingwithviews,302–305intintegerdatatype,specificationsfor,110int1column,specifyingIDENTITYpropertyfor,138integerdatatypesassigningtoprimarykeycolumns,136descriptionsof,376overviewof,109–110interactivedatadisplays,creatingandusing,539Internet,deployingSSEon,8INTOclausesavingresultswith,225–228usingwithcrosstabs,242usingwithresultsets,231–232is_auto_close_oncolumnmodifyingvaluesin,80–81usingwithsys.
databasesview,79–80IsDigitfunction,usingwithCharcharacters,378IsPostBackproperty,managingWebFormswith,433–434itemscollectionofListBox,populatingwithSqlDataReader,469–470INDEX585JJOINclause,usingwithCTEs,237–238joinsdefinitionof,191formorethantwodatasources,205–208typesof,192L.
ldfextension,descriptionof,83lengthofnumberdatatypes,significanceof,108LIKEoperator,usingwithapproximatecharactermatches,175linkedqueriesversusOPENROWSETfunction,169linkedservers,runningqueriesfrom,172–174listitems,includingcalculatedcolumnvaluesin,158ListBoxcontrolsassigningDataTablevaluesto,491–493availabilityinSSEsolutions,570configuringandusing,536–538performinglookupswith,541–544populatingitemscollectionof,469–470populatingwithfilenames,402–403ListBoxitems,filteringdynamically,497–498ListBoxselections,readingtextfilesbasedon,403–404ListColumnValues.
batfiledisplayinginNotepad,67–68scheduling,69–70ListColumnValues.
sqlexample,66–67localvariables,usingwithWHEREclause,176locksondatabasefiles,releasing,4logbackup,definitionof,99logfilesattachingwithCREATEDATABASEstatement,89–95contentsof,87–88LOGONclause,usingwithCREATEDATABASEstatement,87–88LOGINsecurable,permissionsfor,326loginsaddinganddropping,314addingtofixedserverroles,328,330authenticatingwith,308–309definitionof,314enablingtocreatedatabases,332enablingtocreateotherlogins,328–331grantingdatabaseaccessto,333–336grantingpermissionto,332naming,314SQLServerlogins,313–317syntaxforserverlogins,320typesof,313usingwithschemas,350Windowslogins,318–321LookupAccessData.
aspxpage,opening,568lookupsperformingonWebFormpages,564–573performingwithstoredprocedures,545–547performingwithtwoListBoxcontrols,541–544LookupSSEData.
aspxpagewithformattingsettings,573returningorderhistorydatawith,571–572Mmany-sideofone-to-manyrelationship,insertingrowfor,254–255many-to-manyrelationships,implementingwithforeignkeys,151–154MARS(multipleactiveresultsets),significanceof,10masterdatabase,descriptionof,75masterpages,usingwithASP.
NETprojects,416MAXRECURSIONhint,usingwithCTEs,238MAXSIZEkeywordusingwithLOGONclauseofCREATEDATABASEstatement,88usingwithONclauseofCREATEDATABASEstatement,86–87.
mdfextension,descriptionof,83Mekeywordexplanationof,388usingwithASP.
NETpages,418MessageBoxclass,usinginVBE,382message-for-the-dayASP.
NETapplication,424–427MessageForTheDaycodemodule,programming,425–426metadataobtainingfordatabases,75–78processingwithsystemviews,256–259returninghierarchicalmetadatawithsystemcatalogviews,81–82MicrosoftDownloadCenterwebsite,12modeldatabase,descriptionof,75moneydatatype,specificationsfor,110msdbdatabase,descriptionof,75MSDEdatabasesmigratingandrevising,522–524usingVWDEtoconnectto,555MSDEversusSSE,11–12multicolumnCHECKconstraints,using,144–145multicolumnprimarykeys,using,143–144.
Seealsoprimarykeysmulti-statementtable-valueduser-definedfunctions.
SeeTFs(multi-statementtable-valueduser-definedfunctions)Mynamespace,categoriesof,401MyCopytable,selectingrowsin,228mysqlsystemresourcedatabase,descriptionof,75INDEX586Nnamecolumn,usingwithsys.
databasesview,79–80NAMEkeyword,usingwithONclauseofCREATEDATABASEstatement,86namedschemas,creatingviewsin,250–252.
Seealsoschemasnchar()syntax,usingwithUnicodedatatypes,116.
ndfextension,descriptionof,83nestingandgrouping,184–186.
NETProvidersforADO.
NET,descriptionsof,445–446networkaccessconfiguringSSEfor,19–20disablingforSSEandVisualStudio2003connections,26–27NewDatabaseDesignerfeaturesof,42invoking,42NewQuerytool,locationof,23nonprofessionaldevelopers,useofSSEby,6–7normalization,significanceof,167Northwindsampledatabaseconnectingto,26downloading,24referencingwithUSEstatement,82usingwithAccessprojects,27–28NOTNULLphrase,effectof,131Notepad,displayingListColumnValues.
batfilein,67–68ntextdatatype,using,116NTILEnumbers,assigningtorows,223–224NTILErankingfunction,descriptionof,220nullvaluesassigningzeroesinsteadof,132–133prohibitingincolumns,131–132usingstoredprocedurestofilterfor,274–275null-valuesearches,performingwithouterjoins,195–197numberdatatypesbitdatatypes,109characteristicsof,108currencydatatypes,110integerdatatypes,109–110overviewof,376–377numericdatatypes,overviewof,111–112nvarchar(max)datatype,significanceof,10nvchar()syntax,usingwithUnicodedatatypes,116O-oswitch,usingwithsqlcmdutility,64–65ObjectExplorerdisplayingScriptmenuitemsin,51obtainingmetadatafromSSEinstanceswith,76Scriptcontextmenuitemsin,56OBJECT_IDfunction,usingwithviews,250objectsaddingtoSSEdatabases,519–522creatinginownedschemas,353–356creatinginschemas,356–359exploringinSSMS-basedquerytoolconnections,37VBEdatatypesas,374–379ODBClinkedtables,connectingtoSSEfrom,28–29ONclausesdesignatingdatafilesin,86–87usingwithself-joins,203–204OnErrorGoTostatements,effectof,391ONUPDATEkeyword,usingwithFOREIGNKEYconstraints,146OnePage.
htmfile,creatinginSolutionExplorer,428OPENROWSETfunctionspecifyingargumentsin,171usingwithadhocqueries,168–169operationsspecialists,useofSSEby,7–8OptionExplicitOnVBEsetting,using,380–381ORDERBYclausecontrollingroworderwith,215relationshiptoSELECTstatements,185–186relationshiptoviews,250usingwithcrosstabs,242–243usingwithfour-tablejoins,207usingwithresultsets,227,231usingwithsubqueries,213orderhistorylookingupinAccessdatabases,564–569lookingupinSSEdatabase,569–573OrderDatecolumnvalue,processinginlistforSELECTstatement,162osql,relationshiptosqlcmdutility,8outerjoinscountingrowswith,197–198definitionof,192searchingforrowswith,195–197output,savingtofiles,64–65outputparametersreturningcomputedvaluesas,280–281returningscalarvalueswith,278–281returningvaluesfromviewsas,279–280OVERclause,usingPARTITIONkeywordwith,220ownedschemas,creatingobjectsin,353–356.
SeealsoschemasP-Pswitch,usingwithsqlcmdutility,62@Pagedirective,usingwithASP.
NETprojects,414,420Page_Loadeventprocedure,usingwithapplicationvariables,440pagesforASP.
NETprojects,viewinginbrowser,423INDEX587parameterqueries,lookingupinformationwith,539–541parameters.
SeealsodefaultparametervaluesdesignatinginADO.
NET,486usinginWHEREclauses,272–273usingwithstoredprocedures,284PARTITIONkeyword,usingwithrownumbervalues,220–221partitionalview,descriptionof,248passwords,usingwithsigneddata,367.
Seealsocomplexpasswordspathnames,populatingComboBoxcontrolwith,401–402PATINDEXT-SQLfunction,relationshiptoViewDesigner,51PERCENTkeyword,usingwithSELECTstatements,159period(.
),followingMekeywordwith,388permissionsassigningtocustomdatabaseroles,345–348assigningtoprincipals,324–327assigningviafixeddatabaseroles,332–338assigningviewfixedserverroles,327–332granting,denying,andrevoking,339–348grantingtologins,332hierarchyof,326forLOGINsecurable,326overviewof,325–327inroles,339–342PIVOTrelationaloperatordescriptionof,240savingcrosstabtableswith,242–243using,240–241preciseversusapproximatedatatypes,113precisionofnumberdatatypes,significanceof,108primarydatafiles.
SeealsodatafilesdesignatingwithFORATTACHclauseofCREATEDATABASEstatement,90–95specifyinglocationsfor,90PRIMARYKEYconstraintdescriptionof,107using,136–137primarykeys,designatingcolumnsas,135–138.
SeealsomulticolumnprimarykeysPRIMARYkeyword,usingwithONclauseofCREATEDATABASEstatement,86principaltype,identifying,310principalsassigningpermissionsto,324–327creating,313–324overviewof,308–313relationshipswithschemas,353usingsystemcatalogviewswith,309PrintViewprocedure,usingwithDataViews,495ProductSubcategorytable,extractingthreecolumnsfrom,157professionaldevelopers,useofSSEby,8ProSSEAppsdatabase,creating,43ProSSEAppsCh04database,creating,123protocols,enablingwithSQLServerConfigurationManager,20pubssampledatabase,downloading,24QqueriesdesigningwithViewDesigner,49reusingwithCTEs,232–239runningagainstSSEinstances,16runningfromcurrentdatabasecontextordifferentdatabase,166–167runningfromlinkedservers,172–174runningfromviews,167–168specifyingfromserverinstances,168–174typesof,168QueryBuilderdialogbox,opening,566QueryDesignerAddTabledialogboxin,558openingfromDatabaseExplorer,522,524querytabs,opening,52–53,60querytools.
SeealsoSSMS-basedquerytoolSSMSforSQLServerExpress,17wizardsassociatedwith,5QuickConsolewindow,supportfor,399,400R:rsqlcmdkeywordeffectof,63referencingfileofstatementswith,63rangeoflegitimatevaluesfornumberdatatypes,significanceof,108RANKfunctiondescriptionof,220using,222–223rankingfunctions,examplesof,219–220ranks,assigningtorows,222–224Readmethod,usingwithcolumnvaluesforsingleSELECTstatement,465ReadytoInstallscreen,displaying,16realdatatypedescriptionof,112specificationsfor,113recoverymodelschoosingandmodifying,101–102examplesof,99–101fullandbulk-loggedmodels,99,100settingsfor,100–101simplerecovery,100referencedatatypesinVBE,explanationof,375referentialintegrityrelationshiptoFOREIGNKEYconstraints,145–146supportingwithforeignkeys,146–150RefreshiconinNewDatabaseDesigner,descriptionof,44RegistrationInstallationscreen,displaying,13regularexpressions,verifyingemailaddresseswith,477INDEX588relationalandcross-tabulatedtables,convertingbetween,239–244.
Seealsotablesrelationaldata,pivotingtocrosstabs,240–241relationaldatabasemanagers,mainpurposeof,107Relationshipscontextmenuitem,selectingfortablecolumns,47remotedown-levelclients,connectingfrom,24–30REPLACEfunction,usingwithstrings,162Restartoption,usingwithserverinstances,20restoreoperation,performingondatabases,102–103restoringdifferentialbackups,104–105resultsetrows,ranking,219–224resultsets.
Seealsocombinedresultsetsappendingtooneanother,229–232combiningwithorwithoutduplicates,229–230fordatabaserolesandusers,312generatingfileswith,404–405manipulating,224–225readingfilevalueswith,405–408returningfromsinglestoredprocedure,270–271returningwithstoredprocedures,271–278returningwithoutparameters,268–271savingwithINTOclause,225–228ResultstabinViewDesigner,populating,49returnstatusvalues,processing,282–284RETURNSclause,usingwithCREATEFUNCTIONstatements,263–264REVERTstatement,usingwithSQLServerlogins,316–317REVOKEpermission,descriptionof,339rightjoins,syntaxfor,196rolepermissions,fine-tuningwithGRANTandDENYstatements,342–345roles,explanationof,309ROLLBACKstatementssyntaxfor,297usingwithAFTERtriggers,295–297rownumbervalues,assigningtorows,220–222roworder,controllingwithORDERBYclause,215–219rowsearches,performingwithouterjoins,195–197ROW_NUMBERrankingfunctiondescriptionof,220using,221–222rowsaddingfordatamanipulationwithstoredprocedures,285–287addingtoClassestable,286addingtoClassGradestable,286addingtodatabasetablesthroughDataTables,501–502addingtotables,48assigningNTILEnumbersto,223–224assigningranksto,222–224assigningrownumbervaluesto,220–222computingindatasources,180copyingfromtables,228countingwithouterjoins,197–198deletingfromtables,288–290deletingin,478–483filteringwithoperators,178–179filteringwithBETWEENoperator,179–181filteringwithINoperator,181–183inserting,updating,anddeleting,498–509insertingformany-sideofone-to-manyrelationship,254–255insertingintables,475–478insertingintablesrelativetoviews,253–254mergingfrommultipletables,206–208sortingbasedonmultiplecolumnvalues,217–218sortingbasedonsinglecolumnvalues,216–217updatingrelativetoviews,255–256usingcrossjoinswith,198–200usingDataTablestoupdatecolumnvaluesin,503–504usinginnerjoinswith,192–195usingouterjoinswith,195–198usingself-joinswith,200–204rowswithmatchingcolumnvalues,usingself-joinswith,200–202runtimeerrors,dynamicallyadaptingto,395–397S-Sswitch,usingwithsqlcmdutility,60–61,67saSQLServerlogin,advisoryaboutuseof,36SalesForATitleIDstoredprocedure,scalarvaluesreturnedby,546sampledatabases,installing,24–25savingcrosstabtables,242–243.
sqlfiles,59–60SayHelloDynamicallyWebFormlayingout,431–433verifyingoperationof,434–435scalaruser-definedfunctions.
SeeFNs(scalaruser-definedfunctions)scalarvalues,returningwithoutputparameters,278–281scaleofnumberdatatypes,significanceof,108ScheduleiconinNewDatabaseDesigner,descriptionof,44schedulingtasks,69–71schema,explanationof,119,156schemas.
Seealsodboschema;namedschemas;ownedschemascreatingandowning,349–353creatingobjectsin,356–359explanationofoverviewof,348–349powerof,350relationshipswithprincipals,353INDEX589scopesofprinciples,examplesof,308Scriptcontextmenuitems,generatingT-SQLscriptswith,56ScripticoninNewDatabaseDesigner,descriptionof,44Scriptmenuitems,displaying,51scriptingvariables,usingwithsqlcmdstatements,66–67searchingandcopyingdatabaseswithWindowsExplorer,83securables,overviewof,325securityfeatures,availabilityof,307–308securityadminfixedserverrole,using,329SelectCase.
.
.
EndSelectstatement,usingwithsinglevaluesandCommandobjects,458SELECTlistitems,specifying,156–165SELECTlistsincludingsubqueriesin,208–210specifyingrankingfunctionsfor,220usingwithfour-tablejoins,207SELECTstatementsbuildinggraphicallyinViewDesigner,49calculatingcomputedlistitemswith,159–162comparingtimeformatswith,164creatinggraphically,51creatinguserswith,322displayingdatetimevalueswith,162–165featuresof,156mergingmorethantwodatasourcesin,205–208permittingrunningbyusers,336–338readingcolumnvaluesreturnedby,464–469specifyingargumentsinOPENROWSETfunctionwith,171specifyingforreusewithCTEs,232–233usingBETWEENoperatorwith,179–180usingGROUPBYclausewith,183usinginnerjoinswith,192–195usingUNIONoperatorswith,229–232usingWHEREclausewith,175–177usingwithcrossjoins,199usingwithCTEs,238usingwithhierarchicalmetadata,81,82usingwithIFuser-definedfunctions,264usingwithloginsandfixedserverroles,331usingwithobjectsandownedschemas,354usingwithouterjoins,196–197usingwithprincipals,311usingwithself-joins,200–204usingwithsigneddata,366usingwithSQLServerlogins,315,317,318,319,320usingwithsqlcmdscriptingvariables,66usingwithwidecharacterdatainvarcharcolumns,125versusviews,247SelectedIndexChangedeventprocedure,usingwithListBoxcontrols,538self-joinsdefinitionof,192usesfor,200usingtojoinrowsbasedontwocriteriaexpressions,202–203usingtojoinrowswithmatchingcolumnvalues,200–202serverinstances.
SeealsoSQLServerinstancesconnectingtousingWindowsauthentication,38–41andDAC(DedicatedAdministratorConnection),61restartingtoregisterchanges,20specifyingqueriesfrom,168–174serverlogins,syntaxfor,320server-basedwebsites,creatingandcopyingfilesto,429–431:serverlistkeyword,usingwithsqlcmdutility,62serversdetachingdatabasesfrom,96,97runningASP.
NETcodeon,410ServiceAccountscreen,displaying,14ServiceBroker,significanceof,11SETNOCOUNTstatement,usingwithcrossjoins,198SETstatementsusinginaggregation,188usingincharactermatches,176usingwithCTEs,236usingwithreturnstatusvalues,282setup.
exe,invoking,12severitylevelsforerrors,reporting,133SharedMemoryprotocol,significanceof,19ShortandUShortdatatypes,usinginVBE,383–384ShortGridView.
aspxpage,displayingandopening,562ShowmethodofMessageBoxclass,usinginVBE,382SignandInsertstoredprocedure,inputparametersfor,364SignedDatatable,insertingrowin,366signingdata,363–367simplerecoverymodel,implementing,100SingledatatypeinVBE,descriptionof,376–377SIZEkeywordusingwithLOGONclauseofCREATEDATABASEstatement,88usingwithONclauseofCREATEDATABASEstatement,86–87smalldatetimedatatype,descriptionof,109,111smalldatetimevalues,storageof,162smallintintegerdatatype,specificationsfor,110smallmoneydatatype,specificationsfor,110smarttagwindow,usingwithGridViewcontrols,563smarttagsmodifyingcontrolbehaviorwith,532–538INDEX590usingwithListBoxcontrols,542usingwithparameterqueries,540softwareandhardwarerequirementsforSSE,12SolutionExplorercreatingOnePage.
htmfilein,428viewingpagesin,423solutions,startinginVWDE,423solutionsinVBEaddingitemsto,372exploring,373startingandsaving,371–372sortorder,changinginDataGridViewcontrols,496–497sortedvalues,returningtoDataTables,489–491sorting,enablingforGridViewcontrols,564sp_*,indicatingsystem-storedprocedureswith,267.
Seealsosystem-storedproceduressp_addlinkedserverdescriptionof,267invoking,172sp_adduser,using,334–335sp_attach_*statements,advisoryabout,89sp_attach_db,descriptionof,267sp_dbcmptlevel,descriptionof,77sp_dbfixedrolepermission,executing,337–338sp_dboptiondescriptionof,267usingwithsystemcatalogviews,80sp_detach_dbdescriptionof,267detachingdatabasefileswith,96sp_dropdevice,removingbackupdeviceswith,106sp_droplinkedsrvlogin,usingwithlinkedservers,173sp_dropserver,usingwithlinkedservers,173sp_helpdbdescriptionof,267executing,87usingfordatabasehelp,76–78sp_helpdbfixedroleinvokingwithEXECstatement,337usingwithprincipals,313sp_helplinkedsrvlogin,usingwithlinkedservers,173sp_helprolemember,usingwithprincipals,313sp_helpsrvroleusingwithlogins,329usingwithprincipals,310sp_helpsrvrolemember,usingwithprincipals,311sp_linkedserversdescriptionof,267usingwithlinkedservers,173sp_linkedsrvlogin,descriptionof,267sp_rename,descriptionof,267sp_srvrolepermission,usingwithprincipals,311.
sqlfiles,savingandopening,59–60SQLNativeClient,significanceof,8SQLServer2005versusSSE,9–11SQLServerauthentication.
Seealsoauthenticationexplanationof,22connectingbymeansof,62connectingwithusingSSMS-basedquerytool,35–37SQLServerBrowserserviceadvisoryabout,20determiningstatusof,20enabling,21stoppingfromSQLServerConfigurationManager,27SQLServerConfigurationManagerenablingprotocolswith,20opening,18stoppingSQLServerBrowserservicefrom,27SQLServerdatabases,openingandclosingconnectionsto,448–450SQLServerExpress.
SeeSSE(SQLServerExpress)SQLServerExpressInstallationWizard,launching,12SQLServer,identifierrulesfor,82SQLServerinstances.
Seealsoserverinstancesconnectingtousingsqlcmdutility,60–62connectingtousingSSMS-basedquerytool,34–41purposeforconnectingto,62SQLServerloginscreating,313–317creatingnewusersfor,322–323SQLServerSurfaceAreaConfigurationtool,usingwithSSEinstances,91SQLstatements,avoidingwritingof,499sql_variantdatatype,using,118sqlbrowser.
exefile,enablingSQLServerBrowserwith,21SqlBulkCopyclass,migratingExceldatatoSSEwith,510SqlBulkCopyinstance,using,512–514sqlcmdstatementsbatching,67–69rerunningandediting,68runningandbatchingdynamically,65–71sqlcmdswitches,viewing,60sqlcmdutilityconnectingtoSQLServerinstanceswith,60–62connectingwithSQLServerauthentication,62connectingwithWindowsauthentication,61descriptionof,8guidelinesforuseof,62overviewof,60runningstatementsfromfileswith,62–64savingoutputtofileswith,64–65usingtogeneratefilewithresultset,404–405workingaroundshortinputandoutputrulefor,63INDEX591sqlcmd.
exeprogram,invoking,60SqlCommandclassinstance,descriptionof,454SqlCommandobjectusing,459usingwithcolumnvaluesforsingleSELECTstatement,465SqlConnectionobjects,creatinginstancesof,449SqlConnectionStringBuilderclass,usingwithADO.
NET,450SqlDataAdaptersample,486–489SqlDataReader,populatingitemscollectionofListBoxwith,469–470SQLEXPRESSnamedinstance,setting,18,19sqlexpr.
exe,launchingSQLServerExpressInstallationWizardwith,12SQLservr.
exefile,significanceof,21SSE(SQLServerExpress)ADO.
NETconnectionsto,451–453Collation,ErrorReporting,andReadytoInstallScreensin,15–16configuringfornetworkaccess,19–20configuringtostartautomatically,17–19hardwareandsoftwarerequirementsfor,12installing,12–16versusMSDE,11–12overviewof,3–5performingsystemcheckfor,12–13Registration,Feature,andInstanceNameScreensin,13ServiceAccountandAuthenticationModescreensin,14–15versusSQLServer2005,9–11SSEdata,viewing,49–50SSEdatabases.
Seealsodatabasesauto-closefeaturein,74lookinguporderhistoryin,569–573minimumnumberoffilesin,43usingDatabaseExplorervisualtoolswith,517–519usingtableswith,26SSEinstancesenablingtoworkwithdatabasefiles,452retrievingmetadatafrom,75–76settingup,18usingSQLServerSurfaceAreaConfigurationtoolwith,91usingWindowsaccounttoconnectto,41SSEquerytools.
SeequerytoolsSSEusersbusinessanalysts,6–7DBAsandoperationsspecialists,7–8hobbyistsandothernonprofessionaldevelopers,6professionaldevelopers,8SSMS-basedquerytool.
Seealsoquerytoolsconnectingfrom,22–23connectingtoSQLServerinstanceswith,34–41connectionobjectsin,37descriptionof,17designing,running,saving,andrerunningSQLscriptswith,51–60launching,22overviewof,33–34usingdesignerswith,41–51andWindowsauthenticationconnections,38–41SSMSLoginaccount,significanceof,39Standardtoolbar,identifying,23standardview,descriptionof,248statements,runningfromfiles,62–64storedprocedureoutputparameters.
Seeoutputparametersstoredprocedures.
Seealsosystem-storedprocedures;usp_*addingwithDatabaseExplorervisualtools,525–526contrastinginputparametersfor,276–278creating,269creatingandusing,267–268deletingrowsintableswith,288–290filteringfornullvalueswith,274–275grantingEXECUTEpermissionfor,289performingdatamanipulationwith,284–292performinglookupswith,545–547returningresultsetsandinputparameterswith,271–278returningresultssetsfrom,270–271running,270specifyingdefaultparametervalueswith,273–274syntaxforinvocationof,270updatingcolumnvalueswith,287–288usingtoreturnresultsetswithoutparameters,268–271usingwithBULKINSERTstatements,291usingwithparameters,284versusviewsanduser-definedfunctions,268stringvalues,calculatingexpressionsfor,161StringvariablesinVBE,contentsof,381StringversusStringBuldertypesinVBE,382StringBuilderclass,usingwithCharandStringdatatypesinVBE,377–379strings,usingREPLACEfunctionwith,162StrToDblFunctionprocedure,using,394–395Studentstable,creatingviewsfor,252–253subqueries.
Seealsocorrelatedsubqueriesdefinitionof,208includinginSELECTlists,208–210includinginWHEREclauses,211–214sys.
columnsandsys.
typesviews,joining,258sys.
database_principalsview,usingwithprincipals,309sys.
databasessystemcatalogview,using,79–80sysnamedatatype,using,116sys.
server_principalsview,usingwithprincipals,309systemcatalogviews.
Seealsoviewsexploringcertificatesandkeyswith,360–361INDEX592featuresof,78andmodificationis_auto_close_oncolumnvalues,80–81returninghierarchicalmetadatawith,81–82sys.
databases,79–80usingwithprincipals,309systemcheck,performingforSSE,12–13systemdatabases,typesof,75systemviews.
Seealsoviewsprocessingmetadatawith,256–259trackingviewswith,251system-storedprocedures,usingEXECUTEstatementwith,76.
Seealsosp_*;storedproceduresSystem.
Xml.
dllassembly,addingreferenceto,464,468sys.
views,returningINFORMATION_SCHEMA.
VIEWScolumnswith,257–258Ttablechanges,archivingwithtriggers,297–302tablecolumns.
Seecolumnstabledatatype,using,120TableDesignerfeaturesof,44topandbottompanesof,46tablevaluesextractingandprocessing,129–130retrievingtotestdataconnections,26TableAdaptercomponent,usingwithDataSourcewizard,526tables.
Seealsoderivedtable;relationalandcross-tabulatedtablesaddinganddropping,473–475addingandpopulatingwithdata,44–49addingdatato,48,124–128conceptualizing,239copyingandsavingresultsetswithINTOclause,225–228copyingselectedrowsfrom,228creatingforExcelworksheets,511–512creatingviewsfor,252–253creatingwithcolumns,121–124insertingrowsin,253–254,475–478joininginViewDesigner,55–56modifyingwithALTERTABLEstatement,128naming,47processingchangesto,505–509recoveringvaluesfor,126–128returningcolumnnamesin,81–82usingDataTablestoaddrowsto,501–502usingwithSSEdatabases,26tasks,scheduling,69–71tempdbdatabase,descriptionof,75TemplateExplorerdescriptionof,58T-SQLcodesamplesin,52templatesstartingASP.
NETprojectswith,412usingwithASP.
NET,413textdatatype,using,116textfiles,readingbasedonComboBoxandListBoxselections,403–404Textproperty,usingwithASP.
NETpages,419TextBox,usingwithWebForms,431–435TextBox3,checkingforvaluein,480TFs(multi-statementtable-valueduser-definedfunctions),descriptionof,259Thenclause,usingwithWebForms,434timeanddatedatatypes,overviewof,111timeformats,comparingwithSELECTstatement,164times,assigningcurrenttimes,133–135TimeSpanstructure,usingwithDatedatatypesinVBE,377timestampdatatype,using,117tinyintintegerdatatype,specificationsfor,110Titleproperty,usingwithASP.
NETpages,415TOPkeywordusinginaggregation,187–188usingwithinnerjoins,193usingwithORDERBYclause,215usingwithSELECTstatements,158–159toporbottomrows,listingwithORDERBYclause,218–219triggersAFTERtriggers,295–297archivingtablechangeswith,297–302copyingvalueswith,301–302creatingandusing,292–293INSTEADOFtriggers,302–304performingdatamanipulationthroughviewswith,304–305Tryblock,usingwithfilevaluesandresultsets,406Try.
.
.
Catchstatementsformforrunningof,392–393invokinginfunctionprocedures,394–395usingdirectly,393–394Try.
.
.
Catch.
.
.
Finallystatementseffectof,391usingwithADO.
NETconnectionstrings,451usingwithsinglevaluesandCommandobjects,458–459T-SQLrankingfunctionsin,219–220relationshiptoSSE,9T-SQLbatches,terminatingwithGOkeyword,173–174T-SQLcode,generatingautomatically,54–58T-SQLscriptsgeneratinginObjectExplorer,56formodifyingis_auto_close_oncolumnvalues,81forreturningmetadataaboutcolumnsintables,81scriptingvariablesusedwith,66INDEX593T-SQLstatementsopeningquerytabforwritingof,52–53scheduling,69–71typinginquerytab,23writingwithSSMS-basedquerytool,51–52TwoDTsInADScodesample,487two-tablejoins,upgradingtothree-tablejoin,205–208U-Uswitch,usingwithsqlcmdutility,62Unicodecharacterdatatypesoverviewof,116–117specifyingcolumnswith,124usinginVBE,377–378UNIONoperatorusingwithCTEs,237usingwithresultsets,229–232UNIQUEconstraint,descriptionof,136uniqueidentifierdatatype,using,117–118UNPIVOTrelationaloperatordescriptionof,240using,243–244Updatemethod,usingwithDataAdapterobjects,486,503UPDATEstatementsdependenceonreferentialintegrity,149designatingchangesformultiplecolumnsin,153usingwithinsertedanddeletedtables,294–295usingwithsigneddata,366usingwithtriggers,301–302usingwithviews,255–256UPDATEtriggers,using,299–300updating,restrictingwithDataGridViewcontrols,533–535USEstatementsexampleof,66inquerytab,55usingwithhierarchicalmetadata,81,82userexperience,simulatingwith.
exefile,390userinstanceconnectionstring,significanceof,516UserInstancesscreen,displaying,16user-definedfunctionscontrastinginputparametersfor,276–278versusstoredprocedures,268typesof,259usersassigningcurrentusers,133–135creating,321–324permittingtorunSELECTandINSERTstatements,338permittingtorunSELECTstatements,336–338UShortandShortdatatypes,usinginVBE,383–384Usingstatement,usingwithfilevaluesandresultsets,406,407Using.
.
.
EndUsingblock,usingwithapplicationvariables,438usp_AddClassstoredprocedure,using,285.
Seealsostoredproceduresusp_AddClassGradesstoredprocedure,specifyingwithCREATEPROCstatement,286–287usp_DeleteGradesClassesstoredprocedure,creating,289usp_MedialSubtotalstoredprocedure,exampleof,280usp_MinMaxSubTotalstoredprocedure,effectof,279usp_NumberIsOddstoredprocedure,calling,277–278usp_UpdateLastNamestoredprocedure,usingCREATEPROCstatementwith,287–288V-vswitch,usingwithsqlcmdutility,67valuedatatypesinVBE,explanationof,375valuetypes,boxing,375valuesassigningtocolumnsautomatically,46assigningtovariables,157copyingwithtriggers,301–302lookingupbasedonCTEcolumnvalues,234–235recoveringfromoldtablesfornewtables,126–128returningfromviewsasstoredprocedureoutputparameters,279–280returninginWebapplications,460–462returningwithCommandobjects,455–460showinginDataViews,495–496showingwithGROUPBYclause,183–184valuesfromfiles,readingwithresultsets,405–408valuesinranges,filteringfor,177–183VALUESkeyword,usingwithwidecharacterdatainvarcharcolumns,125varbinary()datatype,using,117varbinary(max)datatypesignificanceof,10using,117varcharcolumns,insertingwidecharacterdatainto,124–126varchardatatype,using,116varchar(max)datatype,significanceof,10variablesassigningvaluesto,157declaringandassigningvaluesto,159declaringinVBE,379–385variable-widthdatatypes,specifyingforcolumns,123VBE(VisualBasicExpress)comparingStringvariablevaluesin,380–381INDEX594DataSourceswindowin,526declaringvariablesandassigningdatatypevaluesin,379–385featuresof,374firingClickeventproceduresin,381startingandsavingnewsolutionprojectsin,371–372StringversusStringBuldertypesin,382usingVisualBasicdatatypesin,374–385VBEdatatypevalues,assigning,379–385VBEdatatypes.
Seealsodatatypesandassignmentstatements,381–385Booleandatatypes,377CharandStringdatatypes,377–379Datedatatypes,377numberdatatypes,376–377usingDimstatementswith,379–380usingOptionExplicitOnVBEsettingwith,380–381VBEvisualtools.
SeeDatabaseExplorervisualtoolsvContactNameview,descriptionof,50–51VerifySignedByCertfunction,usingwithsigneddata,365VIEWDEFINITIONpermission,effectof,326ViewDesignercreatingandsavingviewswith,49–50joiningtwotablesin,55–56viewvalues,returningasstoredprocedureoutputparameters,279–280views.
Seealsosystemcatalogviews;systemviewsaccessingdatawith,248–252creatingandusingwithdboschema,249–250creatingforClasses,ClassGrades,andStudentstables,252–253creatinginnamedschemas,250–252extractingdatafrom,249featuresof,50–51,247–248versusIFuser-definedfunctions,259modifyingdatawith,252–256performingdatamanipulationthrough,304–305reportingcolumnmetadatafor,258–259runningqueriesfrom,167–168versusstoredprocedures,268trackingwithschemas,251typesof,248usingconstraintswith,248usingINSTEADOFtriggerswith,302–305VisualBasic,integerdatatypesin,376visualdatabasetools.
SeeDatabaseExplorervisualtools;VWDEvisualdatabasetoolsVisualStudio2003,connectingtoSSEfrom,25–27VWDE(VisualWebDeveloper)featuresof,427startingsolutionsin,423VWDEvisualdatabasetoolsfordisplayingdataonWebFormpageswithGridViewcontrols,560–564formakingdatabaseconnections,554–557forperforminglookupsonWebFormpages,564–573forviewingandmodifyingdatabases,558–560WWebapplicationsreadingdatain,431–435returningvaluesin,460–462WebFormpagesperforminglookupson,564–573startingwithsingle-filecodemodel,415–417WebFormusers,sayinghellotodynamically,431–435WebFormsaddingtoASP.
NETprojects,416managingdynamicallywithIsPostBackproperty,433–434MessageForTheDay,424–427startingwithcode-behind-page-file,420usingcontrolswith,432–433usingGridViewcontrolstodisplaydataon,560–564websitesBooksOnline,16,163creatingforusewithASP.
NETprojects,412forIISinstallationonWindowsXP,430MicrosoftDownloadCenter,12forsampledatabases,24XHTMLresource,421well-formedXMLdocument,explanationof,119WHEREclausesincluding<=operatorin,178includingcorrelatedsubqueryin,214–215includingsubqueriesin,211–214usingparametersin,272–273usingwithBETWEENoperator,179–180usingwithinnerjoins,193–194usingwithSELECTstatements,175–177usingwithself-joins,203–204Whileloop,usingwithcolumnvaluesforsingleSELECTstatement,465While.
.
.
EndWhilestatement,usingtopopulateitemscollectionofListBoxcontrol,470WhoWhenDefaults.
sqlcreatingfive-columntablein,134resultlistingforrunningscriptin,135wildcardcharacters,usingincharactermatches,175–176Windowsaccounts,connectingtoSSEinstanceswith,41Windowsapplications,returningvalueswithCommandobjectsin,455–460INDEX595Windowsauthentication.
Seealsoauthenticationconnectingtoserverinstanceswith,38–41connectingwithsqlcmdutility,61descriptionof,22WindowsExplorer,searchingforandcopyingdatabaseswith,83WindowsFormcalculatoraddingbuttonclickeventproceduresto,387–389creatingstartupformfor,385–386populatingwithcontrols,386–387testingcodebehind,389–390WindowsForms,draggingdatasourceitemsto,529–531Windowsloginscreating,318–321creatingnewusersfor,323WindowsXPfirewall,configuringforSSE,21–22WITHkeyword,usingwithCTEs,234,236wizards,availabilityof,5XXCopydeploymentadvantageof,452enablingwithauto-closefeature,74restrictionson,5significanceofandsupportfor,4,11–12XHTMLresource,421xmldatatype,using,118–120xp_cmdshellsystem-storedprocedureusingstringswith,96usingwithprimarydatafiles,91,92Zzeroes,assigninginsteadofnullvalues,132–133INDEX596