An associative entity is a conceptual concept. An associative entity can be thought of as both an entity and a relationship since it encapsulates properties from both. It is a relationship since it is serving to join two or more entities together, but it is also an entity since it may have its own properties.
Posted Date:- 2021-09-18 06:25:51
Scheduled tasks let you manage the tasks in an automated manner that runs on regular or predictable cycles. You can schedule administrative tasks and also determine the order in which tasks will run.
Posted Date:- 2021-09-18 06:24:55
Refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by-products and/or departments, or by associated costs and incomes.
Posted Date:- 2021-09-18 06:24:14
This means the transaction finish completely, or it will not occur at all.
Posted Date:- 2021-09-18 06:12:42
A prime attribute is an attribute that is part of a candidate key.
Posted Date:- 2021-09-18 06:12:07
An ER diagram or Entity-Relationship diagram is a special picture used to represent the requirements and assumptions in a system from a top-down perspective. It shows the relations between entities (tables) in a database.
Posted Date:- 2021-09-18 06:11:34
This is a very open-ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be:
1. No indexes
2. No Table scans
3. Missing or out of date statistics
4. Blocking
5. Excess recompilations of stored procedures
Posted Date:- 2021-09-18 06:10:47
Perhaps the best performance gain is when you can create a cursor asynchronously rather than needing the whole population operation to be completed before further processing can continue. Then, by checking specific global variables settings, you can tell when there is no further processing to take place. However, even here, care has to be taken. The asynchronous population should only occur on large record sets rather than those that only deal with a small number of rows.
Use the smallest set of data possible. Break out of the cursor loop as soon as you can. If you find that a problem has occurred, or processing has ended before the full cursor has been processed, then exit. If you are using the same cursor more than once in a batch of work, and this could mean within more than one stored procedure, then define the cursor as a global cursor by using the GLOBAL keyword, and not closing or deallocating the cursor until the whole process is finished. A fair amount of time will be saved, as the cursor and the data contained will already be defined, ready for you to use.
Posted Date:- 2021-09-18 06:09:45
Database table: The table contains records in the form of rows and columns. A permanent table is created in the database you specify and remains in the database permanently until you delete it.
Syntax:
1. Create table TableName (ID INT, NAME VARCHAR(30) )
2. Drop syntax: drop table TableName
3. Select Syntax: Select * from TableName
Click on the following link to read more on temporary tables in SQL.
Posted Date:- 2021-09-18 06:07:53
Authentication mode is used for authentication of the user in SQL server, and it can be selected at the time of setup of the database engine.
SQL Server supports two authentication modes: Window authentication mode and mixed mode.
Window authentication mode: This authentication mode is used to connect through a Microsoft NT 4.0 or window 2000 user account. In Windows authentication server take computer's username and password for authentication purpose. SQL server authentication mode is disabled in this mode.
Mixed mode: It is used to connect with the instance of SQL Server using window authentication or SQL Server authentication. In SQL server authentication mode a unique username and password are required for a particular database, as it will not depend on windows account.
Posted Date:- 2021-09-18 06:06:00
First and foremost, a stored procedure is a compiled set of code, where passing T-SQL through languages such as VB, Visual FoxPro, etc., means that the set of code needs to be compiled first. Although T-SQL within VB, etc., can be prepared before running, this is still slower than using a stored procedure. Then, of course, there is the security aspect, where, by building a stored procedure, you can place a great deal of security around it.
When dealing with sensitive data, you can use an encrypted stored procedure to hide sensitive columns, calculations, and so on. Finally, by using a stored procedure, I feel that transactional processing becomes a great deal easier and, in fact, using nested transactions becomes more insular and secure. Having to deal with transactions within code that may have front-end code, will slow up a transaction, and therefore a lock will be held for longer than necessary.
Posted Date:- 2021-09-18 06:05:07
It returns the most recently created identity value for the tables in the current execution scope.
Posted Date:- 2021-09-18 06:04:10
it updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.
Posted Date:- 2021-09-18 06:03:34
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.
Posted Date:- 2021-09-18 06:02:47
One-to-One relationship: It can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One to one relationship exists if an entity in one table has a link with only one entity on another table. Let?s take an example of the employee and their employee id so that a unique employee id will be there for a particular employee at another table.
Posted Date:- 2021-09-18 06:01:58
In SQL there is an object which is known as a virtual table, it is not stored directly in the file of the database. The virtual table allows SQLite for manipulating and accessing the resources by using a powerful language of SQL query. In other terms, the view in SQL is known as a virtual table. This view represents the columns and rows like any real table.
To create a view in SQL you can use the following syntax:
CREATE VIEW view_name AS
SELECT column1, column2, . . .
FROM table_name
WHERE condition;
So, a virtual table doesn't exist physically in the database. It helps in defining things that can be used as the SQL statements just similar to a real table.
Posted Date:- 2021-09-18 06:01:04
In SQL a cross join is also known as a cartesian product which is used to produce a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.
Posted Date:- 2021-09-18 05:59:25
Triggers are special kinds of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE, and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder.
Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Posted Date:- 2021-09-18 05:58:08
USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
Posted Date:- 2021-09-18 05:56:27
An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using the sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy.
Posted Date:- 2021-09-18 05:55:17
A foreign key is basically a primary key of another table used in the given table. It is used to create connections between two different tables. Whereas a unique key uniquely determines a row that isn’t the primary key and Creates a non-clustered index.
Posted Date:- 2021-09-18 05:54:38
Global temporary tables are represented with ## before the table name. Scope will be the outside the session whereas local temporary tables are inside the session. Session ID can be found using @@SPID.
Posted Date:- 2021-09-18 05:53:36
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS, and FULL OUTER JOINS.
Posted Date:- 2021-09-18 05:29:02
Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax).
SELECT select_list
[HDEV:INTO new_table_]
FROM table_source
[HDEV:WHERE search_condition]
[HDEV:GROUP BY group_by_expression]
[HDEV:HAVING search_condition]
[ORDER BY order_expression [ASC | HDEV:DESC] ]
Posted Date:- 2021-09-18 05:28:15
RDBMS: It is referred to as Relation Database Management Systems (RDBMS).
RDBMS possesses the below characteristics:
<> Write-intensive operations: The RDBMS is frequently written to and is often used in transaction-oriented applications.
<> Data in flux or historical data: The RDBMS is designed to handle frequently changing data. <> Alternatively, RDBMS can also store vast amounts of historical data, which can later be analyzed or "mined".
<> Application-specific schema: The RDBMS is configured on a per-application basis and a unique schema exists to support each application.
<> Complex data models. The relational nature of the RDBMS makes it suitable for handling sophisticated, complex data models that require many tables, foreign key values, complex join operations, and so on.
<> Data integrity: The RDBMS features many components designed to ensure data integrity. This includes rollback operations, referential integrity, and transaction-oriented operations.
Posted Date:- 2021-09-18 05:27:03
1.Logical – Planning, Analysis, and Design
2.Physical – Design, Implementation, and Maintenance
Posted Date:- 2021-09-18 05:25:34
A developer must investigate volumes of data (capacity planning), what types of information will be stored, and how that data will be accessed. If you are dealing with an upgrade to an existing system, analyzing the present data and where existing data volumes occur, how that data is accessed and where the current response bottlenecks are occurring, can help you search for problem areas in the design.
A new system would require a thorough investigation of what data will be captured, and looking at volumes of data held in other formats also will aid design. Knowing your data is just as important as knowing the constituents of your data. Also, constantly revisit your design. As your system is built, check relationships, volumes of data, and indexes to ensure that the physical design is still at its optimum. Always be ready to check your system by using tools like the SQL Server Profiler.
Posted Date:- 2021-09-18 05:25:02
It plays a very major part. When building a new system, or adding to an existing system, the design must be correct. Ensuring that the correct data is captured and is placed in the appropriate tables, that the right relationships exist between the tables, and that data redundancy is eliminated is an ultimate goal when considering performance. Planning a design should be an iterative process, and constantly reviewed as an application is developed.
It is rare, although it should be the point that everyone tries to achieve, when the initial design and system goals are not altered, no matter how slightly. Therefore, a designer has to be on top of this and ensure that the design of the database remains efficient.
Posted Date:- 2021-09-18 05:24:33
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the properties of a transaction.
Posted Date:- 2021-09-18 05:24:09
PL/SQL (Procedural Language for SQL) is a procedural language developed by Oracle to work with the Oracle database using procedures in SQL. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database. PL/SQL syntaxes include declarations for variables, constants, procedures, functions, conditions, and loops.
Control statements in PL/SQL:
* Control statements are very important in PL/SQL.
* Control Statements are elements in a program that controls the flow of program execution.
* The syntax of control statements is similar to regular English and is very similar to the choices that we make every day.
* Branching statements are as follows:
* If statement
* If - THEN - ELSE
* Nested IF
* Branching with logical connectivity
* While
* For Loop
Posted Date:- 2021-09-18 05:23:48
FLOOR function is used to round up a non-integer value to the previous least integer. Example is given
FLOOR(6.7)
Returns 6.
Posted Date:- 2021-09-18 05:22:10
Exceptions are handled using TRY—-CATCH constructs and it is handles by writing scripts inside the TRY block and error handling in the CATCH block.
Posted Date:- 2021-09-18 05:20:50
Lets you extend the base SQL Server data types by providing a descriptive name and format to the database
E.g. Flight_num appears in many tables and all these tables have varchar(8)
Create a user-defined data-type
Posted Date:- 2021-09-18 05:19:01
It is a value that will be used by a column if no value is supplied to that column while inserting data.
I can’t be assigned for identity and timestamp values.
Posted Date:- 2021-09-18 05:18:29
UNION – It is used to select the related information.
It is similar to that of JOIN command.
UNION ALL – It is similar to that of UNION command, but it selects all the values.
It does not remove the values from the table but will retrieve the data.
Posted Date:- 2021-09-18 05:17:47
When the triggers are fired for any DML command, insert and delete tables are created, these tables are called Magic tables in SQL server.These tables are used inside the triggers for transactions.
Posted Date:- 2021-09-18 05:16:54
Exceptions are handled using TRY—-CATCH constructs and it is handles by writing scripts inside the TRY block and error handling in the CATCH block.
Posted Date:- 2021-09-18 05:16:11
Scheduled tasks or jobs are used to automate processes that can be run on a scheduled time at a regular interval. This scheduling of tasks helps to reduce human intervention during night time and feed can be done at a particular time. User can also order the tasks in which it has to be generated.
Posted Date:- 2021-09-18 05:15:38
The SQL Server agent plays a vital role in day to day tasks of SQL server administrator(DBA). Server agent’s purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.
Posted Date:- 2021-09-18 05:03:47
Following methods are used to protect from SQL injection attack:
<> Filtering input parameters
<> Use parameter collection with Dynamic SQL
<> Use Parameters for Stored procedures
<> In like clause, use escape characters
Posted Date:- 2021-09-18 04:48:43
A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.
Posted Date:- 2021-09-18 04:47:17
A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.
Posted Date:- 2021-09-18 04:47:14
It is the reverse process of normalization. It increases query performance by reducing the joins. It is used for OLAP applications.
Posted Date:- 2021-09-18 04:46:42
Trigger – It is used to execute a batch of SQL code when the commands like INSERT, UPDATE or DELETE are executed against a table.
They are automatically executed or triggered when the data gets modified.
Types of Triggers:
* Insert
* Update
* Delete
* Instead of
Posted Date:- 2021-09-18 04:46:23
It is the way to eliminate redundant data
1. Reduces null value
2. Enables efficient indexing
3. 1NF – Removes duplicated attributes, Attribute data should be atomic, and attribute should be the same kind.
4. 2NF – This should be in 1NF and each non-key is fully dependent on the primary key.
5. 3NF – This should be in 2NF and all the non-key attributes which are not dependent on the primary key should be removed. All the attributes which are dependent on the other non-key attributes should also be removed. Normalization is done in.
Posted Date:- 2021-09-18 04:45:36
Subquery – It is a query which is used when expressions are allowed and can be nested inside the main query like SELECT, UPDATE, DELETE or INSERT statements.
Properties:
<> A subquery in the main query that has to be placed on the right-hand side of the comparison operator
<> A subquery has to be placed parenthesis, in order to get executed first before the main query.
<> It cannot have any order by clause.
<> More than one subquery can be included in the main query.
Posted Date:- 2021-09-18 04:44:33
COALESCE – It is a function that is used to return the first non-null expression from more than one column within the arguments.
CHECK constraint – It is used to enforce the integrity. It is applied to a column in a table to limit the values that have to be placed in a column.
Posted Date:- 2021-09-18 04:43:21
It is a tool that allows system’s administrator to monitor the events in SQL Server.It is mainly used to capture and save the data of each event of a file or a table for analysis.
Posted Date:- 2021-09-18 04:42:52
SQL Server agent
The SQL Server agent plays an important part in the day to day tasks of the SQL Server Database Administrator (DBA).
Its purpose is to implement the tasks easily with the Scheduler engine that allows our tasks to run at a scheduled time and date.
Modes of Authentication in SQL Server
The two authentication modes in SQL Server are:
* Windows Mode
* Mixed Mode
From the tools menu of SQL Server configuration properties in the security page, the modes can be changed.
Posted Date:- 2021-09-18 04:42:26
MSSQL stands for Microsoft Server SQL that is a Microsoft’s relational database management system. It is a featured database which is designed to compete against Oracle Database and MySQL. MSSQL is also referred as SQL Server.
Posted Date:- 2021-09-18 04:41:40