Alter database table columns without dropping table Part 67 — Transcript

Full transcript

1. 0:00is part 67 of sql server tutorial in
2. 0:03this video we'll discuss altering
3. 0:04database table columns without dropping
4. 0:07the table
5. 0:08let's understand this with an example
6. 0:10we'll be using this table tbl employee
7. 0:12for this demo notice that this table has
8. 0:14got id name gender and salary columns
9. 0:18and i have used this create table script
10. 0:20to create the table tbl employee and if
11. 0:23you notice the salary column n where
12. 0:25care is the data type and this is the
13. 0:27sql script to populate it with some
14. 0:29sample data if you need the sql script i
15. 0:31will have it available on my blog now
16. 0:33let's say based on this table we want to
17. 0:36write a query which is going to list the
18. 0:38total salaries of employees grouped by
19. 0:41gender so we want the output to be like
20. 0:43this so let's write a query for that
21. 0:47so select
22. 0:49gender
23. 0:50and we want sum of salary
24. 0:55and let's give it as amalias
25. 0:58total
26. 0:59from table tbl employee
27. 1:04and we want to group the salaries
28. 1:07by
29. 1:08gender
30. 1:09and keep in mind the salary column data
31. 1:12type is nvecca and let's try to execute
32. 1:14this and see what's going to happen look
33. 1:15at the error message it states operand
34. 1:18data type and where care is invalid for
35. 1:20some operator so basically this error
36. 1:23message is stating we cannot use the
37. 1:26salary column
38. 1:27with some aggregate function that's
39. 1:30basically because its data type is
40. 1:31n-wirecare so we need to change the data
41. 1:34type of this column from n-wirecare to
42. 1:37integer and one way to do that within
43. 1:40the object explorer right click on the
44. 1:42table tbl employee click design
45. 1:46and notice the data type of the salary
46. 1:48column it's n vacca let's change it to
47. 1:51integer
48. 1:53and let's try to save it and see what's
49. 1:54going to happen look at that i get a
50. 1:57warning saving changes is not permitted
51. 2:00basically this is telling we need to
52. 2:02drop and recreate the table and if you
53. 2:04remember this table has already got some
54. 2:07data as well so if we drop and recreate
55. 2:09it we are going to lose that data so how
56. 2:12do we get rid of this warning message
57. 2:14okay
58. 2:15basically there are two ways
59. 2:18option one use a sql query to alter the
60. 2:21column okay so instead of using the sql
61. 2:24server designer we can simply use a sql
62. 2:27query
63. 2:29so let's write the sql query
64. 2:33alt
65. 2:34table tbl employee
66. 2:38and within that table we want to alter
67. 2:40the
68. 2:41column
69. 2:44salary and we want to change the data
70. 2:46type to integer
71. 2:48so let's execute that and let's now run
72. 2:51this query and see if it works as
73. 2:53expected look at that it works as
74. 2:54expected so this is one option and the
75. 2:57second option is basically to disable
76. 3:01this option within sql server so prevent
77. 3:04saving changes that require table
78. 3:06recreation okay this option is on by
79. 3:10default within sql server all we need to
80. 3:12do is disable that option and to do that
81. 3:14go back to sql server within tools
82. 3:17select options
83. 3:19and then expand designers and under that
84. 3:22table and database designers and uncheck
85. 3:24this checkbox prevent saving changes
86. 3:26that require table recreation
87. 3:29click ok
88. 3:30now let's try to
89. 3:33design the table now at the moment
90. 3:35salary data type is integer maybe let's
91. 3:37change it to something like maybe small
92. 3:40integer
93. 3:42let's save the changes and see work it
94. 3:44works look at that it gives a warning
95. 3:46but that's fine once i click yes look at
96. 3:49what's going to happen
97. 3:52the table the changes are saved
98. 3:56and as you might expect when we execute
99. 3:57this query we get the same output
100. 4:01that's it for today thank you for
101. 4:02listening have a great day