Find Table Creation Date In SQL Server

Introduction In this article, we will discuss how to get the created date of a table. Sometimes, we may need to know when the table was created in the particular database. Rather than relying on the row inserted date, we can see when the table was created. Finding the Created date using T-SQL SELECT name... Continue Reading →

List All Available Time Zone – SQL Server

Introduction In this simple tips and tricks article, I would like to share how many time zones does our SQL Server supports. Well, it's pretty much easy to find by using the system table sys.time_zone_info sys.time_zone_info Here is the sample T-SQL to get the list of time zones our SQL Server supports. SELECT name AS... Continue Reading →

To Get Total Number of Columns In a Table In SQL Server

Introduction In this simple article, lets explore to get total number of columns in a table in SQL Server Example SELECT COUNT(COLUMN_NAME) AS [Number of Columns] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'PracticalWorks' AND TABLE_SCHEMA = 'Demo' AND TABLE_NAME = 'OrderDetails' Fig. 1 Number of Columns in a Table From the INFORMATION_SCHEMA.COLUMNS table we can find... Continue Reading →

sp_rename – Rename a column or table in MS SQL Server

Introduction In this article, I would like to share how to rename a column name or table name in SQL Server using T-SQL. For that, we have to use sp_rename. sp_rename renames the objects in the current database. Renaming column name to new column name The syntax for renaming a column in SQL Server using... Continue Reading →

Altering Column – From NULL to NOT NULL in SQL Server

Introduction When working in SQL Server tables, sometimes we may need to change a column that allows NULL to NOT NULL. This change is required as part of business requirement, that is definitely this column contains value or else as part of performance improvements, we may be in need to change the column from NULL... Continue Reading →

Execution Time of Stored Procedures In SQL Server

Introduction When working in SQL Server Stored Procedures, we are keen about the execution time of it. Its very simple to find execution time of stored procedures in SQL Server. Execution Time Its simple to figure out the execution time of stored procedures using SET STATISTICS TIME ON. SET STATISTICS TIME ON EXEC [Sales].[pGetSalesDetails] After... Continue Reading →

Different Ways To Get Table Column Names In SQL Server

Introduction In this simple article, we will discuss different ways to find the particular table's column names in SQL Server. Method One The first approach is using the system table sys.columns. This table results many columns, but we are interested only in the column name, so I am selecting name column from the sys.columns table,... Continue Reading →

Python Packages in SQL Server – Get Started with SQL Server Machine Learning Services – Part 5

Introduction When installing Machine Learning Services in SQL Server by default few Python Packages are installed. In this article, we will have a look on how to get those installed python package information. Python Packages When we choose Python as Machine Learning Service during installation, the following packages are installed in SQL Server, revoscalepy -... Continue Reading →

Find Columns That Allows NULL In SQL Server

Introduction The columns in a table, that allows NULL in a database sometimes can lead to performance issues. The Nullable columns may be one of the reason to lead query to performance bad. By making columns NOT NULL can help the query to perform well. In this article we will discuss a simple way to... Continue Reading →

SQL Server Rank Functions

Introduction As like an Aggregate functions, the Ranking functions will rank the values of specified field and categorize them according to their rank . Ranking functions are most commonly used to find the top records based on conditions, example, to find the top ranking students, top highest paid employees and etc. Ranking Functions There are... Continue Reading →

T-SQL Query For Finding The Longest And Shortest names In A Table

Introduction In SQL Server, we can find the Longest String Value and Shortest String Value from the same table. To find the longest and shortest values, there are many ways, but in this article, we will discuss some simplest ways. Disclaimer: From this article, I am not sharing these are the only efficient ways to... Continue Reading →

MERGE Statement in SQL Server to Insert, Update, Delete Records

Introduction In a single transaction (MERGE Statement) we can perform Insert, Update and Delete records on a existing table based on the result comparison between important columns with another table. Which also means we no longer needs to write multiple T-SQL statements to handle Insert, Update, Delete. MERGE Statements To perform MERGE, we need two... Continue Reading →

