sqlserver material 6
How to Find Tables without Indexes?
Run the following query in the Query Editor.USE <database_name>;GOSELECT SCHEMA_NAME(schema_id) AS schema_name,name AS table_nameFROM sys.tablesWHERE OBJECTPROPERTY(OBJECT_ID,'IsIndexed') = 0ORDER BY schema_name, table_name;GOHow to Copy Data from One Table to Another Table?
There are multiple ways to do this.1) INSERT INTO SELECT
This method is used when table is already created in the database earlier and data have to be inserted into this table from another table. If columns listed in the INSERT clause and SELECT clause are same, listing them is not required.
2) SELECT INTO
This method is used when table is not created earlier and it needs to be created when data from one table must be inserted into a newly created table from another table. The new table is created using the same data types as those in selected columns. (Read more here)
What is Catalog Views?
Catalog views return information that is used by the SQL Server Database Engine. Catalog Views are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.What is PIVOT and UNPIVOT?
A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.In simpler word UNPIVOT table is reverse of PIVOT Table, however it is not exactly true. UNPIVOTING is for sure reverse operation to PIVOTING but if during PIVOTING process data aggregated the UNPIVOT table does not return to original table. (Read more here)
What is a Filestream?
Filestream allows you to store large objects in the file system and have these files integrated within the database. It enables SQL Server-based applications to store unstructured data such as documents, images, audios and videos in the file system. FILESTREAM basically integrates the SQL Server Database Engine with New Technology File System (NTFS); it basically stores the data in varbinary (max) data type. Using this data type, the unstructured data is stored in the NTFS file system, and the SQL Server Database Engine manages the link between the Filestream column and the actual file located in the NTFS. Using Transact-SQL statements users can insert, update, delete and select the data stored in FILESTREAM-enabled tables.What is SQLCMD?
sqlcmd is enhanced version of the isql and osql, and it provides way more functionality than other two options. In other words, sqlcmd is better replacement of isql (which will be deprecated eventually) and osql (not included in SQL Server 2005 RTM). sqlcmd can work in two modes – i) BATCH and ii) interactive modes. (Read more here)What do you mean by TABLESAMPLE?
TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set. (Read more here)What is ROW_NUMBER()?
ROW_NUMBER() returns a column as an expression that contains the row’s number within the result set. This is only a number used in the context of the result set; if the result changes, the ROW_NUMBER() will change.What are Ranking Functions?
Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic. The different Ranking functions are as follows:ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.
DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. (Read more here )
What is Change Data Capture (CDC) in SQL Server 2008?
Change Data Capture (CDC) records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables and makes a record available of what changed, where, and when, in simple relational ‘change tables’ rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track along with the metadata needed to understand the changes that have been made.How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?
In SQL Server 2005 and earlier versions, there is no inbuilt functionality to know which row was recently changed and what the changes were. However, in SQL Server 2008, a new feature known as Change Data Capture (CDC) has been introduced to capture the changed data. (Read more here)
What is the CPU Pressure?
CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks and there are more tasks in the queue that have not yet started. (Read more here)
How can I Get Data from a Database on Another Server?
If you want to import data only through T-SQL query, then use OPENDATASOURCE function. To repeatedly get data from another server, create a linked server and then use the OPENQUERY function or use 4-part naming. If you are not adhered with T-SQL, then it is better to use import/export wizard, and you can save it as a SSIS package for future use. (Read more here)
What is the Bookmark Lookup and RID Lookup?
When a small number of rows are requested by a query, the SQL Server optimizer will try to use a non-clustered index on the column or columns contained in the WHERE clause to retrieve the data requested by the query. If the query requests data from columns not present in the non-clustered index, then the SQL Server must go back to the data pages to get the data in those columns. Even if the table has a clustered index or not, the query will still have to return to the table or clustered index to retrieve the data.
In the above scenario, if table has clustered index, it is called bookmark lookup (or key lookup); if the table does not have clustered index, but a non-clustered index, it is called RID lookup. (Read more here)
What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE:
Specifies whether to roll back after a specified number of seconds or immediately if transaction is not complete.
NO_WAIT:
Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, then the request will fail.(Read more here)
What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?
In case of GETDATE, the precision is till milliseconds, and in case of SYSDATETIME, the precision is till nanoseconds.(Read More Here)
How can I Check that whether Automatic Statistic Update is Enabled or not?
The following query can be used to know if Automatic Statistic Update:
SELECT is_auto_create_stats_on,is_auto_update_stats_on
FROM sys.databases
WHERE name =‘YOUR DATABASE NAME
FROM sys.databases
WHERE name =‘YOUR DATABASE NAME
How to Find Index Size for Each Index on Table?
We can use the following query to find the size of index.
SELECT *
FROM sys.indexes
WHERE OBJECT_ID=OBJECT_ID('HumanResources.Shift')
FROM sys.indexes
WHERE OBJECT_ID=OBJECT_ID('HumanResources.Shift')
What is the Difference between Seek Predicate and Predicate?
Seek Predicate is the operation that describes the b-tree portion of the Seek. Predicate is the operation that describes the additional filter using non-key columns. Based on the description, it is very clear that Seek Predicate is better than Predicate as it searches indexes, whereas in Predicate, the search is on non-key a column – which implies that the search is on the data in page, files itself.
What are Basics of Policy Management?
SQL server 2008 has introduced a policy management framework, which is the latest technique for SQL server database engine. SQL policy administrator uses SQL Server Management Studio to create policies that can handle entities on the server side like the SQL Server objects and the instance of SQL Server databases. It consists of three components: policy administrators (who create policies), policy management, and explicit administration. Policy-based management in SQL Server assists the database administrators in defining and enforcing policies that tie to database objects and instances. These policies allow the administrator to configure and manage SQL server across the enterprise. (Read more here)
What are the Advantages of Policy Management?
The following advantages can be achieved by appropriate administration of policy management system.
- It interacts with various policies for successful system configuration.
- It handles the changes in the systems that are the result of configuration against authoring policies.
- It reduces the cost of ownership with simple elaboration of administration tasks.
- It detects various compliance issues in SQL Server Management Studio.
What are Policy Management Terms?
To have a better grip on the concept of Policy-based management, there are some key terms you need to understand.
Target – A type of entity that is appropriately managed by Policy-based management. For example, a table, database and index, to name a few.
Facet -A property that can be managed in policy-based management. A clear example of facet is the name of Trigger or the Auto Shrink Property of database.
Conditions – Criteria that specifies the state of facet to true or false. For example, you can adjust the state of a facet that gives you clear specifications of all stored procedures in the Schema ‘Banking’.
Policy – A set of rules specified for the server objects or the properties of database.
What is the ‘FILLFACTOR’?
A “FILLFACTOR” is one of the important arguments that can be used while creating an index.
According to MSDN, FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.
Specifying a fill-factor value of 70 would imply that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table. The fill-factor setting applies only when the index is created or rebuilt. (Read more here)
Where in MS SQL Server is ’100’ equal to ‘0’?
Fill-factor settings of 0 and 100 are equal! (Read more here)
What are Points to Remember while Using the FILLFACTOR Argument?
- If fill-factor is set to 100 or 0, the Database Engine fills pages to their capacity while creating indexes.
- The server-wide default FILLFACTOR is set to 0.
- To modify the server-wide default value, use the sp_configure system stored procedure.
- To view the fill-factor value of one or more indexes, use sys.indexes.
- To modify or set the fill-factor value for individual indexes, use CREATE INDEX or ALTER INDEX statements.
- Creating a clustered index with a FILLFACTOR < 100 may significantly increase the amount of space the data occupies because the Database Engine physically reallocates the data while building the clustered index. (Read more here)
What is a ROLLUP Clause?
ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. If we want sum on different levels without adding any new column, then we can do it easily using ROLLUP. We have to just add the WITH ROLLUP Clause in group by clause. (Read more here)
What are Various Limitations of the Views?
- ORDER BY clause does not work in View.
- Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, then we will have to modify them first.
- Index created on view not used often.
- Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed.
- One of the most prominent limitations of the View it is that it does not support COUNT (*); however, it can support COUNT_BIG (*).
- UNION Operation is now allowed in Indexed View.
- We cannot create an Index on a nested View situation means we cannot create index on a view which is built from another view.
- SELF JOIN Not Allowed in Indexed View.
- Outer Join Not Allowed in Indexed Views.
- Cross Database Queries Not Allowed in Indexed View.
What is a Covered index?
It is an index that can satisfy a query just by its index keys without having needed to touch the data pages.
It means that when a query is fired, SQL Server doesn’t need to go to the table to retrieve the rows, but can produce the results directly from the index as the index covers all the columns used in query. (Read more here)
When I Delete any Data from a Table, does the SQL Server reduce the size of that table?
When data are deleted from any table, the SQL Server does not reduce the size of the table right away; however, it marks those pages as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages. If you wait for sometime, the background process de-allocates the pages, finally reducing the page size. (Read more here)
What are Wait Types?
There are three types of wait types, namely,
Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker or it’s not yet available.
Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.
External Waits. External waits occur when an SQL Server worker is waiting for an external event. (Read more here)
How to Stop Log File Growing too Big?
If your Transaction Log file was growing too big and you wanted to manage its size, then instead of truncating transaction log file, you should choose one of the options mentioned below.
1) Convert the Recovery Model to Simple Recovery
If you change your recovery model to Simple Recovery Model, then you will not encounter the extraordinary growth of your log file. However, please note if you have one long running transaction it will for sure grow your log file till the transaction is complete.
2) Start Taking Transaction Log Backup
In this Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits.
If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?
No, we can’t see definition of encrypted stored procedure in Activity Monitor
Comments