Check Data-length of the all the data in the column in Sql Server | query to check size of all the data present in the column

Taking an Example , Suppose we want to check the data-size of all the data present in a column for a Employee table :

Query :

SELECT DATALENGTH(CUSTOMER_ID) AS EMP_ID_LENGTH ,
       DATALENGTH(EMPLOYEE_NAME) AS EMPLOYEE_LENGTH,
       *
FROM EMPLOYEE_TABLE

changing column size in sql | Query to increase or decrease column length in sql server

Changing Column Size :

Suppose you want to change the size of a column having datatype as NVARCHAR of length 100.
We will increase it length to 200.

Query :

ALTER TABLE CUSTOMER_TABLE
ALTER COLUMN CUSTOMERNAME nvarchar(200)

[Resolved] ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified..Column '' cannot be added to non-empty table '' because it does not satisfy these conditions. | Msg 4901, Level 16, State 1 | SQL alter table error

Hi in this post i will show how to resolve this below error.

Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column '' cannot be added to non-empty table '' because it does not satisfy these conditions.

1. Creating a scenario where you can get this type of issues.

create table empDetails

(

nameid int identity primary key,

name varchar(1000),

addres varchar(1000),

mobile numeric

)

insert into empDetails(name,addres,mobile) values ('chandan','india',123456789)

select * from empdetails

2. Now i will add a column to this table  

alter table empdetails

add [deptid] [int] NOT NULL 

Executing this wil give me the error as

To resolve this we will add default value '0' to the column.

Thus the error gets resolved.