The maximum value for an Bigint in SQL Server is: -9223372036854775808 through 9223372036854775807 And the byte size is 8 bytes. Here is the proof (Thanks to BJ) [cc lang=”sql”] DECLARE @max bigint, @min bigint SELECT @max = 127, @min = 1 WHILE @min = 1 BEGIN BEGIN TRY SELECT @max = @max * 2 + 1 END TRY BEGIN CATCH BEGIN TRY SET @min = -1 – @max END TRY BEGIN CATCH SET @min = 0 END CATCH END CATCH END SELECT @min , @max [/cc] other maximum values: Int: -2147483648 through 2147483647 (4 bytes) SmallInt: -32768 through 32767 (2 bytes) TinyInt: 0 through 255 (1 byte)
Continue reading ...
The maximum values for an integer in SQL Server are: -2147483648 through 2147483647 And the byte size is 4 bytes. Other maximum values: BigInt: -9223372036854775808 through 9223372036854775807 (8 bytes) SmallInt: -32768 through 32767 (2 bytes) TinyInt: 0 through 255 (1 byte) Here is the proof (thanks to BJ): [cc lang=”sql”] DECLARE @max int, @min int SELECT @max = 127, @min = 1 WHILE @min = 1 BEGIN BEGIN TRY SELECT @max = @max * 2 + 1 END TRY BEGIN CATCH BEGIN TRY SET @min = -1 – @max END TRY BEGIN CATCH SET @min = 0 END CATCH END CATCH END SELECT @min , @max [/cc]
Continue reading ...
The SQL Server case statement is a conditional statement that returns a single value based on the evaluation of a statement. Case statements can be used in a SELECT list, WHERE clause and even an ORDER BY clause. Case statement can also be nested. This provides a lot of pliability for evaluating multiple expressions. We’ll start by walking through a simple case statement. There are two ways to formulate a case statement. One is to present the column or variable to evaluate after the word case. It should be noted that the else portion of a case statement is completely optional. Basic Equality Evaluation [cc lang=”sql”] DECLARE @Season varchar(100) SET @Season = ‘Summer’ DECLARE @Climate varchar(50) SET @Climate = CASE @Season WHEN ‘Winter’ THEN ‘Cold’ WHEN ‘Spring’ THEN ‘Mild’ WHEN ‘Summer’ THEN ‘Hot’ WHEN ‘Fall’ THEN ‘Cool’ ELSE ‘Unknown’ END SELECT @Climate [/cc] That syntax works well when evaluating whether single variable is equal to a value. If we want to add additional criteria then we have to formulate the statement a different way. Basic Multi-Evaluation [cc lang=”sql”] DECLARE @Temperature int SET @Temperature = 64 DECLARE @Climate varchar(50) SET @Climate = CASE WHEN @Temperature < 0 THEN 'Way Too Cold' WHEN @Temperature BETWEEN 0 AND 50 THEN 'Cold' WHEN @Temperature BETWEEN 51 AND 60 THEN 'Cool' WHEN @Temperature BETWEEN 61 AND 70 THEN 'Mild' WHEN @Temperature BETWEEN 71 AND 80 THEN 'Warm' WHEN @Temperature BETWEEN 81 AND 100 THEN 'Hot' WHEN @Temperature > 100 THEN ‘Way Too Hot’ ELSE ‘Unknown’ […]
Continue reading ...
The Alter Column statement can modify the data type and the Nullable attribute of a column. The syntax is the same for SQL Server 2005 and SQL Server 2008 except 2008 allows the sparse attribute to be changed. For the example below, we will begin by creating a sample table, then we will modify the columns. [cc lang=”sql”] CREATE TABLE dbo.Employee ( EmployeeID INT IDENTITY (1,1) NOT NULL ,FirstName VARCHAR(50) NULL ,MiddleName VARCHAR(50) NULL ,LastName VARCHAR(50) NULL ,DateHired datetime NOT NULL ) — Change the datatype to support 100 characters and make NOT NULL ALTER TABLE dbo.Employee ALTER COLUMN FirstName VARCHAR(100) NOT NULL — Change datatype and allow NULLs for DateHired ALTER TABLE dbo.Employee ALTER COLUMN DateHired SMALLDATETIME NULL — Set SPARSE columns for Middle Name (sql server 2008 only) ALTER TABLE dbo.Employee ALTER COLUMN MiddleName VARCHAR(100) SPARSE NULL [/cc] Columns can be altered in place using alter column statement. Only the datatype, sparse attribute (2008) and the Nullable attribute of a column can be changed. You cannot add a NOT NULL specification if NULL values exist. In order to change or add a default value of a column, you need to use Add/Drop Constraint. In order to rename a column, you must use sp_rename.
Continue reading ...
Examples on how to removed duplicate rows from your table even if you do not have a primary key.
Continue reading ...