Function to Calculate Simple Interest In SQL Server

Introduction Whenever working in Financial Applications like Banking applications or financial modules in any ERP application we might have seen as Simple Interest. Usually we do those calculations in the programming application or else in the Frontend side and pass that calculated value to the database tables. But we can also do the same simple... Continue Reading →

Creating T-SQL Query Shortcuts in SQL Server Management Studio(SSMS)

Introduction Using keyboard shortcuts, it saves time, isn't it?. SQL Server Management Studio is also not an exception !!!. We can add our own custom keyboard shortcuts, that too for T-SQL query. Really cool !!!! How many of you know about this feature???. Do not worry if you did not know this before. Now you... Continue Reading →

Efficient Way Of Copying Database In SQL Server

Introduction Sometimes when working in Database, we may need to create another working copy of the current database or production database to check our new logic which should not affect the existing objects such as tables, stored procedures or etc. Example, suppose we may need to check few things using the Production database, but applying... Continue Reading →

One Drop Statement For Multiple Tables In SQL Server

Introduction When as a developer working in a database, which is in Development state, we may create some physical tables or temp tables to check the logic. Or based on the initial requirement we may have created more tables as part of Normalizing the data and our reviewer may suggest some better ideas to accommodate... Continue Reading →

List of Available Database for Current User In SQL Server

Introduction When working in the SQL Server, we may have to check some other databases other than the current one which we are working. In that scenario we may not be sure that does we have access to those Databases?. In this article we discuss the list of databases that are available for the current... Continue Reading →

Find Recently Modified Tables In SQL Server Database

Introduction SQL Server maintains a table to keep information about each tables that are created in appropriate databases. Its a System Table called as sys.tables. For every table created to the database, a new one record is created in the sys.tables table which contains information such as table name, object id of table, created date,... Continue Reading →

Import Dataset In Python From SQL Server – Get Started with SQL Server Machine Learning Services – Part 4

Introduction In our previous article, we discuss about how to run Python in SQL Server that hard coded. In this article we will discuss about the data which is already stored in our SQL Server Databases. Steps to Process Python with SQL Server Data In order to work with Data using Python, we have to... Continue Reading →

SQL Server sp_execute_external_script Stored Procedure – Get Started with SQL Server Machine Learning Services – Part 3

Introduction When we want to execute any external language script in SQL Server, then we have to sp_execute_external_script stored procedure. sp_execute_external_script  is a new system stored procedure which was introduced in the SQL Server 2016. It is mainly used to execute the Machine Learning Services such as Python and R in SQL Server. Executing Python... Continue Reading →

SQL Query To Increase Salary By Percentage For All Employees In A Table

Introduction Sometimes we may need to increase the current column value by Percentage. The very good example is Salary in an organization. We need to update the salary of each employee based on the Hike Percentage. Increasing Salary By Percentage In this example, lets assume the Hike percentage is equal for all employees, and I... Continue Reading →

T-SQL Query To Order By The Last Three Characters Of A Column

Introduction In this article, we will discuss how to Order By the Column Result By Last Three Characters of that Column. Order By The Last Three Characters First lets select all the rows from the table, SELECT StudentId, FirstName, LastName, RegistrationNumber FROM [Details].[StudentDetails] Fig 1. All Results from the table StudentDetails Now lets apply logic... Continue Reading →

Azure Data Studio Tips And Tricks

Introduction Who will say NO to when they want to improve the speed and efficiency of writing SQL Queries?. Azure Data Studio has more features to improve efficient way of writing SQL Queries. In this article, we will have a look some Tips and Tricks in Azure Data Studio Command Palette If we want to... Continue Reading →

Select Names NOT Starting with Vowels in MS SQL Server

