CODING & ANALYTICS by Gbenga Oridupa

Best place for your IT solutions. Email : Gbenga. Unstoppable @gmail.com

Example: You are on a project and you need to automate how you retrieve the last day of the month every time you pull data because you use it almost all the time. This is a quick solution, by using a Scalar-valued function. Run this: USE YourDBName GO Create Function [dbo].[FnGetLastDayOfMonth] -- Input parameters ( @Anydate datetime ) RETURNS datetime AS /******************************************************************** Returns the last day of the month (extracted from the date passed) *********************************************************************/ BEGIN -- add one month to the datepassed SET @Anydate = DATEADD(m,1,@Anydate) RETURN DATEADD(d,-datepart(d,@Anydate),@Anydate) END You can test this in a simple select statement;  SELECT [dbo].[fnGetLastDayOfMonth](GETDATE()) as  LastDayOfTheMonth Here is the result when i ran it;

Example:   Need to do a quick  row count for all the tables in a database . This is a quick way to find the row count for all the tables in a database. The Cursor with Count(*) can be slow as it has to count rows for each of the table. USE YourDBName GO SELECT OBJECT_NAME ( id ) AS TableName ,        rowcnt           AS [RowCount] FROM   sysindexes s        INNER JOIN sys.tables t                ON s.id = t. OBJECT_ID WHERE   s.indid IN ( 0 , 1 , 255 )        AND is_ms_shipped = 0 I ran above query on Test DB and got below information: Fig 1: Get Row Count for all the Tables in SQL Server Database                                                     

  Check this out. Below is the summary of SQL Server 2016 new features.  1          Query Store a)      It contains query work load history . b)      It can enforce polices to direct SQL Server Query processor to execute in a specific     manner c)         Captures queries, query plans, runtime statistics, etc. d)        New system views are added to support Query store e)         Helps system-wide or database level performance analysis and troubleshooting 2        Column Store a)         Column store index on your in-memory table for higher throughput b)       Columnstore indexes achieve up to 10x greater data compression than row store indexes. This greatly reduces the IO required to execute analytics queries and therefore improves query performance 3         Live Query Statistics a)         Live Query plans with system resources consumption such as CPU and memory for all running queries in the plan b)         It helps you watch change of statistics d

Example: Working on a project and needed to get list of all the tables from all the databases with columns and columns demographic. This information can be needed for many reasons. The below script uses cursor to loop through all the databases to get the list of tables with columns. I have ignored the system databases such as master, model, msdb and tempdb. You can filter the databases on which you would like to run the query. ________________________________________________________ USE Master GO --Declare Variables DECLARE @DatabaseName AS VARCHAR(500) --Create Temp Table to Save your Results IF OBJECT_ID('tempdb..#Results') IS NOT NULL     DROP TABLE #Results CREATE TABLE #Results (     ServerName VARCHAR(150)     ,DatabaseName VARCHAR(150)     ,SchemaName VARCHAR(150)     ,TableName VARCHAR(150)     ,ColumnName VARCHAR(150)      ,Data_Type VARCHAR(150)    ,Is_Nullable VARCHAR(25)    ,Character_Maximum_Length VARCHAR(10)     ) DECLARE CUR CURSOR