SQL Server SQL Server Developer Beginner by mukilan December 16, 2022 written by mukilan December 16, 2022 Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry’s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum. Why do we use it? It is a long established fact that a reader will be distracted by the readable content of a page when looking at its layout. The point of using Lorem Ipsum is that it has a more-or-less normal distribution of letters, as opposed to using ‘Content here, content here’, making it look like readable English. Many desktop publishing packages and web page editors now use Lorem Ipsum as their default model text, and a search for ‘lorem ipsum’ will uncover many web sites still in their infancy. Various versions have evolved over the years, sometimes by accident, sometimes on purpose (injected humour and the like). Where does it come from? Contrary to popular belief, Lorem Ipsum is not simply random text. It has roots in a piece of classical Latin literature from 45 BC, making it over 2000 years old. Richard McClintock, a Latin professor at Hampden-Sydney College in Virginia, looked up one of the more obscure Latin words, consectetur, from a Lorem Ipsum passage, and going through the cites of the word in classical literature, discovered the undoubtable source. Lorem Ipsum comes from sections 1.10.32 and 1.10.33 of “de Finibus Bonorum et Malorum” (The Extremes of Good and Evil) by Cicero, written in 45 BC. This book is a treatise on the theory of ethics, very popular during the Renaissance. The first line of Lorem Ipsum, “Lorem ipsum dolor sit amet..”, comes from a line in section 1.10.32. The standard chunk of Lorem Ipsum used since the 1500s is reproduced below for those interested. Sections 1.10.32 and 1.10.33 from “de Finibus Bonorum et Malorum” by Cicero are also reproduced in their exact original form, accompanied by English versions from the 1914 translation by H. Rackham. Develop the Database scripts What is a Database? Logically the database is container that contains different database objects Database Tables — which holds the actual data, basic unit of storage Views TVFs SP SF Triggers Indexes Sequences . . . There are two types of databases 1. System databases:- which are automatically created during installation of the SQL server software. We have five system databases Master : meta data information ( Data about other data ) Model : act like a template for the new database msdb : data about other services ( SSIS package, Schedule the data ) tempdb : stores the data temporarily resourcedb : to access the master DB ( system tables ) metadata information of ( SYS schema ) for the proper functioning of the SQL server software, means to say DO NOT TOUCH KIND 2. User Defined databases:- which we ( developers ) create max up to 32767 UDD. — I want to store some data about my friends Table object ————- MyfriendsContact Name Gender MobileNo DOB Email Qualification Address Madhu M 78787 78 ghjghj Saritha F 5565 85 jkgkjg Ravi M 555 82 ggkgk Ramya F 67868 93 gjgg MCA ghjghj Praveen M 4334 80 gkjgkjg MCA ghkjgk Sheela F 67868 85 gkjgkg MTech ghjkgkg Database Table view TVFs . . — I want to create a new database T – SQL SQL — Std, any RDBMS T-SQL — derived from standard SQL SQL Std : 100 keywords — any RDBMS ( SQL server, Oracle, MYSQL, DB2, ….. ) : 20 keywords — specific to Sql server only ————— T-SQL : 120 Keywords 4800 35 to 40 Oracle has its own extensions 100 + 30 — SQL DB2 has its own extensions 100 + 25 Redshift SQL server SQL T-SQL SQL / T-SQL commands ———————- 13 commands — Master, — any RDBMS 1. DATA DEFINITION LANGAUGE Commands ( DDL) : are used to define the database objects which will act upon the database objects a. CREATE : is used to create a new database object ( any ) b. ALTER : is used to modify an existing database object ( any ) c. DROP : is used to remove an existing database object ( any ) 2. DATA MANIPULATION LANGAUGE Commands ( DML) which will act upon the data a. INSERT : is used to add a new record / row / data b. UPDATE : is used to modify the existing data c. DELETE : is used to remove the existing data / record / rows — removes specific data or the entire data d. TRUNCATE : is used to remove the existing data —– removes the entire data e. MERGE : is used to perform I/U/D based on the conditional logic synchronization purpose table — 1 million rows to remove all the data from that table DELETE — slow , 10 minutes, fully logged mode TRUNCATE — Prefer, very fast , less than 1 minute, minimal logged 3. DATA CONTROL LANGAUGE Commands ( DCL) : are used to control the access on the Database objects a. GRANT : is used to provide the access/ permissions ( Read / write ) on the DB objects to other users b. REVOKE : is used to take back the given access on the DB objects from other users 4. DATA TRANSACTION LANGAUGE Commands ( DTL) a. COMMIT : is used to save the changes permanently b. ROLLBACK : is used to undo the changes ( NOTE : upto the last commit ) I1 I2 I3 COMMIT I4 I5 ROLLBACK 5. DATA RETRIEVAL / QUERY LANGAUGE Commands ( DRL / DQL) a. SELECT — very very important — 70 % database subject is used to retrieve / get the data from the database ( table ) MyfriendsContact —————— Name Gender MobileNo DOB Email Qualification Address Madhu M 78787 12 ghjghj Saritha F 5565 22 jkgkjg Ravi M 555 24 ggkgk Ramya F 67868 14 gjgg MCA ghjghj Praveen M 4334 17 gkjgkjg MCA ghkjgk Sheela F 67868 14 gkjgkg Mtech ghjkgkg Database Table — store — to create a new database CREATE CREATE DATABASE <databaseName>; CREATE DATABASE Naresh_2022_May_9AMto10AM — to create a new table to store all my friends contacts CREATE TABLE <tableName> ( <columnList> ); Std SQL : Every SQL statement should be terminated with a semi colon In SQL Server, ; is optional Best Practice : Always follow the standard easy migration of the code MyfriendsContact —————— FName Gender MobileNo DOB Email Qualification Address Madhu M 78787 12 ghjghj Saritha F 5565 22 jkgkjg Ravi M 555 24 ggkgk Ramya F 67868 14 gjgg MCA ghjghj Praveen M 4334 17 gkjgkjg MCA ghkjgk Sheela F 67fdsf868 14 gkjgkg Mtech ghjkgkg CREATE TABLE Tbl_MyFriendsContact ( FriendName VARCHAR(20), Gender CHAR(1), MobileNo NUMERIC(10,0), DOB DATETIME, EMail VARCHAR(40), Qualification VARCHAR(10) ); <colName> <datatype> [<nullablity> <constraints>] Pascal case notation Best practise : always follow the naming conventions Keywords / reserved words , specify in capital letters Datatypes Alpha Numerical Datatypes ———————— 1. CHAR 2. VARCHAR 3. TEXT 4. NCHAR 5. NVARCHAR 6. NTEXT Numerical Datatypes 0 – 9 ——————– 1. BIT 2. TINYINT 3. SMALLINT 4. INT 5. BIGINT 6. NUMERIC 7. DECIMAL 8. FLOAT 12-dsfdsf-32323 12-FEB-2022 31-FEB-2022 — wrong DateTime datatypes ———————— 1. DATETIME 2. SMALLDATETIME 3. DATE 4. TIME 5. DATETIME2 6. DATETIMEOFFSET Dbo is the Schema What is Schema ? is used to organize the related database objects is the actual container NOTE : whenever a new database is created, automatically dbo schema will be created CREATE SCHEMA <schemaName> Database dbo ( database owner ) Tables — which holds the actual data, Basic unit of storage Views TVFs SP SF Triggers Indexes Sequences . . . Schema2 Tables — which holds the actual data, Basic unit of storage Views TVFs SP SF Triggers Indexes Sequences . . . Schema3 Tables — which holds the actual data, Basic unit of storage Views TVFs SP SF Triggers Indexes Sequences . . . Schema4 Tables — which holds the actual data, Basic unit of storage Views TVFs SP SF Triggers Indexes Sequences . . . CREATE TABLE [SchemaName.]<tableName> ( <columnList> ); dbo schema2 schema3 schema4 Q : in which schema the table will be create, If I do not specify the schemaName? Ans : dbo schema — wrong answer : it will be created under users default schema CREATE TABLE Tbl_MyFriendsContact ( FriendName VARCHAR(20), Gender CHAR(1), MobileNo NUMERIC(10,0), DOB DATETIME, EMail VARCHAR(40), Qualification VARCHAR(10) ); NOTE:- Every user will be mapped with the users default schema Madhu — Schema3 Priya — Schema4 Raghunath — dbo jagadish — — Schema2 Gayatri — dbo Ganesh — Schema3 Madhu conneted — CREATE TABLE T1 — it will be created under Schema3 Jagadish — CREATE TABLE T1 — it will be created under Schema2 Ganesh — CREATE TABLE T1 — it will be created under Schema3 Ganesh — CREATE TABLE schem4.T1 — it will be created under Schema4 Gayatri — CREATE TABLE schema2.T1 — it will be created under Schema2 Gayatri — CREATE TABLE T1 — it will be created under dbo SELECT * FROM Tbl_MyFriendsContact DML commands ————– INSERT command : is used to add a new record / row Form1 ——- INSERT INTO <tableName> VALUES(<ValuesList>); INSERT INTO Tbl_MyFriendsContact VALUES(‘Madhu’,’M’,999,’03/22/1985′,’madhu@gmail.com’,’Mtech’); INSERT INTO Tbl_MyFriendsContact VALUES(‘Saritha’,’F’,888,’05/16/1989′,’saritha@gmail.com’,’MCA’); INSERT INTO Tbl_MyFriendsContact VALUES(‘Ravi’,’M’,777,’08/27/1987′,’Ravi@gmail.com’,’Btech’); Note : the values should correspond to the order of the columns in which they are defined : each value should be separated with a comma separator. : character and DateTime data should be enclosed in single quotes but not numeric data : In which format we need to specify the date values DD/MM/YYYY MM/DD/YYYY Answer:- based on the language setting of the database us_english — MM/DD/YYYY British — DD/MM/YYYY SELECT @@LANGUAGE — us_english there are 2 ways to get structure of the table 1. using system Stored procedure SP_HELP <Tablename> 2. using Keyboard short cut ALT + F1 SP_HELP Tbl_MyFriendsContact INSERT INTO Tbl_MyFriendsContact VALUES(‘Jagadish’,’M’,6612362233,’24/04/1999′,’Jagadish@gmail.com’,’Btech’); — Error date format not accepted INSERT INTO Tbl_MyFriendsContact VALUES(‘Jagadish’,’M’,6612362233,’04/24/1999′,’Jagadish@gmail.com’,’Btech’); — dd/mm/yyyy INSERT INTO Tbl_MyFriendsContact VALUES(‘Deepthi’,’F’,555,’20/07/1999′,’Deepthi@gmail.com’,’Btech’); — Error SELECT * FROM SYS.tables SELECT * FROM Tbl_MyFriendsContact INSERT INTO Tbl_MyFriendsContact VALUES(‘Deepthi’,’F’,666,’07/20/1999′,’Deepthi@gmail.com’,’MCA’) — I want to provide the date value in dd/mm/yyyy INSERT INTO Tbl_MyFriendsContact VALUES(‘Ramya’,’F’,333,’17/04/1997′,’Ramya@gmail.com’,’BTech’); — error –the, we need to change the language setting to British SET LANGUAGE British — will apply on this session only SELECT @@LANGUAGE — British INSERT INTO Tbl_MyFriendsContact VALUES(‘Ramya’,’F’,333,’17/04/1997′,’Ramya@gmail.com’,’BTech’); — No error INSERT INTO Tbl_MyFriendsContact VALUES(‘Kiran’,’M’,233,’25/07/1997′,’Kiran@gmail.com’,’BTech’); — No error INSERT INTO Tbl_MyFriendsContact VALUES(‘Deepak’,’M’,443,’05/23/1997′,’Ramya@gmail.com’,’BTech’); — Error SET LANGUAGE us_english — MM/DD/YYYY INSERT INTO Tbl_MyFriendsContact VALUES(‘Swathi’,’F’,553,’08/15/1997′,’swathi@gmail.com’,’BTech’); — No errror SELECT * FROM Tbl_MyFriendsContact NOTE : SQL Server will stores in this format only. 1985-03-22 00:00:00.000 YYYY-MM-DD HH:MI:SS.nnn Name Gender email Qualification INSERT INTO Tbl_MyFriendsContact VALUES(‘Prabha’,’F’,’prabha@gmail.com’,’MCA’); INSERT INTO Tbl_MyFriendsContact VALUES(‘Prabha’,’F’,NULL,NULL,’prabha@gmail.com’,’MCA’); What is NULL? — Very Very Important it is an unknown value / missing value / Inapplicable Graduation PostGraduation BSc MCA Bcom NULL NULL NULL Prashanth Name Gender dob INSERT INTO Tbl_MyFriendsContact VALUES(‘Prashanth’,’M’,NULL,’05/21/1996′,NULL,NULL); Scenario ——— T1 100 columns 10 columns — i know the data 90 columns — i do not know the data INSERT INTO T1 VALUES(‘asdsad’,’sd’,’dadsd’,NULL,NULL,NULL,NULL,100,NULL,NULL,NULL,’sfras’,NULL,NULL,NULL,NULL,’sdsadsd’,NULL,NULL,NULL,……..) — tedious — leads to error prone Form2 ——- INSERT INTO <tableName>(<colList>) VALUES(<ValuesList>); Name Gender Qualification INSERT INTO Tbl_MyFriendsContact(FriendName,Gender,Qualification) VALUES(‘Srikanth’,’M’,’MCA’); SELECT * FROM Tbl_MyFriendsContact Name Gender Dob INSERT INTO Tbl_MyFriendsContact(Dob,Gender,FriendName) VALUES(’06/17/1998′,’M’,’Naveen’); 1. INSERT INTO …. VALUES a. INSERT INTO TableName VALUES() b. INSERT INTO TableName(colList)) VALUES() 2. SELECT … INTO … FROM 3. INSERT INTO …. SELECT 4. INSERT INTO … EXEC 5. BULK INSERT DML UPDATE command : is used to modify the existing data UPDATE <tablename> SET <colName> = <NewValue> WHERE <Condition> — updates only the specific row which statisfies the condition UPDATE <tablename> SET <colName> = <NewValue> — dangerours, VV careful, — updates all of the rows — to modify the mobileno of Ramya from 333 to 8989 UPDATE Tbl_MyFriendsContact SET MobileNo = 8989 WHERE FriendName = ‘Ramya’ SELECT * FROM Tbl_MyFriendsContact — to modify the mobileno and also email of prashanth UPDATE Tbl_MyFriendsContact SET MobileNo = 4545,EMail= ‘Prashanth@Gmail.com’ WHERE FriendName = ‘Prashanth’ — to modify the mobileNo of Prabha UPDATE Tbl_MyFriendsContact SET MobileNo = 2323 In SQL Server, the default behaviour is AUTOCOMMITTED. it willl automatically commits whenever the the statement is executed no chane of unod the changes Can we default behaviour? Yes SET IMPLICIT_TRANSACTIONS ON — chance of undo or rollback the changes SET IMPLICIT_TRANSACTIONS OFF — No chance of undoing(Rollback) the changes ( default behavior) SET IMPLICIT_TRANSACTIONS ON — to modify the qualification of prashath to Mtech UPDATE Tbl_MyFriendsContact SET Qualification = ‘Mtech’ ROLLBACK SELECT * FROM Tbl_MyFriendsContact UPDATE Tbl_MyFriendsContact SET Qualification = ‘Mtech’ WHERE FriendName = ‘Prashanth’ AND COMMIT ROLLBACK UPDATE Tbl_MyFriendsContact SET Qualification = ‘BTech’ WHERE FriendName = ‘Naveen’ OR FriendName = ‘Srikanth’ SET IMPLICIT_TRANSACTIONS OFF UPDATE Tbl_MyFriendsContact SET Email = ‘Srikanth@gmail.com’ ROLLBACK — to modify the qualification of all my female friends to MTech UPDATE Tbl_MyFriendsContact SET Qualification = ‘Mtech’ WHERE Gender = ‘F’ SELECT * FROM Sys.tables SELECT * FROM Tbl_MyFriendsContact DML commands — to remove the data of ramya DELETE command : is used to remove the existing data DELETE FROM <tableName> WHERE <condition> — removes the specifc data which satifies the condition DELETE FROM <tableName> — VVV careful, dangeruous, removes all / entrie data SELECT * INTO Tbl_MyFriendsContact_backup FROM Tbl_MyFriendsContact SELECT * FROM Tbl_MyFriendsContact_backup SELECT * FROM Tbl_MyFriendsContact — to remove the data of ramya DELETE FROM Tbl_MyFriendsContact WHERE Friendname = ‘Ramya’ — to remove the data of srikanth DELETE FROM Tbl_MyFriendsContact WHERE friendname = ‘Srikanth’ — to remove allof my female friends DELETE FROM Tbl_MyFriendsContact WHERE gender = ‘F’ — autocommitted ROLLBACK SET IMPLICIT_TRANSACTIONS ON — chance of undo the changes — to remove kiran record DELETE FROM Tbl_MyFriendsContact ROLLBACK — undo the change upto the last committed SET IMPLICIT_TRANSACTIONS OFF –Default behaviour is the best I1 COMMIT I2 COMMIT I3 I4 I5 COMMIT U1 COMMIT U2 COMMIT I6 UP3 — went wrong ROLLBACK ROLLBACK TRUNCATE command : is used to remove the existing data : it is only possible to remobve the entire data TRUNCATE TABLE <Tablename> SELECT * FROM Tbl_MyFriendsContact INSERT INTO Tbl_MyFriendsContact SELECT * FROM Tbl_MyFriendsContact_backup COMMIT TRUNCATE TABLE Tbl_MyFriendsContact 10 million rows to remove all the data DELETE — slow, 20 minutes TRUNCATE — Prefer, Fast, less than one minute DELETE can be rollback and TRUNCATE cannot be rollback — True as per the std SQL, ORACLE In Sql server, both behaves as same, based on the IMPLICIT_TRANSACTIONS setting IMPLICIT_TRANSACTIONS is ON : both DELETE and TRUNCATE can be rollback IMPLICIT_TRANSACTIONS is OFF : both DELETE and TRUNCATE cannot be rollback SELECT * FROM Tbl_MyFriendsContact SET IMPLICIT_TRANSACTIONS ON — chance of undo the changes TRUNCATE TABLE Tbl_MyFriendsContact ROLLBACK CREATE TABLE [SchemaName.]<tableName> ( <columnList> ); dbo schema2 schema3 schema4 Q : in which schema the table will be create, If I do not specify the schemaName? Ans:- dbo schema — wrong answer : it will be created under users default schema <colname> <datatype> [ <Nullability> <constraints> ] CREATE TABLE Tbl_MyFriendsContact ( FriendName VARCHAR(20), Gender CHAR(1), MobileNo NUMERIC(10,0), DOB DATETIME, EMail VARCHAR(40), Qualification VARCHAR(10) ); Data Types:- <datatype> determines what type of data we want to store in that column YYYY-MM-DD — Language Neutral format in SQL Server Alpha Numerical Datatypes ———————————– 1. CHAR 2. VARCHAR 3. TEXT 4. NCHAR 5. NVARCHAR 6. NTEXT Numerical Datatypes ————————— 1. BIT 2. TINYINT 3. SMALLINT 4. INT 5. BIGINT 6. NUMERIC 7. DECIMAL 8. FLOAT Alpha Numerical Datatypes ———————————— ASCII character datatypes, Stores the ASCII characters, ASCII character set (256 different characters ) : abcABCDEFG, 012345, &^%$#@!()……. : Assume : it stores only English language characters NOTE:- for each character it will occupy one byte of space 1. CHAR 2. VARCHAR 3. TEXT UNICODE character datatypes, stores the UNICODE characters. : it contains all the characters of all the foreign languages across the globe : it can store any language character NOTE:- for each character, it will occupy two bytes of space 4. NCHAR 5. NVARCHAR 6. NTEXT FriendName CHAR / VARCHAR / TEXT — English Venkat Sarath – 12 bytes FriendName NCHAR / NVARCHAR / NTEXT — Any language Venkat Sarath – 24 bytes 1. CHAR : it is fixed length ASCII character datatype <colName> CHAR(n) : n is the maximum number of characters that we want to store : n can be specified maximum upto 8000 : remaining are filled with spaces FriendName CHAR(10) — ok FriendName CHAR(1000) — ok FriendName CHAR(2000) — ok FriendName CHAR(4000) — ok FriendName CHAR(8000) — ok FriendName CHAR(9000) — Not ok, Error, maximum is 8000 only FriendName CHAR(10) Madhu — 10 bytes Saritha — 10 bytes Chaitanya — 10 bytes Ram — 10 bytes Ravi — 10 bytes Praveen — 10 bytes Om — 10 bytes Madhu Sudhan Rao — Error, 16 characters, but we defined as 10 <columName> CHAR– by default it will treated as 1, same as CHAR(1) 2. VARCHAR : it is variable length ASCII character datatype the size varies based upon the value <colName> VARCHAR(n) : n is the maximum number of characters that we want to store : n can be specified maximum upto 8000 : remaining are not filled with spaces FriendName VARCHAR(10) — ok FriendName VARCHAR(1000) — ok FriendName VARCHAR(2000) — ok FriendName VARCHAR(4000) — ok FriendName VARCHAR(8000) — ok FriendName VARCHAR(8001) — Not ok, Error, maximum is 8000 only FriendName VARCHAR(10) Madhu– 5 bytes Saritha– 7 bytes Chaitanya– 9 bytes Ram– 3 bytes Ravi– 4 bytes Praveen– 7 bytes Om– 2 bytes Madhu Sudhan Rao — Error, 16 characters, but we defined as 10 <colName> VARCHAR– by default it will treated as 1, same as VARCHAR(1) DescriptionOfmyfriend VARCHAR(10000) — Error, maximum is 8000 safsad asds dsajd s.adjsla djlsajdl sajdlsajd saklsalkdasl hdasd asdasd asdas asdas dasdas sadas dasd sdfdsfdsf dsf dsfdsf dsfds asf sfasfadas das DescriptionOfmyfriend VARCHAR(10000) — Error, maximum is 8000 TEXT : it is a variable-length ASCII character datatype : it can store more than 8000 characters and upto 2GB characters <colName> TEXT — can store more than 8000 characters and upto 2GB characters DescriptionOfmyfriend VARCHAR(10000) — Error DescriptionOfmyfriend TEXT — store more than 8000 characters and up to 2GB characters not to use the TEXT datatype anymore outdated / obsolete DescriptionOfmyfriend TEXT — not to use DescriptionOfmyfriend VARCHAR(10000) — error use the MAX keyword along with the VARCHAR datatype DescriptionOfmyfriend VARCHAR(MAX) — behaves the same as TEXT datatype, store more than 8000 characters and up to 2GB characters. Designation VARCHAR(4000) — ok, surprise to others, — degrade in performance ( slow ) Designation VARCHAR(8000) Designation VARCHAR(60) Address VARCHAR(500) ================================ 4. NCHAR: it is fixed length UNICODE character datatype <colName> NCHAR(n) : n is the maximum number of characters that we want to store : n can be specified maximum upto 4000 : remaining are filled with spaces FriendName NCHAR(10) — ok FriendName NCHAR(1000) — ok FriendName NCHAR(2000) — ok FriendName NCHAR(4000) — ok FriendName NCHAR(5000) — Not ok, Error, maximum is 4000 only FriendName NCHAR(8000) — Not ok, Error, maximum is 4000 only FriendName NCHAR(10) Madhu — 20 bytes Saritha — 20 bytes Chaitanya — 20 bytes Ram — 20 bytes Ravi — 20 bytes Praveen — 20 bytes Om — 20 bytes మధు — 4 bytes অনিথা मनोज Madhu Sudan Rao — Error, 16 characters, but we defined it as 10 <colName> NCHAR — by default, it will be treated as 1, same as NCHAR(1) N’madhu’ N’saritha’ N’మధు’ — ?? N’ప్రభాసిని’ — ???? N’मनोज’ Difference between CHAR and NCHAR 5. NVARCHAR : it is variable length UNICODE character datatype the size varies based upon the value <colName> NVARCHAR(n) : n is the maximum number of characters that we want to store : n can be specified maximum upto 4000 : remaining are not filled with spaces FriendName NVARCHAR(10) — ok FriendName NVARCHAR(1000) — ok FriendName NVARCHAR(2000) — ok FriendName NVARCHAR(4000) — ok FriendName NVARCHAR(5000) — Not ok, Error, maximum is 4000 only FriendName NVARCHAR(8000) — Not ok, Error, maximum is 4000 only FriendName NVARCHAR(10) Madhu– 10 bytes Saritha– 14 bytes Chaitanya– 18 bytes Ram– 6 bytes Ravi– 8 bytes Praveen– 14 bytes Om– 4 bytes Madhu sudhan rao — Error, 16 characters, but we defined as 10 <colName> NVARCHAR– by default it will treated as 1, same as NVARCHAR(1) DescriptionOfmyfriend NVARCHAR(6000) — Error, maximum is 4000 Dummy Data on systems with systemd versions that support it, MariaDB uses this feature to extend the startup timeout during certain startup processes that can run long. Therefore, if you are using systemd 236 or later dummy data DescriptionOfmyfriend NVARCHAR(6000) — Error, maximum is 4000 NTEXT : it is a variable length UNICODE character datatype : it can store more than 4000 characters and upto 1GB characters <colName> NTEXT — can store more than 4000 characters and upto 1GB characters DescriptionOfmyfriend NVARCHAR(10000) — Error DescriptionOfmyfriend NTEXT — store more than 4000 characters and upto 1GB characters not to use the NTEXT datatype anymore outdated / obsolete DescriptionOfmyfriend NTEXT — not to use DescriptionOfmyfriend NVARCHAR(10000) — error use the MAX keyword along with the NVARCHAR datatype DescriptionOfmyfriend NVARCHAR(MAX) — behaves same as NTEXT datatype, store more than 4000 characters and upto 1GB characters Non-Integer datatypes —————————— Non-Integer datatypes : stores the non integer values, numbers with decimals 245.78, 987.532, 12.37 6. NUMERIC 7. DECIMAL 8. FLOAT Integer datatypes ———————– Integer datatypes: stores the integer values, numbers without decimals 245, 987, 12.37 1. BIT : 1 bit of space, it can store only 0 and 1 apart from NULL 2. TINYINT : 1 byte of space, can store only +ve values, Range : 0 to 255 [Interview Question] 3. SMALLINT : 2 bytes of space, can store both +ve and -ve values, Range : -32768 to +32767 4. INT : 4 bytes of space, can store both +ve and -ve values, Range : -2,14,74,83,648 to +2,14,74,83,647 5. BIGINT : 8 bytes of space, can store both +ve and -ve values, Range : -____ to +_________ They differ in 2 ways a. amount of storage space that it occupies b. Range of values that it ca accept ——– VVV Imp <Column Name> BIT 0 — Ok 1 — Ok 2 — Ok, but it will be stored as 1 67 — Ok, but it will be stored as 1 -200 — Ok, but it will be stored as 1 345 — Ok, but it will be stored as 1 NOTE:- Any non-zero value will stored as 1 Zero will be stored zero Only two possible values Gender BIT 0 — F 1 — M IsActive BIT 1 — Yes 0 — No < Column Name > TINYINT 0 — ok 1 — ok 100 — ok 200 — ok 255 — ok 256 — Not ok, Error, because max is 255 only -10 — Not ok, Error, because minimum is 0 < Column Name > SMALLINT 0 — ok 1 — ok 2000 — ok 10525 — ok 30000 — ok -2000 — ok 32000 — ok 33000 — Not ok, Error, because the max 32767 Arithmetic overflow salary SMALLINT 25000 — ok 30000 — ok 35000 — Not general formula ————— Min Value ———- 256 to the power of n bytes – ————————— 2 Max Value ———- 256 to the power of n bytes + ————————— – 1 2 SMALLINT ( 2 bytes ) Min Value : – 32,768 Max Value : + 32,767 Min Value ———- 256 to the power of 2 – ————————— 2 Max Value ———- 256 to the power of 2 + ————————— – 1 2 INT ( 4 bytes ) Min Value : – 2,14,74,83,648 Max Value : + 2,14,74,83,647 Min Value ———- 256 to the power of 4 – ————————— 2 Max Value ———- 256 to the power of 4 + ————————— – 1 2 Age BIGINT / SMALLINT / TINYINT / INT Age TINYINT Non-Integer datatypes : stores the non integer values, numbers with decimals 245.78, 987.532, 12.37 6. NUMERIC 7. DECIMAL 8. FLOAT 3427.739 <colName> NUMERIC(P,S) : P is precision, the total number of digits including the decimal part : S is scale, the number of digits after the decimal point : P – S : the maximum number of digits before the decimal point <colName> NUMERIC(7,3) 7 – 3 = 4 3427.739 23.768 — ok 256.269 — ok -2.764 — ok 23.54734345546554646 — ok, will be stored as 23.547 -4562.216732432434 — ok, will be stored as 4562.216 2.567546435353253 — ok, will be stored as 2.567 2345.768 — ok 23146.675 — Not , Error ____. + or – 9999.999 <colname> NUMERIC(5,2) 5 – 2 = 3 24.43 — ok 124.67 — ok 127.6897907709 — ok, 127.68 1.3456436 — ok, 1.34 678.43 — ok 2372.67 — Not Ok 21356.897 — Not ok max : 999.99 23.6543 1.3246 243.5678 21.3214 432.3456 2.4567 127.8932 <colName> NUMERIC(7,4) the value of P can be max upto 38 <colName> NUMERIC(38,0) 32432434343432432432434324324324324324324 <colName> NUMERIC(38,38) 0.45345345345345345435 0.565465365465465465465465456546546 0.32532432432434343424324 0 and 1 <colName> NUMERIC(38,35) ___.463454545345345345345345 1 comment 0 FacebookTwitterPinterestEmail mukilan previous post Where To Start Microsoft Azure next post Power Shell 1 comment A WordPress Commenter December 16, 2022 - 7:40 pm Hi, this is a comment. To get started with moderating, editing, and deleting comments, please visit the Comments screen in the dashboard. Commenter avatars come from Gravatar. Comments are closed.