Introduction Sometime we may need to select the column results that the string not starting with Vowels. In this article, We will have a look, How to select the columns results not starting with Vowels. In our previous articles we discussed how to select columns results  Select Names Starting With Vowels in MS SQL ServerSelect Names... Continue Reading →

Select Names NOT Starting And NOT Ending With Vowels in MS SQL Server

Introduction Sometime we may need to select the column results that the string not starting and not ending with Vowels. In this article, We will have a look, How to select the columns results not starting and not ending with Vowels. In our previous articles we discussed how to select columns results  Select Names Starting With... Continue Reading →

Select Names NOT Ending With Vowels in MS SQL Server

Introduction In this article, We will have a look, How to select the columns results NOT ending with Vowels. In our previous articles we discussed how to select columns results starting with vowels and column results ending with vowels. Select Names Starting With Vowels in MS SQL ServerSelect Names Ending With Vowels in MS SQL... Continue Reading →

Select Names Ending With Vowels in MS SQL Server

Introduction Sometime we may need to select the column results that the string ends with Vowels. In this article, We will have a look, How to select the columns results ending with Vowels. In one of the articles, we discussed how to select names Starting with Vowels, please refer that article here. Example As an... Continue Reading →

Install Machine Learning Services for Python-Get Started with SQL Server Machine Learning Services – Part 2

Introduction This is Part 2 of "Get Started with SQL Server Machine Learning Services ". In the part 1 article, we discussed  about the capabilities of Machine Learning in SQL Server. In this article, we will discuss "How to Install Machine Learning Services in SQL Server". Note: If you are new to this article, recommending... Continue Reading →

Difference Between Drop Table, Truncate Table & Delete Table in MS SQL Server – Zero to Hero Query Master – Part 6

Introduction In this article, we will discuss the difference between Drop Table, Truncate Table, Delete Table. To perform dropping, deleting and truncating a table, I am creating three tables. With these three tables let's look into more:  Students.StudentDetailsStudents.StudentMarks Students.StudentClass Drop TableThe syntax for dropping a table in MS SQL is,  Syntax DROP TABLE TableName Example DROP... Continue Reading →

Tables in Database- MS SQL Server – Zero to Hero Query Master – Part 5

Introduction All the data in a database is stored in a Table, which is a data object. Data is organized in Rows and Columns in a table and each row represents a unique record while each column represents a field in the record Types of Tables SQL Server provides the following types of tables that... Continue Reading →

How is Data Stored in SQL Database

Introduction Being Software Developer / DBA / Database Developer, its important to know or understand the concept How the Data is Stored in SQL Database. This knowledge is very important when we want to troubleshoot and fix SQL queries that are not performing well from the Tuning point. In this article, we will discuss how... Continue Reading →

Get Started with SQL Server Machine Learning Services – Part 1

Introduction We know Humans learn from their past experiences. Mean while Machines follow Instructions given by Humans. But what if Human can train Machines to learn from the past data?. In simple, this is what Machine learning is !!!!. SQL Server has capabilities of Machine Learning. In this article, we will discuss about the capabilities... Continue Reading →

SQL Server Date Functions

Introduction Date and Time plays major role in many projects. SQL Server has several date and time functions. In this article, we will discuss all the Date and Time functions in SQL Server with an Example. CURRENT_TIMESTAMP() Returns the date and time of the machine the SQL Server is running on. Example SELECT CURRENT_TIMESTAMP AS... Continue Reading →

SQL Server String Functions

Introduction In this article, we will discuss about the SQL Server String Functions (built in functions) in SQL Server. String Functions in SQL Server ASCII() Return the ASCII code value of a character CHAR() Convert an ASCII value to a character CHARINDEX() Search for a substring inside a string starting from a specified location and... Continue Reading →

Aggregate Functions in SQL Server

Introduction Aggregate Functions are used to return a single value, which is calculated from multiple values in a column. Aggregate functions are build in SQL Server functions which are applied to set of records and we get summarized data. Aggregate Functions The following are the set of built in Aggregate functions in SQL Server, AVG()CHECKSUM()... Continue Reading →

