Saturday, September 14, 2019

CHAPTER 4: Microsoft SQL Sever

Section 1.8: UPDATE All Rows


A simple form of updating is incrementing all the values in a given field of the table. In order to do so, we need to define the field and the increment value

The following is an example that increments the Score field by 1 (in all rows):

UPDATE Scores
SET score = score + 1

This can be dangerous since you can corrupt your data if you accidentally make an UPDATE for a specific Row with an UPDATE for All rows in the table.

Section 1.9: TRUNCATE TABLE



TRUNCATE TABLE Helloworlds


This code will delete all the date from the table helloworlds. Truncate table is almost similar to Delete from Table code. The difference is that you ca not use where clauses with Truncate. Truncate table is considered better than delete bacause it uses less transaction log spaces.

Note that if an identity column exists, it is reset to the initial seed value (for example, auto-incremented ID will restart from 1). This lead to inconsistency if the identity columns is used a foreign key in another table.

Section 1.10: Retrieve Basic Server Information

SELECT @@VERSION

Returns the version of MS SQL Server running on the instance.

SELECT @@SERVERNAME

Returns the name of the MS SQL Server instance.

SELECT @@SERVICENAME

Returns the name of the Windows service MS SQL Server is running as.

SELECT serverproperty ('ComputerNamePhysicalNetBIOS') ;

Return the physical name of the machine where SQL Server is running. Useful to identify the node in a failover cluster.

SELECT * FROM fn_virtualservernodes();

In a failover cluster returns every node where SQL Server can run on. It returns nothing if not a cluster.

Section 1.11: Create new table and insert records from old table

SELECT * INTO NewTable FROM OldTable

Creates a new table with structure of old table and inserts all rows into the new table

Some Restrictions


     1. You cannot specify a table variable or table-valued parameter as the new table.
     2. You cannot use SELECT...INTO to create a partitioned table, even when the source table partitioned. SELECT...INTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO...SELECT FROM statement.
     3.Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. If these objects are required, you can create them after executing the SELECT...INTO statement.
     4.Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order to the column in the new table. If this property is required in the new table, alter the column definition after executing the SELECT...INTO statement to include this property.
     5. When a computer column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computer at the time SELECT...INTO was executed.

No comments:

Post a Comment