Saturday, August 31, 2019

CHAPTER 3: Microsoft SQL Sever

Section 1.3: UPDATE Specific Row

UPDATE HelloWorlds

SET HelloWOrld = 'HELLO WORLD!!!'
WHERE Id = 5

The above code updates the value of the field "HelloWorld" with HELLO WORLD!!!" for the record where "Id = 5" in HelloWOrlds table.

Note:In an update statement,It is advised to use a "where" clause to avoid updating the whole table unless and until your requirement is different.


Section 1.4: DELETE All Rows

DELETE

FROM Helloworlds

This will delete all the data from the table. The table will contain no rows after you run this code Unlike DROP TABLE, this preserves the table itself and its structure and you can continue to insert new rows into that table.

Another way to delete all rows in table is truncate it, as follow:

TRUNCATE TABLE Hellowords


Difference with DELETE operation are several:

     1.Truncate operation doesn't store in transaction log file
     2.If exist IDENTITY field, this will be reset
     3.TRUNCATE can be applied on whole table and no on part of it (instead with DELETE command you can associate a WHERE clause)


Restrictions Of TRUNCATE 
     
     1. Cannot TRUNCATE a table if there is a FOREIGN KEY reference
     2. If the table is participated in an INDEXED VEIW
     3. If the table is published by using TRANSACTIONAL REPLICATION OR MERGE REPLICATION
     4.It will not fire any TRIGGER defined in the table


Section 1.5: Comments in code

Transact-SQL supports two forms of comment writing. Comments are ignored by the database engine, and are meant for people to read.


Comments are preceded by -- and are ignored until a new line is encounterd:

-- This is a comment
SELECT *
FRON MyTable -- This is another comment
WHERE Id = 1;

Slash star comments begin with /* and end with */. All text between those delimiters is considered as a comment block.

/* This is a multi-line comment block. */
SELECT Id =1. [Message] = 'First row'
UNION ALL
SELECT 2, 'Second row'
/* This is a one liner */
SELECT 'More';

Slash star comments have the advantage of keeping the comment usable if the SQL Statement loses new line characters. This can hammpen when SQL is captured during troubleshooting.

Slash star comments can be nested and a starting /* inside a slash star comment needs to be ended with a */ to be valid. The following code will result in an error

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/

The slash even though inside the quote is considered as the start of a comment. Hence it needs to be ended with another closing star slash. The correct way would be

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/   */


Section 1.6: PRINT 

Display a message to the output console. Using SQL Server Management Studio, this will be displayed in the messages tab, rather than the results tab:


PRINT 'Hello World!' ;
   
Section 1.7: Select rows that match a condition

Generally, the syntax is:


SELECT <column names>
FROM <table name>
WHERE <condition>

For example:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith'

Conditions can be complex:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith AND (City = 'New York' or City = 'Los Angeles')

No comments:

Post a Comment