Retrieve Unsaved SQL Query Scripts

Introduction In this article, we will have a look how to retrieve the Unsaved SQL Query using T-SQL. Sometimes, we may close the Query Window or SSMS accidentally without saving. This may cause to loss the Queries which we where working. Retrieve Unsaved SQL Query using T-SQL Statement Apply the below T-SQL Statement in which... Continue Reading →

Select Statement Execution Order In SQL Server

Introduction We all can write the SELECT Statement in SQL Server. But how many of us know the execution order of SELECT statement query?. In this article, lets discuss how the SELECT statement is Executed or the in which order the Query is executed. Query Order Of Execution SELECT column,column1,.... column n FROM TableName JOIN... Continue Reading →

System Databases in SQL Server

Introduction In SSMS, we many of may noticed System Databases under the Database Folder. But how many of us knows its purpose?. In this article lets discuss about the System Databases in SQL Server. System Database Fig. 1 System Databases There are five system databases, these databases are created while installing SQL Server. MasterModelMSDBTempdbResource Master... Continue Reading →

What is Database Normalization in SQL Server – MS SQL Server – Zero to Hero Query Master – Part 4

What is Database Normalization Database normalization is the step by step process of organizing data to minimize data redundancy i.e. Data duplication which in turn ensures data consistency Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies.Normalization rules divide larger tables into smaller tables... Continue Reading →

Schema Comparisons using Visual Studio SQL Data Tools

Introduction There are many occasions where we may need to compare the schema between two databases. For example, comparing Development and Production databases against each other, or comparing a database against a SQL Server Database project. Microsoft Visual Studio SQL Data Tools provides functionality that allows us to compare the structure of the database tables,... Continue Reading →

Link a SQL Server Database Project to a Git Repository

Introduction Continuous Integration has become practice to many development project. Did you know, we can achieve that in SQL Server Project in Visual Studio as well!!. Much needed one !!! There is a relatively easy path to get our databases into Database Projects (which we discussed in previous article), and ultimately source control. Implementing proper database... Continue Reading →

Create and Publish SQL Server Database Project With Visual Studio

Introduction Databases plays an important role in any project these days. It becomes too difficult to manage the objects such as Tables, Views, Procedures and Functions increases over the time and also difficult to manage when number of team members works in Database at the same time. And even after that when we manage the... Continue Reading →

Customize Azure Data Studio with Dashboard Widgets

Introduction Azure Data Studio is a free multi-platform database tool with built-in support for both SQL Server on-premises and Azure SQL Databases. These databases can be accessed in Azure Data Studio for numerous tasks like query editing, data development, built-in charting of T-SQL queries, etc. Database developers or Database Administrators often have a need to... Continue Reading →

Cycle Clipboard Ring In SSMS – Reuse Copied Items

Introduction As a developer, the most used shortcut keys are CTRL+C and CTRL+V i.e., copy and paste. But CTRL+C and CTRL+V allows us to copy paste the last item. But what happens if we want to paste more than one item that we copy or cut without going back? Cycle Clipboard Ring Cycle Clipboard Ring... Continue Reading →

Set custom colors to differentiate between environments in SSMS

Introduction Generally we have to shift between different environments within SQL Server Management Studio, as Dev Environment, Staging, Production, resulting in the creation of multiple query tabs. With each query tab connected to different environments. This leads to unmanageable sometimes and difficult to keep track of each environment. To resolve this confusion, SSMS provides us... Continue Reading →

Recover unsaved SQL queries in SSMS

Introduction During our productive hours, sometimes due to SSMS crashes or sudden machine reboots due to a windows update, during this time there is a possibility to lose the unsaved works / Script files. There is an option to recover our unsaved SQL queries in SSMS. How to recover unsaved SQL Queries By enabling the... Continue Reading →

Multiple Backup Files of the SQL Server database with SSMS and T-SQL

