Your explanation is really good and would like to see more videos. My primary source of learning on SQL Server, also for preparing the exam Great course Venkat. I am bowled over and constantly referring to Pragim YouTube videos as they are so clear and amazingly perfect in the way they are produced at a perfect pace and with clear and definitive examples on SSMS and accompanying integrated notes.
If you wish to receive email alerts when new articles, videos or interview questions are posted on PragimTech. About Instructor. Details 5 Stars. SQl Courses post. SQL post. World Best Course post. SQL Course post. Sql post.
Just Great post. The Unstoppable post. Architect post. Excellent post. Really informative post. Excellent Teacher post. Learn SQL post. You ll see an icon as shown in the screen below. Double-click on that icon to open it. Since our requirement is to import the text file, drag a d drop the Flat File Source fro u the tool box to the data flow tab screen.
Again double click on the Flat File Source task it will pop up a window which has the connection that we had set up at the b initial stage as shown in the screen below. You can click on the Preview button to confirm your structure.
Step 9 — Now we need to set the destination flow i. We need to map the source and the destination in order to tell SSIS e how the mapping works. So click on the Flat file Source and drag the green arrow. Notice that this also allows you to keep null values. In our example, we are going to create a new table. But if you have a table already created, you can select it from the table drop -down menu available.
A table has been created in the database already for this example with the structure below. This table name can be seen in the list as shown in the screen below. You should see e a screen similar to the one below. We should see each. And you can see the data that has been imported from the Text file to the Database. This task is quite easy to perform and almost used in every SSIS g package that will be created.
In order to follow the steps on how to configure the w SQL DB configuration check my previous chapter as stated above. To do the configuration, first drag the green w. It will open the screen below for configuration. Check the download section to see the excel sheet which is created with this project. What is a Trigger? Creating a Trigger Under the Hood Nesting Triggers What is a View? Creating and Using a View Advantages of Views Index Basics Indexes in SQL Server Guidelines for Indexing Selectivity Creating an Index Index Options Maintaining Indexes Rebuilding Indexes Statistics Index Tuning Wizard Graphical Query Plan Query Optimizer.
Query Optimizer Cost Limit Graphical Execution General Query Guidelines SQL Profiler. SQL Profiler Using the SQL Profiler Database Locking Locking Levels Lock Types If you have multiple environments, once again you can run your script against each environment.
Once you get used to writing and running scripts, you will probably find it quicker than entering data directly into the table. Database Driven Websites When you create a database driven website, you actually write SQL scripts to insert data into the database. You also use SQL scripts to read data, update data, and delete data. Tip: Before you open Query Analyzer, use Enterprise Manager to navigate to the database you'd like to work with. That way, Query Analyzer will open using that database.
The Object Browser displays to the left of your workspace. You can use this interface to create database objects such as databases, tables, views etc , insert data into a database table, select data, update data, delete data. The following screen shot shows an example of using a SQL 'select' statement to select data from a database:. When I started creating the above example, I forgot the name of the table that I wanted to select data from. That wasn't a problem. All I needed to do was navigate through the Object Browser until I saw the names of the tables in the left pane.
When I saw the table I needed, I simply clicked and dragged it onto the workspace area and releasing the mouse in the right spot. The above 'select' statement is an example of a SQL query. Apart from the occasional example, SQL queries are outside the scope of this tutorial. Database Administration Tasks Most of the database administration tasks that can be performed in Enterprise Manager through the user interface can be performed programatically in Query Analyzer.
This tutorial concentrates on the Enterprise Manager method, mainly because it's usually a lot easier for new users to get their head around. Once you become more familiar with SQL Server, you may find yourself using Query Analyzer to perform many of the tasks that you started out doing in Enterprise Manager. A view consists of a SELECT statement, and when you run the view, you see the results of it like you would when opening a table.
Some people like to think of a view as a virtual table. This is because a view can pull together data from multiple tables, as well as aggregate data, and present it as though it is a single table. Benefits of Views A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database but not necessarily all of the data. Views can do the following:. Join columns from multiple tables and present them as though they are part of a single table Present aggregate information such as the results of the COUNT function.
They can assist programmers and administrators greatly in working with the database configuration and its data. A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database under the "Stored Procedures" node. Programmers and administrators can execute stored procedures either from the Query Analyzer or from within an application as required.
Transact-SQL is a relatively easy language to learn and I highly recommend becoming familiar with it. Benefits of Stored Procedures Here are some key benefits in using stored procedures:. Faster execution: Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. Running the code on the SQL Server as a stored procedure eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.
Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way. The following code creates a stored procedure called "MyStoredProcedure":. For example, to run the above stored procedure, type the following:.
Many of the tasks you can perform via Enterprise Manager can be done via a system stored procedure. For example, some of the things you can do with system stored procedures include:. SQL Server has a number of security features that assist database administrators in maintaining their database in a secure way. You can see the areas of security by expanding the "Security" folder within Enterprise Manager.
Typically, you will have a database administrator who has access to everything. Then you will have users with varying levels of access, depending on the tasks they're allowed to perform. Doing this can prevent inexperienced users from wreaking havoc on your database environment. Imagine if one morning you came to work, only to find that someone had accidentally deleted your main database!
If you have many users, you can assign them a role. Roles enable you to assign the same access rights across many users. Instead of assigning permissions against an individual user or 'login' , you assign them against a role.
Server Roles Server roles are available for various database administration tasks. Not everyone should be assigned to a server role. In fact, only advanced users such as database administrators should be assigned a server role. As you can see, some of these roles allow very specific tasks to be performed. If you don't have many technical users, it's likely that you'll only use one or two of these roles including sysadmin. This can be useful if you need to perform distributed queries query a remote database.
Setting up a linked server is quite straight forward in Enterprise Manager, all you need is details of the remote server, and the database that you need to query. You can also configure options in the other two tabs depending on your requirements. Remote Servers The Remote Servers option allows you to execute a stored procedure on another instance of SQL Server without establishing another connection.
The Remote Servers option is only provided for backwards compatibility. If you need to execute stored procedures against a remote server, use a linked server.
To Create a New Login 1. Complete the Login Properties in the "General" tab by providing a name for the login, choosing the Authentication method providing a password if you choose "SQL Server Authentication" , and selecting the database to use as a default. If you don't choose language, it will use. Click the "Database Access" tab to specify which databases this login is allowed to access.
By default, the login will be assigned to the "Public" role, which provides the login with basic access. If the login needs more access in one or more databases, it can be assigned to another role with greater privileges. Note that these roles are "Database Roles" and are different to the Server Roles in the previous tab.
Database Roles are created within each database and specify what the login can do within that database. Enables you to build complex DTS packages containing workflow and event-driven logic. In this example, we are importing data from a Microsoft Access database, so we use the "Microsoft Access" option. We also specify the file name of the Access database as well as username and password if required.
We are also using Windows Authentication, but we could just have easily used one of the user accounts on our SQL database. Choose whether to copy one or more tables or to use a query to specify the data to transfer. In this example, we are going to copy a table and all its data.
0コメント