The Young DBA

This is the new function introduced with 2016 version of SQL Server. This is used to cast value from its existing data type to a specified target data type. This occurs only if the operation is successful. It returns NULL if the conversion fails. TRY_CAST is an extended version of the CAST function. Query SELECT CAST ( 'Tech-Recipes' as INT ) as CastExample ; result Msg 245 , Level 16 , State 1 , Line 2 Conversion failed when converting the varchar value 'Tech-Recipes' to data type int . query SELECT CAST ( '12' as INT ) as CastExample ; result 12 SELECT TRY_CAST ( 'Tech-Recipes' as INT ) as CastExample ; Result NULL SELECT TRY_CAST ( '20' as INT ) as CastExample ; Result 20

This is new built-in table-valued function introduced in sql server 2016. STRING_SPLIT that splits the provided input string by a specified separation character and returns the output separated values in the form of table. This is usefull-function that you might always use userdefined long and unoptimized function in most applications. Now sql server have built in function to split the text. Query SELECT * FROM STRING_SPLIT('Suman,Satyam,Shivam',',') Result Suman Satyam Shivam Thanks for reading Plz dont forget to like Facebook Page.. https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

After-a-long time I got chance to work on MS-Access. MS-Access is very intresting database-application development tool. I like ms access a lot  becouse, this is the tool that helped me to turn into database in 2009.  Microsoft Access is a popular database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical  user interface and software-development tools. Microsoft Access stores data in its own format based on the Access Jet Database Engine.  It can also import or link directly to data stored in other applications and databases. Now a days there are lots of changes in Access but basics are still the same.

When we work with a single table in SQL Server, it is very easy to perform audit queries. But, while dealing with multiple tables of the same database, it becomes a very tedious job for new database developers. We need data from multiple sources(for example : all tables of a single database). As a newcomer to this field, i too got stuck in the same kind of queries, so, I have searched the web and have listed down a few queries that can help in retrieving data through audit queries. 

Performance of query execution (stored Procedure or Ad-hoc query) is depends on Execution Plan and execution plans depend on Statistics-histogram . We already know Query Statistics is metadata of database engine which depend on data. Every insert-update-delete changes its stats. SQL server allowing us to export and copy to another server. So we can use it to sync the performance of server during quality or testing of procedures and queries. Step Connect to Production Server and right click on database. In the list just choose Generate Script. Now click on next and Choose Advance option button. Then select script statistics and then Next and Finish. This will generate Script just copy these script and run on Quality server/Testing Server Database.   Happy Reading...

Not always but I observed simple join is much better than Correlated sub-query. So plz avoid to use it, if we have option than .... today i am sharing how a simple join is better than correlated sub query. . correlated sub-query in columns select   candidate_id,last_name,first_name,client_id, (select name from client_master where client_id=candidate_master.client_id) as cl_name  from candidate_master

When we execute query select * from sys . dm_exec_query_stats it will show aggregate performance statistics of cached query plans of SQL Server. The details including one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. Details have two column SQL Handle and Plan Handle.

Hi friend,   These days I become little bit Lazy. The reason was my long leave from work. I visited my Hometown in Durga Puja. It was really very cheerful moment for me to meeting with parents and cousins. Please  don’t ask about my last 7 Days I was suffered with Eye-Flue. It isolated me becouse I was not  unable to use TV/Computer/Phone/Book, social media and SQL. After 6 days  now i become OK. Session of Diwali festival also comes up.  So I should wake up and write some Diwali special Query.  I started my SQL Server, opened SSMS and New Query Editor new problem ….. How to generate Running Average of Last two days?

This summary is not available. Please click here to view the post.

Hi friends, This is my post on Backup-Restore on Network Shared drive. After 2 days holidays coming back on job is difficult task and today I am not well due to cold. I did some wrong with myself eaten sweets more and after that cold water. SO my throat is not working properly. Actually Every Enterprise is facing with Crunch of resources for running application and keeping backups. Sometime we require to restore database and we have no more space on local server drive. We all facing in our life as a SQL-DBA. If you have space on Shared drive then we can use it for our backup-restore task test activity.  I am sharing you how to do on Network shared drive below tasks but be sure SQL have proper access on such drive or shares :-

Source : Technet

An 'Inner Join' is used to join operation in an applications, which creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of A and B are combined into a result row. We can create inner Join through various method in application. I want to share 3 ways which I used in various application. Implicit Join Method Explicit Join Method Cross Apply Method

By default the no count setting will be OFF in sql server for any procedure execution. What is happening when we execute any procedure , Sql server connect and send back information to client and client to server. This allows client to show the record set along with message in different tab but this message transferring activity creates an extra over-head on network. We can reduce this overhead by turning   ON to no count so the procedure will get much better performance in execution.

Backup-Restore msdb..sp_delete_backuphistory '1-Jan-2005' msdb..logmarkhistory msdb..backupset msdb..backupfile msdb..backupmediaset msdb..backupmediafamily msdb..restorefile msdb..restorefilegroup msdb..restorehistory Index Related sys.indexes sys.dm_db_index_usag sys.Indexes sys.dm_db_index_physical_stats sys.dm_db_index_usage_stats sys.index_columns SQL Server Operating System sys.dm_os_sys_info sys.dm_os_performance_counters sys.dm_exec_requests sys.dm_exec_sessions Master..sysprocesses sys.dm_exec_connections sys.dm_os_waiting_tasks sys.dm_os_wait_stats sys.dm_os_nodes       SQL Server Agent Tables dbo.sysjobactivity dbo.sysjobhistory dbo.sysjobs dbo.sysjobschedules dbo.sysjobservers dbo.sysjobsteps dbo.sysjobste

It was very tricky question asked to me during one of DBA session and that time my answer was no Idea, but we can do this very easily with select command. TSQL select * from into ZFI_TAB_4_DAYBUK2 SAP .. MYAC . ZFI_TAB_4_DAYBUK where 1 = 2 ZFI_TAB_4_DAYBUK2 is new table ZFI_TAB_4_DAYBUK  is a source table We can also use SET FMTONLY to view metdata of table or query returns. SET FMTONLY ON ; GO SELECT      BUKRS , BELNR , BUZEI , GJAHR , BLART , LTEXT , CONVERT ( datetime ,LEFT( BUDAT , 4 )+ SUBSTRING ( BUDAT , 5 , 2 )+RIGHT( BUDAT , 2 )) as BUDAT , CONVERT ( datetime ,LEFT( BLDAT , 4 )+ SUBSTRING ( BLDAT , 5 , 2 )+RIGHT( BLDAT , 2 )) as BLDAT , BSCHL , GSBER , GTEXT , UMSKZ , DEB_CRE , DMBTR , WAERS , ACC_TYPE , SGTXT , SAKNR , TXT50 , KUNNR , CUST , LIFNR , VEND , XBLNR , TXT60 , BUSINESS_AREA , CONVERT ( datetime ,LEFT( CPUDT , 4 )+ SUBSTRING ( CPUDT , 5 , 2 )+RIGHT( CPUDT , 2 )) as CPUDT , VEND_CU