SELECT, UPDATE, DELETE & Comments code in SQL

SELECT all rows and columns from a table

Syntax:

sql">SELECT *

FROM table_name

Using the asterisk operator * serves as a shortcut for selecting all the columns in the table. All rows will also be selected because this SELECT statement does not have a WHERE clause, to specify any filtering criteria.

This would also work the same way if you added an alias to the table, for instance e in this case:

sql">SELECT *

FROM Employees AS e

Or if you wanted to select all from a specific table you can use the alias + ” .* “:

sql">SELECT e.*, d.DepartmentName

FROM Employees AS e

INNER JOIN Department AS d

ON e.DepartmentID = d.DepartmentID

Database objects may also be accessed using fully qualified names:

sql">SELECT * FROM [server_name].[database_name].[schema_name].[table_name]

 

This is not necessarily recommended, as changing the server and/or database names would cause the queries using fully-qualified names to no longer execute due to invalid object names.

Note that the fields before table_name can be omitted in many cases if the queries are executed on a single server, database and schema, respectively. However, it is common for a database to have multiple schema, and in these cases the schema name should not be omitted when possible.

Warning: Using SELECT * in production code or stored procedures can lead to problems later on (as new columns are added to the table, or if columns are rearranged in the table), especially if your code makes simple assumptions about the order of columns, or number of columns returned. So it’s safer to always explicitly specify column names in SELECT statements for production code.

sql">SELECT col1, col2, col3

FROM table_name

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.

DELETE All Rows

sql">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:

sql">TRUNCATE TABLE HelloWords

Difference with DELETE operation are several:

1. Truncate operation doesn’t store in transaction log file
2. If exists 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 VIEW
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 encountered:

sql">-- This is a comment

SELECT *

FROM 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.

sql">/* This is

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 happen 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

sql">/*

SELECT *

FROM CommentTable

WHERE Comment = '/*'

*/

The slash star 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

sql">/*

SELECT *

FROM CommentTable

WHERE Comment = '/*'

*/ */