If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a non-clustered Index… Read More
Transact-SQL Optimization Tips· Use views and stored procedures instead of heavy-duty queries.This can reduce network traffic, because your client will send to server only stored proc… Read More
21: What is GROUP BY?A: The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Withou… Read More
Often while working with T-SQL we create copies of tables as a backup of the original/base table so that we can make changes to the data in the original/base table for testing purposes, and… Read More
What is normalization? Explain different levels of normalization?Check out the article Q100139 from Microsoft knowledge base and of course, there's much more information available in the net… Read More
1. What’s the difference between a primary key and a unique key?Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creat… Read More
In a general way, if we define a cluster then we will define like this: A cluster is a group of some items which acts like a single system. From the figure 1, you can easily find your answer… Read More
This is perfect login code for login into application, here I am using stored procedures. Here I posted my table code and stored procedure code.LoginForm.csusingSystem.Data.SqlClient;n… Read More
What is RDBMS?Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained ac… Read More
This script will list all indexes and their types (clustered/nonclustered).
SELECT so.name AS TableName
, si.name AS IndexName
, si.type_desc AS IndexType
FROM… Read More
SQL Server, DBA interview questionsQuestions are categorized under the following sections, for your convenience:Database design (8 questions)SQL Server architecture (12 questions)Database ad… Read More
Recently, I’ve been working on a project that of necessity involves periodically updating data in some reasonably large tables that exist in an Operational Data Store (ODS). This parti… Read More
TSQL Interview QuestionsWhat is DBMS?A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that… Read More
I was working with a customer to troubleshoot memory optimized table issues. In this scenario, our customer uses a memory optimized table variable. He put 1 million rows of data… Read More
I can proudly announce that it is already possible to preorder the SQL Server 2016 Developer’s Guide book (https://www.amazon.com/SQL-Server-2016-Developers-Guide-ebook/dp/B01MS5L01Q/r… Read More
In the last post, I have discussed 10 ways to prevent SQL database corruption and now, I am going to discuss query optimization in SQL Server database.Microsoft SQL Server performs most of t… Read More
Indexes? Indexes are a key factor for a database, relational or not. Without indexes the only option for a database would be to read all the data and discard what is not needed. Very ineffi… Read More
Recently I assisted on a customer issue where customer wasn’t able to alter a memory optimized table with the following error
Msg 41317, Level 16, State 3, Procedure ddl, Line 4 [Batch… Read More
One of the design goals of SQL Server 2016 was to improve performance and scalability of the engine out of the box to make it run faster without the need of any knobs or trace flags for cust… Read More
Tiger Team (@mssqltiger) represented by (Ajay Jagannathan, Parikshit Savjani, Tejas Shah and Akbar Farishta) and the Data Migration team represented by (Sudhakar Sannakkayala, Amit Banerjee… Read More
Issue
Exception information:
Exception type: CrmException
Exception message: Failed to delete audit data from one or more of the specified p… Read More
It should be commonly understood that any ERP system is built to appeal generally to a wide variety of companies. Epicor is no exception to the rule. This is a fundamental principle of… Read More
SAP released the report MSS_CS_CREATE a few months ago. Using this report, customers can create an additional Nonclustered Columnstore Index (NCCI) on any SAP ERP table. This has already bee… Read More
We have a database here that stores information about Azure SQLDB health issues; it was chugging along fine for months, then earlier this week it started to struggle. You can see the large i… Read More
I have a table that captures the host platform that a user is running on. The table's definition is straightforward: IF OBJECT_ID('[Auth].[ActivityPlatform]', 'U') IS NULL BEGIN CREATE TABLE… Read More
These last days, I have been working in several service requests with same behavior.
Customer has an application that that is searching a value per execution.
The parameter value that is usi… Read More
Indexes are the primary source of the databases. Terribly designed indexes is one of the major problem faced by the user. Designing it properly is the major task as it is used to achieve bes… Read More
If you follow this blog, you know that in the last couple years, we have been adding more information to showplan, to make it a one-stop-shop for all your query performance troubleshooting i… Read More
When you need to check the health or status of all INDEXs and need to REBUILD the required INDEXs depends on the Fragmentation level (%), you can get help from below SQL queries.In my exampl… Read More
What Is Optimizer Timeout?
SQL Server uses a cost-based query optimizer. Therefore, it selects a query plan with the lowest cost after it has built and examined multiple query plans. One of… Read More
The task of upgrading a SQL Server 2008 R2 cluster to SQL Server 2012 with the idea of using the AlwaysOn accessibility groups in the future can be quite complex; it depends on… Read More
SQL - Structured Query language is used to communicate with DataBase , it is the standard language for relational database management systems.As part of this course you will learn below topi… Read More
According to expert Koen Verbeeck, comprehending whatever you need from SQL Server is perhaps the most important step in successful usage in commercial applications.
Successful deploymen… Read More
This article focus on suspect SQL database, a problem encountered sometimes by Database Administrators - DBAs. It is uncommon, but high chance of appearing suddenly. Database administrators… Read More
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?A one-to-One relationship can be implemented as a single table and rarely as two tables wit… Read More
Databases are an essential part of modern computing, and the choice of a DBMS system can have a significant impact on the performance, scalability, and cost-effectiveness of an application… Read More
Question: How can I find tables in a SQL Server database that have a Primary Key ?
Answer: To identify if a table has a Primary Key - you can use the OBJECTPROPERTY method with the pro… Read More
Gone are the days of traditional file-based systems. With the advent of new technologies, particularly, in the database and database-related avenues, the demand for professionals adept with… Read More
In this data-driven world, businesses are constantly searching for the perfect data management and analytics solution that can cater to their unique needs. Among the top contenders are Micro… Read More
SQL Basic to Advance
History of SQL:
SQL, short for Structured Query Language, has a history rooted in the 1970s when IBM developed the System R project. It aimed to create a database manage… Read More
Structured Query Language (SQL) is one of the most popular programming languages used by developers, data analysts, database administrators, and various other professionals working with data… Read More