DAY-6
Understanding column
properties
Each column
stores very specific information. You can configure certain properties for a
given column based on the selected data type, which is a property itself. The
most common property is Allow Nulls. This simply means that you can insert a
row into the table without supplying a value.
NULL is a
special value in the database world. It does not mean empty; rather, it
rep-resents the absence of a value and is different from an empty string.
The second
most common property is Is Identity.It is second because it is only available
for most numeric data types.When you set this value for a column, SQL Server
automatically generates a number as each row is inserted. You can customize or
configure the starting point and how the number will increment using the
properties that are available.
Creating tables
Creating
tables with SSMS is much easier than with T-SQL. The biggest disadvantage to
using SSMS, though, is not having very portable code. Once T-SQL is written, it
can be saved and executed against the same instance or another instance of SQL
Server without your having to re-create the script, but this is not the case
with SSMS.If you use the table designer to create a table, you are required to
perform the same steps on another instance of SQL Server if you want to
re-create the table. Nevertheless, it is worth knowing and understanding the
steps. You should learn how to create the table using T-SQL not only because
most things on SQL Server are accomplished using T-SQL, but also because it
allows for easy portability.
Create a table using SSMS
1. With SSMS open, expand the
Databases folder.
2. Expand the SBSChp4SSMS database.
3. Expand the Security folder.
4. Right-click the Schemas folder.
5. Select New Schema from the menu.
6. In the Schema – New dialog box,
type Marketing in the Schema Name text box.
7. Type dbo in the Schema Owner text
box.
8. Click OK.
9. Right-click the Tables folder.
The table designer opens.
10. Select New Table from the menu.
11. In the Column Name column, type
AddressID.
12. Click in the Data Type column and
select int from the drop-down list.
13. In the Column Properties tab that
is located at the bottom of the table designer window, scroll down to and
expand Identity Specification.
14. Set the Is Identity property to
Yes.
15. In the next row of the column
list, type StreetAddress in the Column Name column.
16. Click in the Data Type column and
select varchar from the drop-down list, changing the character string length
to 125.
17. Uncheck the box under the Allow
Nulls column.
18. Repeat steps 16–18 for each
additional column, setting the property according to the specifications.
19. Select View | Properties. The
Properties window opens.
20. Locate and click in the Schema
property.Select HumanResources from the drop-down list.
21. Locate and expand the Regular
Data Space Specification property. In the Filegroup or Partition Scheme Name
property, ensure that SBSSSMSGroup1 is selected.
22. Click the Save button.
23. Type Address in the text box in
the Choose Name window. And click OK.
Add a computed
column using SSMS
1.
Ensure that SSMS is open and you are
connected to your server.
2.
Expand the Databases folder.
3.
Expand the Thigalla database.
4.
Expand the Tables folder.
5.
Right-click the Flowers .Employee table and select Design.
6.
Under Gender, in the next row, type FullName and
press the Tab key.
7. In the Column Properties section
at the bottom of the table designer screen, locate and expand the Computed
Column Specification property.
8. In the Formula property, type LastName+',
'+FirstName click Save.
Thanks
& Regards
Srikanth Goud- Virtualization Administrator 
No comments:
Post a Comment