Hello Readers, Hope all is good and enjoying your day. Today, I will be providing information on How to manage data in SQL Server using views? So, let’s get started. Let’s assume that you are working as Data Engineer or Data Analyst. Rather I can say you are working in the data field. You have a daily automated task of creating SQL query to give the most updated online transactions that the company had. Your query generates an output of the table in the format of 10 columns and 1 million rows. This output you have to transfer to the data manager of the company for further visualization and has to run every day. Now the query you have written is 120 lines of SQL code. You have created the code and you run the code every day to get the output. This is where “views” come into play. Instead of running the query manually, you can actually save that code in the SQL server using the views. This is the best way to save the code in the view. Now, let’s see how we can create the views, use them and modify them, and finally delete them.
How to create the views?
--Creating views format CREATE VIEW view_name AS SELECT column_names FROM table_name WHERE condition SQL Views Example: In the below example, we are creating a SQL query to get sales for last week. --Creating SQL query to get last weeks sales from Sales table CREATE VIEW dbo.get_last_weeks_sales AS SELECT Product, Date, TotalSales FROM sales WHERE Date between DATEADD(DAY,-7,GETDATE()) and CAST(GETDATE() as DATE) Now, we have seen the example of how to create a view. Now this view will be saved in the SQL Server. The view will be found in Database -> Views , when you expand the views folder, we can see all the views created by the user.
2. Modify the existing views: Now let’s say that you have to make some changes to the view. There is a command called ALTER VIEW. We can use that command to change the views. --Alter or modify the existing view ALTER VIEW dbo.view_name AS SELECT statement --Example of modifying a view ALTER VIEW dbo.get_last_weeks_sales AS SELECT statement --The below statement forces all data modification statements --to follow the criteria set within select_statement. --This is optional statement WITH CHECK OPTION In this way, we can create modify and alter the views.
3. Benefits of the views: a. SQL views are the best way to create and manage the queries within SQL server. b. This gives the best management practice of the different query outputs. c. We can pass the parameters in the views. These parameters can take input from the users while we are running the query. The only drawback of the views is that we cannot declare a variable inside the view, but we can pass the input from the user when he wants to run the query. If we need to declare the variable, we need to use stored procedures or functions in the SQL server. d. Don't need to share your code with external users. When you are dealing with external users who just want to see the outcome of the query, they can use the view that you created and you can manage the permission who can see the views and also who can run the views. This gives a layer of protection to your data.
This is the end of the blog. Hope you like this blog. If you like the blog, please flow me for more interesting content on SQL server, Supply chain, Optimization, and Logistics industry. Thank you!