Introduction In this article, we will learn a simple tip to take multiple backup files of one Database. Sometime we may need more than one copy of a backup file in different file location and we can achieve that in two ways, either using T-SQL Query or using SSMS. T-SQL Statement BACKUP DATABASE [PracticalWorks] TO... Continue Reading →

Difference between CURRENT_TIMESTAMP vs GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server

Introduction In this article, we will discuss the difference between CURRENT_TIMESTAMP , GETDATE() , SYSDATETIME() GETUTCDATE() in SQL Server. Even though all four SQL Server function returns the current date-time in SQL Server, there are some subtle differences between them. The main difference between GETDATE() and SYSDATETIME() is that GETDATE returns current date and time... Continue Reading →

Change Schema Name Of Table In MS SQL Server

Introduction In this article we will learn the trick to Move a Table from One Schema to Another Schema. Sometimes accidentally we might have given the incorrect name of a schema for the table while creating. But we can move the newly created table from one schema to another schema. Syntax ALTER SCHEMA NewSchemaName TRANSFER... Continue Reading →

5 Tips to Improve SQL Query Performance

Introduction In this article, I will share few SQL Tips for Boosting our SQL Query Performance. We nobody likes delay in response, right !!. Here are some simple changes that we have to make our query perform faster. The following is the flow of this write-up, Use Schema NameAlways Select ONLY required ColumnsUse NOT EXISTS... Continue Reading →

Identifying object dependencies in SQL Server using SP_DEPENDS

Introduction When developing components for SQL Server one change may affect another database object.  Finding these dependent objects should be straightforward, but in most cases it is not as easy as we would think. There are several methods of getting this information, in this article we will have a look using SP_DEPENDS a System Stored Procedure.... Continue Reading →

Select Names Starting With Vowels in MS SQL Server

Introduction Sometime we may need to select the column results that the string starts with Vowels. In this article, We will have a look, How to select the columns results starting with Vowels. Example As an example, we have a StudentDetails table, first lets select all the results from the table. SELECT [StudentId] ,[FirstName] ,[LastName]... Continue Reading →

Display Line Numbers in a SQL Server Management Studio Query Window (SSMS)

Introduction Sometimes when we encounter an error message for a T-SQL query while debugging. The SQL Server shows the error message along with the line number where the error has occurred. In this article, I will give a simple tip to enable the Line Number feature in SSMS. This feature is applicable to all versions... Continue Reading →

Get list of Tables & Views in MS SQL Server- sp_tables

