Webtesthere.com



Command or Query/StatementCREATE TableBefore creating a table, think about the following:what information you need to store in this table to define the columnswhether or not each column can be NULLwhat value each column will store to define its Type and sizewhether or not column should/may have a Default value and what should it bethink about primary key, decide whether it should be an IDENTITY, whether or not it should be clustered. Usually PK is some kind of IDthink what columns’ combination should be unique in this table. You might need to add it as Unique Key, so SQL will prevent everybody to insert duplicated data.try never to define a PK on the column that has a type of UNIQUEIDENTIFIERit’s a good practice to have the following columns in each table:create_user_id and modify_user_id and make both NOT NULLcreate_timestamp and modify_timestamp and make both NOT NULL and DEFAULT to GETDATE()you will need to think about indexes needed (usually you will add it later on once you know how information is usually pulled up from this table)Never create a separate index just on PK columns because PK is already an index. Please note that sometimes you might need to add an index that includes the same columns as PK, but needs some extra columns as main or included columnsCREATE TABLE person(person_id BIGINT IDENTITY (1 ,1) NOT NULL,--Will be PKlast_name VARCHAR (60) NOT NULL, first_name VARCHAR (60) NOT NULL, date_of_birth DATE NOT NULL,ss_num CHAR(9) NOT NULL,address1 VARCHAR (100) NULL, country_id INT NULL,delete_ind CHAR(1) DEFAULT('N'),--N means person is active, 'Y' means - is not Activecreated_by INT NOT NULL, -- The user who first captured the recordmodified_by INT NOT NULL, -- The user who modified the recordcreate_timestamp DATETIME DEFAULT getdate () NOT NULL,-- Date record was createdmodify_timestamp DATETIME DEFAULT getdate () NOT NULL-- Date record was modified) ON VK_FileGroupGOTo Add a new columnPlease note that if you want to add a NOT NULL column to the existing table that has any data, you will need to DEFAULT this value to something because the already existing records will not have any values for this new column.Example:ALTER TABLE person ADD vcCity VARCHAR(60) NOT NULL DEFAULT('Los Angeles')ALTER TABLE person ADD vcCity VARCHAR(60) NULL PRIMARY KEY (PK) - table may only have ONE Primary Key (PK). It is automatically a UNIQUE key.PK can be CLUSTERED (by Default) or NONCLUSTERED Primary Key can NOT have included columns--To create a Nonclustered PK on the table:ALTER TABLE person ADD CONSTRAINT pk_personPRIMARY KEY NONCLUSTERED (person_id) ON VK_FileGroupGO --To create a Clustered PK on the table. Please note if you don’t specify CLUSTERED, it still will be created as CLUSTERED by Default:ALTER TABLE person ADD CONSTRAINT pk_personPRIMARY KEY CLUSTERED (person_id) ON VK_FileGroupGODROP TableOnce you do it, table will not exist in the database any longerDROP TABLE personDROP constraintALTER TABLE person DROP CONSTRAINT pk_personCREATE UNIQUE index - A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is unique based on those valuesCREATE UNIQUE NONCLUSTERED INDEX ak_person_1 ON person(last_name,first_name)It means that there might only be ONE and only ONE record with the same combination of (last_name and first_name). Of course in reality, you may have people with the same last_name+first_name, but just for our class, we will assume that you can NOT CLUSTERED INDEXA table may only have one CLUSTERED INDEX.Clustered index can not have included columns.* Clustered index sorts and stores the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only ONE clustered index per table, because the data rows themselves can be sorted in only one order.* The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a Clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a HEAP.Example:CREATE CLUSTERED INDEX inx_person_1 ON person(ss_num) ON VK_FileGroupUNIQUE INDEXESA table may have one Clustered and multiple Nonclustered indexes:* Both clustered and nonclustered indexes can be UNIQUE. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value. A table may have multiple UNIQUE keys but usually it will be NO or 1 unique indexOther INDEXESA table may have many NONCLUSTERED Indexes. Nonclustered indexes may have included columnsIndexes usually help with SELECTs, but may slow down the INSERT/UPDATES, so do not create too many indexes.See if you may combine multiple indexes into one especially with included columnsTo create the best indexes:Usually columns that are used in WHERE clause goes as main index_columnsColumns that are listed in SELECT will go into INCLUDED columnsColumns that are used in JOIN - usually goes as index_columns, but may work well in INCLUDEDExample:CREATE NONCLUSTERED INDEX inx_person_2 ON person(last_name, first_name, date_of_birth, delete_ind) INCLUDE(address1, country_id) WITH (ONLINE=ON, DROP_EXISTING=ON)ON VK_FileGroupPlease note:WITH (ONLINE=ON)- can only be used if you have SQL Enterprise Edition. If you create index during production hours, you must use WITH(ONLINE=ON) in order not to affect users and not to cause performance issues. If you don’t have Enterprise Edition, but table is small, it might be OK to create an index even if you don’t have Enterprise Edition DROP_EXISTING=ON - is used when you know that index already exists, so the command will drop the existing and then re-create it with columns specifiedIf you are not sure whether or not INDEX exists:--If you are not sure if index already exists, you can always run the following Example:IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'inx_person_2' AND OBJECT_NAME(object_id) = 'person')BEGIN DROP INDEX person.inx_person_2END GOCREATE NONCLUSTERED INDEX inx_person_2 ON person(last_name, first_name, date_of_birth, delete_ind) INCLUDE(address1, country_id) WITH (ONLINE=ON) ON VK_FileGroupSELECT StatementCreate a SQL SELECT:Find out what data you need to returnFigure out where this data is coming from - what tables/columns? List all the tablesFind out what information is not required to be displayed on the report. These tables should have LEFT JOIN. You still will list all the columns, but some of the data will be NULLThink if you need to add ISNULL for data returned, so report will have ‘’ vs NULLThe first table in the FROM should be a table where data must Exist.Always use Alias If possible - WITH(NOLOCK) (as discussed – this depends on the purpose of the query/business)WHERE clause: What are the conditions for data to be retrieved? What condition should limit data on the report?Calculated Information: Do you need to calculate any data on the report? If so, you might need to have GROUP BY, HAVING BYIf you use SUM in the select, you will need to use GROUP BYMay your query return duplicated rows? If so, make sure you don’t miss any joins. If all is correct, then use DISTINCTAlways check what the sort order should beORDER BY columns must be presented in the SELECT at least from 2008 – 2012Always Test when data exists in all tables and when data doesn’t exist in all tablesRun it with Execution Plan to see if you need to add any indexes. If you have Table Scan or Index Scan, think about the indexes you might need or modifyINSERT StatementINSERT:Check what columns are Mandatory in the table where you INSERT data intoDecide whether data inserted will come from the variables or SELECT from another table or combination – some from SELECT, some from variablesMake sure that the type of the column and data you insert there are the same typeFind out what is PK on the tableCheck if table has any unique keys and validate that data you insert not exists yetMake sure that you validate that combination of unique columns you insert not exists in the table yet or you will get an errorYou can either do it with NOT EXISTS or with LEFT Join check Test it – run the same Insert multiple times to make sure it doesn’t INSERT duplicates and provides correct Error NotificationsPlease note that the following INSERT doesn’t have info for delete_ind, create_timestamp, modify_timestamp, so these values will be set to the DEFAULT values defined by ‘CREATE TABLE’.Please note that the following INSERT doesn’t have info for address1 and country_id. It’s not a problem because these 2 columns can be NULL, so they will be set to NULL automatically.Please note that person_id is an IDENTITY columns, so SQL will automatically set it to the correct id:IF NOT EXISTS(SELECT 1 FROM person WITH(NOLOCK) WHERE ss_num = '322223111')BEGININSERT INTO person (last_name,first_name,date_of_birth,ss_num,created_by,modified_by)VALUES( 'Vasechkin', 'Petr', '1960/04/01', '322223111', 0, 0)ENDELSEBEGINPRINT 'Person with this social security id already exists'ENDStored procedures vs ADHOCStored procedures are:* Easy to maintain* you may reuse sp in diff part of application and by diff developers* everybody has access to the sp on SQL server and can review it to see if they can use it* SQL can reuse the execution plan* sp are much easier to modify and provide the fix to clients.If there is an issue and the statement is directly in the application code, then even though it might be easy to fix, it would be much harder to provide the fix to the client then if this statement was in sp. Because in case you use sp, then once the changes are done, tested and approved, you can just provide the corrected sp to the client.Also, if there is bug in sp, you only fix it in sp and it fixes the issue in every part of application that is using this sp. If you have the same problem with the statement in the application and it is used in multiple places, then you will need to find this code everywhere in application and fix it everywhere one by one. Then you need to test it everywhere to make sure you didn’t make a mistake while doing it in multiple places vs with sp – you can test it in one place because no matter where you use this sp - if sp is fixed, it’s fixed for every place where it is used.INSERT via Stored procedure WITH OUTPUT ParametersCREATE PROCEDURE spInsertPerson (--here you declare all the INPUT and OUTPUT sp parameters. @last_name VARCHAR(60), --should be the same type as in the table, or you will need to take care of it before inserting into person table @first_name VARCHAR(60), @ss_num CHAR(9), --use char vs varchar, because we know it always has to be 9 char @date_of_birth DATE, @address1 VARCHAR(150), @country_code VARCHAR(20), @user_id INT, @person_id BIGINT OUTPUT, @error_msg VARCHAR(100) OUTPUT)AS/*It's always a good idea to add comments in the beginning of the sp the describe sp create/modify date, changes, purporsePurpose: This sp will insert record into person table if it doesn't exist yetHistory:08/03/2016 VKInitial Creation of sp08/05/2016 VKAdded Error handling*/SET NOCOUNT ON --not to show how many records inserted--It's a good practice to always define all LOCAL Variables(variables that are needed only inside the sp) in the beginning of sp in one place vs adding it all over sp--Also it's a good practice to default the variables to some values to avoid problems with NULL DECLARE @country_id INT = 0SELECT@person_id = 0, @error_msg = 'ERROR' --Always use SELECT vs SET IF LEN(ISNULL(@country_code,'')) > 0BEGINSELECT @country_id = ISNULL(country_id, 0)FROM countryWHERE country_code = ISNULL(@country_code,'')ENDIF NOT EXISTS(SELECT 1 FROM person WITH(NOLOCK) WHERE last_name = @last_name AND first_name = @first_name)BEGININSERT INTO person (last_name,first_name,date_of_birth,ss_num,address1, country_id,created_by,create_timestamp, modified_by,modify_timestamp)VALUES(@last_name,@first_name,@date_of_birth,@ss_num,@address1, --not mandatory@country_id,@user_id,GetDate(), --if you don't set it, SQL will set it automatically to the DEFAULT@user_id,GetDate() --if you don't set it, SQL will set it automatically to the DEFAULT))SELECT @person_id = @@IDENTITY --Must be read right after INSERT,@error_msg = 'SUCCESS'ENDELSEBEGIN --means record already existsSELECT @person_id = person_id FROM person WITH(NOLOCK) WHERE last_name = @last_name AND first_name = @first_nameSELECT @error_msg = 'Person ' + @last_name + ' ' + @first_name + ' already exists'ENDSET NOCOUNT OFF --Set it back to OFFGOPlease note you need this table for this sp:CREATE TABLE country(country_id INT IDENTITY (1 ,1) NOT NULL,--Will be PKcountry_name VARCHAR (60) NOT NULL, country_code VARCHAR(20) NOT NULL,created_by INT NOT NULL, -- The user who first captured the recordmodified_by INT NOT NULL, -- The user who modified the recordcreate_timestamp DATETIME DEFAULT getdate () NOT NULL,-- Date record was createdmodify_timestamp DATETIME DEFAULT getdate () NOT NULL-- Date record was modified) ON VK_FileGroupGOExec SP with OUTPUT parametersDECLARE @person_id BIGINT, @error_msg VARCHAR(100)EXEC spInsertPerson 'Pyx1','Vinnie1','123456781','1970/04/01','Address','USA',0,@person_id OUTPUT,@error_msg OUTPUTSELECT @person_id, @error_msg Stored proc without OUTPUT parametersCREATE PROCEDURE spInsertCountry (--here you declare all the INPUT and OUTPUT sp parameters.@country_name VARCHAR (60), @country_code VARCHAR(20),@user_id INT)AS/*Purpose: This sp will insert record into country table if it doesn't exist yetHistory:08/05/2016 VKInitial Creation of sp*/SET NOCOUNT ON --not to show how many records inserted DECLARE @country_id INT = 0,@error_msg VARCHAR(100) = 'ERROR' IF NOT EXISTS(SELECT 1 FROM country WITH(NOLOCK) WHERE country_name = @country_name AND country_code = @country_code)BEGININSERT INTO country (country_name,country_code,created_by,create_timestamp, modified_by,modify_timestamp)VALUES(@country_name,@country_code,@user_id,GetDate(), --if you don't set it, SQL will set it automatically to the DEFAULT@user_id,GetDate() --if you don't set it, SQL will set it automatically to the DEFAULT))SELECT @country_id = @@IDENTITY --Must be read right after INSERT,@error_msg = 'SUCCESS'ENDELSEBEGIN --means record already existsSELECT @country_id = country_id FROM country WITH(NOLOCK) WHERE country_name = @country_name AND country_code = @country_code SELECT @error_msg = 'Country already exists'ENDSELECT @country_id, @error_msgSET NOCOUNT OFF --Set it back to OFFGOExec SP with NO OUTPUT parametersEXEC spInsertCountry 'United States of America','USA',5Stored procedure with TRANSACTION and TRY/CATCH Error HandlingCREATE PROCEDURE spInsertCountry2 (--here you declare all the INPUT and OUTPUT sp parameters.@country_name VARCHAR (60), @country_code VARCHAR(20),@user_id INT)AS/*Purpose: This sp will insert record into country table if it doesn't exist yetHistory:08/05/2016 VKInitial Creation of sp*/SET NOCOUNT ON --not to show how many records inserted DECLARE @country_id INT = 0,@error_msg VARCHAR(100) = 'ERROR', --assuming Error@error_id INT = -1IF NOT EXISTS(SELECT 1 FROM country WITH(NOLOCK) WHERE country_name = @country_name AND country_code = @country_code)BEGINBEGIN TRANSACTIONBEGIN TRYINSERT INTO country (country_name,country_code,created_by,create_timestamp, modified_by,modify_timestamp)VALUES(@country_name,@country_code,@user_id,GetDate(), @user_id,GetDate() )SELECT @country_id = @@IDENTITY --Must be read right after INSERT,@error_msg = 'SUCCESS',@error_id = 0COMMIT TRANSACTION END TRYBEGIN CATCHSELECT @error_id = ERROR_NUMBER() ,--or: @@ERROR,@error_msg = ERROR_MESSAGE()ROLLBACK TRANSACTIONEND CATCHENDELSEBEGIN --means record already existsSELECT @country_id = country_id FROM country WITH(NOLOCK) WHERE country_name = @country_name AND country_code = @country_code SELECT @error_msg = 'Country already exists', @error_id = 0 --Need to discuss with developer who is calling this spENDSELECT @country_id AS 'Country_ID', @error_msg AS 'Message', @error_id As 'Error_ID' SET NOCOUNT OFF --Set it back to OFFGODELETE StatementBefore you delete data, please make sure you delete data related to it from the ‘child’ tables.Always think about WHERE CLAUSE or you may accidently delete all records from the table--Delete from person table all records that don’t have valid Country_ID (meaning to delete all records with country_id = NULL or 0)--The best way:DELETE FROM personWHERE ISNULL(country_id,0) = 0--Also correct, but longerDELETE FROM personWHERE country_id IS NULL or country_id = 0--Delete from the person table all records that have Country_code = ‘NA’DELETE pFROM person p JOIN country cntr ON cntr.country_id = p.country_idWHERE cntr.country_code = 'NA'UPDATE Statement--Set vcCity to Los Angeles for all records in person_id table that have country USAUPDATE pSET p.vcCity = 'Los Angeles'FROM person pJOIN country cnt ON cnt.country_id = p.country_idWHERE cnt.country_code = 'USA' DELETE vs TRUNCATETo DELETE all data from the table:DELETE FROM personNext time you will insert data into this table, person_id will start from the last (person_id +1) that was in the table before data was deleted from the table.You can restore table from back up because data will be recorded into the log file.TRUNCATE TABLE personNext time you will insert data into this table, person_id will start from 1You can NOT restore table from back up because data will NOT be recorded into the log file.SELECT vs SETYou can define multiple variables with SELECT while SET can only define ONE variable at the time.Example:SELECT @person_id = 0 , @error_msg = 'SUCCESS'SET @person_id = 0SET @error_msg = 'SUCCESS'ISNULL*** Please remember that NULL and ‘’(Empty/blank) are not the sameIf value can be NULL, please add the following checkISNULL(address1,’’)*** If you concatenate 2 columns and one of them can be NULL, then always add ISNULL(that_column_name,’’) or otherwise it will NULL out the whole string because NULL + anything will be set to NULLRemember that if you need to compare column that can be NULL with anything else, always add ISNULL check around it. Ex:For INT: ISNULL(column_name,0) = 10Varchar/char: ISNULL(column_name,'') = 'Test'LIKE with ‘%%’Never use ‘%%’. In this case just don’t put it in the WHERE clause at all if you don’t care what the value isCASE in WHERE clause--Depending on the @iDelete, show all person that are Active or InactiveDECLARE @iDelete CHAR(1) = 'N'SELECT last_name, first_nameFROM person pWHERE p.delete_ind = CASE WHEN @iDelete='Y' THEN 'Y' ELSE 'N' END CASE in SELECT clause--Show all people from person table. If delete_ind = ‘Y’, then display word ‘Not Active’, for ‘N’, display ‘Active’. For anything else, display ‘Unknown’:SELECT last_name, first_name,CASE delete_ind WHEN 'Y' THEN 'Not Active'WHEN 'N' THEN 'Active'ELSE 'Uknown'END as Person_StatusFROM person pMore CASE examplesselect test, ?????? CASE ISNULL(vcTest,'') ?????????????????????????? WHEN '10'???? THEN?? 'It''s 10'?????????????????????????? WHEN '+'????? THEN?? 'It''s +'?????????????????????????? WHEN ''????????????? THEN?? 'Empty'?????????????????????????? ELSE?????????????????????? 'diff'?????? END 'TestCase',?????? CASE ????????????? WHEN iTest = 3?????? THEN 'Lera'????????????? WHEN iTest <> 2 THEN 'pasha'???????????? ????????????? ELSE?????????????????????? 'unknown'?????? END 'lp'from aaa TRANSACTION(COMMIT/ROLLBACK)TRANSACTION is needed when you have multiple insert/update/delete statements and you only want to COMMIT all of them or if any error, then none should be commited:BEGIN TRANSACTION?????? BEGIN TRY????????????? update aaa????????????? set test = 'New'????????????? WHERE cqm_item_id = 7??? ????????????? update bbb????????????? set summary = 'New' ????????????? Where cqm_item_id = 7????????????? COMMIT TRANSACTION????????????? print 'success'?????? END TRY?????? BEGIN CATCH????????????? print 'error'????????????? ROLLBACK TRANSACTION?? END CATCHTo display one person by last name if @last_name is passed to sp or all people if @last_name is not passed to sp(meaning it is passed as ‘’)To display one person by last name if @last_name is passed to sp or all people if @last_name is not passed to sp(meaning it is passed as ‘’):CREATE PROCEDURE sp_ListOfPatients (@last_name VARCHAR(60))ASSELECT last_name, first_nameFROM person p WITH(NOLOCK)WHERE last_name = @last_name or LEN(ISNULL(@last_name,'')) = 0sp_ListOfPatients ''sp_ListOfPatients 'Oslik'CREATE PROCEDURE sp_ListOfPatients2 (@last_name VARCHAR(60))ASIF LEN(ISNULL(@last_name,'')) > 0 --Last Name passed to sp, return record for one patient onlyBEGINSELECT last_name, first_nameFROM person p WITH(NOLOCK)WHERE last_name = @last_nameENDELSEBEGIN --last name not passed to sp, return a list of all patients sorted by last nameSELECT last_name, first_nameFROM person p WITH(NOLOCK)ORDER by last_nameENDsp_ListOfPatients2 ''sp_ListOfPatients2 'Oslik'To view the table description for table tbl_namesp_help tbl_nameTo see amount of records in the table tbl_namesp_spaceused tbl_nameInfo about constraint & Foreign KeysSp_helpconstraint tbl_nameTo view the sp text of sp_Namesp_helptext sp_NameTo see index’s info on table tbl_name. Note: doesn’t show included columnssp_helpindex tbl_nameGet Database ID in SSMSprint db_id()Update statisticsPlease note once index is created (or rebuild), the statistic is updated tooCheck when statistic was updated:SELECT name AS index_name,STATS_DATE(OBJECT_ID, index_id) AS StatsUpdatedFROM sys.indexesWHERE OBJECT_ID = OBJECT_ID('order_')ORDER BY STATS_DATE(OBJECT_ID, index_id) descGOUpdate statistic for one index inxPatient_1 on table patient:UPDATE STATISTICS order_ (inxPatient_1) WITH FULLSCANGOUpdate statistic for all indexes:UPDATE STATISTICS order_ WITH FULLSCANGOFind tables with column country_idSELECT c.name AS ColName, t.name AS TableNameFROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_idWHERE c.name = 'country_id' order by t.nameFind sp that are using table personselect object_schema_name(object_id), object_name(object_id), definition from sys.sql_modules where definition like '%person %'order by object_schema_name(object_id), object_name(object_id); ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download