VIBE CODED

One Statement for INSERT, UPDATE, DELETE In earlier versions of SQL Server we had to write separate statements for Insert,update and Delete based on certain conditions but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query. Syntax of MERGE statement is as following: MERGE [ TOP ( expression ) [ PERCENT ] ] [ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias] USING ON [ WHEN MATCHED [ AND ] THEN ] [ WHEN NOT MATCHED [ BY TARGET...

Run the below query SELECT T.NAME AS TABLE_NAME,SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME,C.NAME AS COLUMN_NAME FROM SYS.TABLES AS T INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID WHERE C.NAME LIKE '%User%' ORDER BY SCHEMA_NAME, TABLE_NAME; Output:

TOGGLE A  BREAK  POINT ◦ Press F9 . To   open  the breakpoints window ◦ Press CTRL + ALT_B To RUN the query/execute the store procedure ◦ Press ALT + F5 Sample break point example below: Move from one break point to another: Right click on the query window and select ‘Run To Cursor’

OVER OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, this query: SELECT SUM(Cost) OVER () AS Cost , OrderNum FROM Orders Will return something like this: Cost  OrderNum 10.00 345 10.00 346 10.00 347 10.00 348 Quick translation: SUM(cost) – get me the sum of the COST column OVER – for the set of rows…. () – …that encompasses the entire result set. OVER(PARTITION BY) OVER, as used in our previous example, exposes the entire resultset to the aggregation…”Cost” was the sum of all [Cost]  in the resultset.  We can  break up  that resultset into partitions with the use of PARTITION BY: SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost , OrderNum , CustomerNo FROM Orders My partition is by  CustomerNo  – each “window” of a single customer’s orders will be treated separately from each other “window...

Goto Sql Server Management Studio. Tools - >Sql Server Profiler Click on "OK" and enjoy.

Move content to right  CTRL +A & TAB Move content to left  CTRL +A & SHIFT + TAB Convert text to UPPER CASE in sql editor select the content and press CTRL+SHIFT+U Convert text to LOWER CASE in sql editor select the content and press  CTRL+SHIFT+L . . . . . . . . . . .

Run the below SQL Query to get the procedure information in a database. select * from INformation_schema.routines

Never  might sound like a long time, but prefixing your procedure names with sp_ causes a performance penalty if the procedures exist in a database other than master. Don't do it. The example that  Listing 1  shows illustrates why you should never prefix procedures with sp_ if you intend to use them in a high-volume transaction-processing environment while maintaining the best possible performance. The code in Listing 1 creates two test procedures in tempdb. I named the first procedure Select1 and the second procedure sp_Select1. The procedures run an identical command, SELECT 1, which is the simplest SELECT statement imaginable. Run each of the procedures once, as  Listing 2  shows, to ensure that SQL Server has compiled the procedure plans for each procedure and has cached them in memory. Then, proceed through the following steps to see the performance implication of prefixing procedures with sp_. Start SQL Server Profiler and connect to your server. ...

1. Range of Dates A DateTime can range from January 1, 1753 to December 31, 9999. A SmallDateTime can range from January 1, 1900 to June 6, 2079. 2. Accuracy DateTime is accurate to three-hundredths of a second. SmallDateTime is accurate to one minute. 3. Size DateTime takes up 8 bytes of storage space. SmallDateTime takes up 4 bytes of storage space. Armed with this knowledge, you may want to use SmallDateTime instead of DateTime if you only need to represent dates from January 1, 1900 to June 6, 2079 and you do not need accuracy below 1 minute. Why? Simple! Using SmallDateTime will reduce the amount of data your queries are pulling back. The size of each row will be a bit smaller.

TIP: Performance Tuning Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields. For example, based on the example above, the following syntax would result in better performance: SELECT department, COUNT(1) as "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department; Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.