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 ...
You can add a default value to a new OR existing column in SQL Server without dropping it. Specifying a default constraint should not cause any blocking. Only when setting a column to NULL OR NOT NULL will cause blocking. But to set the NULLABLE attribute, you need to use ALTER COLUMN instead. When adding this default value, it’s always best to specify the constraint name. If you don’t the system will generate a random constraint name instead. This can wreak havoc because this default constraint name will not be the same name generated on your dev / stage / production systems. This is why it’s always best to specify the name. Add Default Value to Existing Column [cc lang=”sql”] — Add default to existing column DateOfHire: ALTER TABLE [dbo].[Employees] ADD CONSTRAINT DF_Employees_DateOfHire DEFAULT (GETDATE()) FOR [DateOfHire] — Add default value to existing column IsTerminated ALTER TABLE [dbo].[Employees] ADD CONSTRAINT DF_Employees_IsTerminated DEFAULT (0) FOR [IsTerminated] [/cc] Add New Column with Default Value [cc lang=”sql”] — Add new column DateOfHire with default ALTER TABLE Employees ADD DateOfHire datetime CONSTRAINT DF_Employees_DateOfHire DEFAULT (GETDATE()) — Add new column IsTerminated with default (no constraint name specified NOT RECOMMENDED) ALTER TABLE Employees ADD IsTerminated datetime DEFAULT (0) [/cc] Add Default Value with Create Table [cc lang=”sql”] CREATE TABLE [dbo].[Employees] ( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NULL, [LastName] [varchar](50) NULL, [SSN] [varchar](9) NULL, — Add default of zero [IsTerminated] [bit] NOT NULL CONSTRAINT DF_Employees_IsTerminated DEFAULT (0) , — Add default of getdate() [DateAdded] [datetime] […]
Continue reading ...
Stub created to discuss SQL Server Hosting companies. Discuss hosts through link below.
Continue reading ...
To Add a foreign key to a column in an existing table, use ALTER TABLE ADD CONSTRAINT [cc lang=”sql”] ALTER TABLE dbo.President_Lookup ADD CONSTRAINT fk_PresidentID FOREIGN KEY (PresidentID) REFERENCES dbo.Presidents (PresidentID) [/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 ...