Introduction Sometimes we may need to get all the tables and views from a database in MS SQL Server. By using T-SQL Statement sp_tables, we can retrieve a list of tables and views. Syntax sp_tables [ @table_name = 'Table name.' ] , [ @table_owner = 'The database user who created the table.' ] , [... Continue Reading →

Selecting Only EVEN or ODD Records from Tables – MS SQL SERVER

Introduction In this article, we will learn to Select only EVEN or ODD records from the MS SQL Server database table🤔. The efficient way to find the record belongs to even or odd is determining by the table's ID column. Select EVEN Records By applying the modulo operator by 2 for the ID column, we... Continue Reading →

Stored Procedure Performance Tuning

Introduction In the Database, we may be using Stored Procedures. Most of the time we are concerned about the performance of the Stored Procedures. In this article, I would like to share a few techniques to improve the stored procedure's performance to get better results in less time. Optimization Techniques SET NOCOUNT ON/OFF - When... Continue Reading →

Optimize your SQL Query – Replace Star (*) in Select Statement – MS SQL Server

Introduction Sometime we may need almost all the columns from a table result. In this scenario we use Star(*) in our select statement i.e. as SELECT * FROM TableName. Using Star(*) in the select statement is BAD. The best approach is selecting the table with column names. In this article we will learn how quickly... Continue Reading →

How to Store non-English Characters in SQL Server

Introduction Sometime, we may need to store non-English characters or multiple languages in our database. In this article, we will have a look at how to store non-English characters in SQL Server. VARCHAR vs NVARCHAR We can store non-English Characters in our database table using the data type NVARCHAR(). The N stands for Unicode. It... Continue Reading →

Backup Database using T-SQL Statements

Introduction In this article, We will discuss how to backup our database in MS-SQL Server using T-SQL Statements. We need to use BACKUP DATABASE statement to create full database backup, along with the name of Database and device to store the backup file. Syntax BACKUP DATABASE database_name TO device_name Example BACKUP DATABASE PracticalWorks TO DISK... Continue Reading →

Pinned tabs in SQL Server Management Studio (SSMS)

Introduction While working in SQL Server Management Studio (SSMS), we may need to open many query windows. But each time we may feel difficult to recognize the important query window which we need to refer frequently. There is an option, we can Pin the tabs which are mostly frequently referred. Pinning Tabs We can pin... Continue Reading →

SCHEMAS in SQL Server -MS SQL Server – Zero to Hero Query Master – Part 3

Introduction This is part 3 of "MS SQL Server- Zero to Hero" and in this article, we will be discussing about the SCHEMAS in SQL SERVER. Before getting into this article, please consider to visit previous articles in this series from below, MS SQL Server – Zero to Hero Query Master – Part 1 MS... Continue Reading →

Split Query Window in SQL Server Management Studio(SSMS)

Introduction Sometime we may write a long query in our query window and we may need to refer the objects or other part of the T-SQL frequently. For that we may be scrolling up and down in the query window. But we have an awesome option for that in the SQL Query Window, which many... Continue Reading →

Last Execution of Stored Procedure in SQL Server

Introduction In an ERP project or any other big applications, there may be many Stored Procedures used. But we are not sure that all the stored procedures are required or not or valid ones. While the requirement(s) has changed, we may ended with another new stored procedure or based on the performance we may have... Continue Reading →

List Databases Available for Current User -SQL Server

Introduction Sometimes, we may be curious to know to which databases do we have access or we need to access a particular database but not sure that we have access or not. In this article, we will discuss a T-SQL that will list the Databases available for current user. For Particular Database HAS_DBACCESS  - This... Continue Reading →

Format JSON Output in SQL Server

Introduction From SQL Server 2016 JSON functions are introduced, since many modern Web and Mobile applications are using JSON data format for exchanging data. Many REST web service results are in format of JSON text or accept the data that is formatted as JSON. In this article, we will have a look How to Create... Continue Reading →

Find user who ran DROP statements – SQL Server

Introduction Sometimes we need to track or find which user has DROPPED the table(s) from the Database. In this article, we will discuss the possible ways to track this situation. I have a table already in my Database, SELECT [StudentId], [FirstName], [LastName] FROM [StudentManagementSystem].[Students].[StudentDetails] Fig 1. Results Drop table Now I am going to apply... Continue Reading →

SQL Server Management Studio Shortcut Keys Cheat sheet

Introduction In this article, I would like to share few shortcut keys which is really useful fIn this article, I would like to share a few shortcut keys which are really useful for day to day usage in SQL Server Management Studio, which will improve your productivity. Main Menu Bar Main Menus in SSMS To... Continue Reading →

MS SQL Server – Zero to Hero Query Master – Part 2

Introduction In this article, you will learn how to create a database, access the created database, and delete the database in SQL Server. This is the continuation of part one of the series of "Zero To Hero in MS SQL Server."  As I stated in my previous article, this article series is mainly for beginners who... Continue Reading →

MS SQL Server – Zero to Hero Query Master – Part 1

Introduction This is part one of the Zero To Hero Series in MS SQL Server. This is mainly focused on beginners who are into MS SQL Server and also for experienced developers who want to freshen up their knowledge for interviews and daily technical work. The examples in this article series will be connecting us... Continue Reading →

Website Built with WordPress.com.

Up ↑

%d bloggers like this: