Skip to main content

Posts

Showing posts with the label SQL Server

Write SQL Server Command with Syntax

  SQL Commands: Main Tips To interact with SQL databases, we need to know the SQL commands. We also called SQL statements. SQL is not case-sensitive, but writing SQL commands in ALL CAPS improves readability of your code.   SQL Statement Syntax Examples, SELECT Statement SELECT * FROM Users The asterisk (*) sign defines all columns of the table. The SELECT statement returns all columns of the Student Users. If you want Name, Age only the try below query   SELECT Name, Age FROM Users   Filtering the Data: WHERE Clause SELECT * FROM Users WHERE Age >=18   LIKE operator is a logical operator that provides to apply a special filtering pattern to WHERE condition in SQL queries. SELECT * FROM Users WHERE Name LIKE 'AN%'   IN operator enables us to apply multiple value filters to WHERE clause. SELECT * FROM Users WHERE Name IN ('Anil','Aradhya')   The BETWEEN operator filters the data that falls into the defined begi...
Read more

Find column name in database sql server?

 /************************************/  /* find column name in database sql server? /*************************************/ /********* Search Tables:***********/ SELECT      c.name  AS 'ColumnName'             ,t.name AS 'TableName' FROM        sys.columns c JOIN        sys.tables  t   ON c.object_id = t.object_id WHERE       c.name LIKE '%MyName%' ORDER BY   TableName,ColumnName; /**** Search Tables and Views: *******/ SELECT      COLUMN_NAME AS 'ColumnName'             ,TABLE_NAME AS  'TableName' FROM        INFORMATION_SCHEMA.COLUMNS WHERE       COLUMN_NAME LIKE '%MyName%' ORDER BY    TableName             ,ColumnName;
Read more

Find List stored procedures in SQL Server database

 /********************************************************************************************/ /*    Example 1: List stored procedures in SQL Server database */ /********************************************************************************************/ SELECT COUNT(*) FROM sys.procedures /********************************************************************************************/ /*    Example 2: List stored procedures in SQL Server database */ /********************************************************************************************/ SELECT COUNT(*) SPCOUNT    FROM INFORMATION_SCHEMA.ROUTINES  WHERE ROUTINE_TYPE='PROCEDURE' /********************************************************************************************/ /*    Example 3: List stored procedures in SQL Server database */ /********************************************************************************************/  select schema_name(obj.schema_id) as schema_name...
Read more

How to Sort a Varchar Column Storing Integers with Order By? SQL Server

Recently I faced a very interesting issue during the column sorting in SQL Server. I have a VARCHAR column in a SQL Server database that can contain either letters or numbers. It depends on how the application is configured on the front-end for the employee codes. One possible solution is to pad the numeric values with a character in front so that all are of the same 'string' length. As an Example, SELECT TOP 15 * FROM [DBO] . [EMP_CODES] ORDER BY CASE ISNUMERIC ( EMP_CODE )     WHEN 1 THEN REPLICATE ( '0' , 100 - LEN ( LTRIM ( RTRIM ( EMP_CODE )))) + LTRIM ( RTRIM ( EMP_CODE ))     ELSE LTRIM ( RTRIM ( EMP_CODE )) END Second possible solution As an Example, SELECT TOP 15 * FROM [DBO] . [EMP_CODES] ORDER BY CASE WHEN ISNUMERIC ( EMP_CODE ) = 1 THEN RIGHT( REPLICATE ( '0' , 21 ) + LTRIM ( RTRIM ( EMP_CODE )), 20 )      WHEN ISNUMERIC ( EMP_CODE ) = 0 THEN LEFT(...
Read more

SQL Server Page Life Expectancy (PLE)

SQL Server Page Life Expectancy (PLE) Counter key performance indicator for those DBAs looking at the overall health of their database instance. Page life expectancy (PLE) -Indicates the number of seconds a page will stay in the buffer pool without references. The recommended value of the PLE counter is (updated: minimum of) 300 seconds. I have seen on busy system this value to be as low as even 45 seconds and on unused system as high as 1250 seconds. Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references. In simple words, if your page stays longer in the buffer pool (area of the memory cache) your PLE is higher, leading to higher performance as every time request comes there are chances it may find its data in the cache itself instead of going to the hard drive to read the data. Some Important point to know about PLE, If you have a server with 64 GB of memory with 56 GB allocated to SQL Server, which means you’re reading abou...
Read more

What's new in SQL Server 2019?

Try SQL Server 2019! https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2019-ctp 1.       Added Big Data Clusters - We can Deploy a Big Data cluster with SQL and Spark Linux and we can access big data from HDFS 2.       Now, support for persistent memory devices 3.       Now, support for column-store statistics in DBCC CLONEDATABASE 4.       Now added new Poly-base connectors 5.       Now certificate management in SQL Server Configuration Manager - SSL and TLS certificates are widely used to secure access to SQL Server instances 6.       Added UTF-8 support Database engine, SQL Graph features and Intelligent query processing 7.       The latest SSMS version supports choice of UTF-8 enabled collations in the UI. 8.       Added a new options - sp_estimat...
Read more