SQL Tips and Tricks Archives - Page 188 of 536 - SQL Authority with Pinal Dave

MySQL – How to do Natural Join in MySQL? – A Key Difference Between Inner Join and Natural Join

March 10, 2014

As you know MySQL supports ANSI JOINs like INNER JOIN, OUTER JOIN, CROSS JOIN etc. Do you know that it supports NATURAL JOIN?. A NATURAL JOIN is a type of JOIN which automatically maps the similar columns from both the tables. Let us create the following tables CREATE TABLE items(item_id…

MySQL – Reset Row Number for Each Group – Partition By Row Number

March 9, 2014

Pinal Dave

SQL, SQL Tips and Tricks

MySQL, Ranking Functions

In earlier post, we have learnt how to generate row number for each row using a variable as MySQL does not have any system function like SQLServer’s row_number() to generate the row number. In this post we will see how to reset row number for each group (Alternate method for…

SQL SERVER – Retrieve Maximum Length of Object Name with sp_server_info

March 7, 2014

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Stored Procedure

A very common question which I often receive is – “How big my table name can be?” The answer is simple 128. You can figure this out by executing stored procedure sp_server_info on SQL Server. Execute following stored procedure in SQL Server. EXEC sp_server_info It will return following resultset. attribute_id…

SQL SERVER – How to Synchronize Data Across Databases and Servers

March 4, 2014

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Utility

If you are developer you must have faced situations which I am going to mention next. A Developer’s Wish Have you ever built a database application where you are driving your navigation with the help of Data? Have you ever realized that you are running query much faster on your…

SQL SERVER – Images and media file management in SQL Server and MySQL – Coding Media Management Tools is Never Easy

March 3, 2014

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

MySQL

A core element to both web and mobile app development is rich media management. How you approach the control of images in particular can be tricky. In most cases you have two avenues: You either save it as a database BLOB or simply link to it as a file. Going…

SQL SERVER – Listing Primary Key of Table with Stored Procedure sp_pkeys

March 2, 2014

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Constraint and Keys

Here is a follow up blog post of my earlier blog post on very similar subject Listing Foreign Key Relationships of Table with Stored Procedure sp_fkeys. In previous blog post we listed foreign keys and now we will list the primary key of the table with the help of Stored…

SQL SERVER – Listing Foreign Key Relationships of Table with Stored Procedure sp_fkeys

March 1, 2014

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Constraint and Keys

A very cool trick which I have previously not shared on my blog regarding how to list all the foreign key relationship of table with the help of Stored Procedure sp_fkeys. Here is a quick script how you can use stored procedure sp_fkeys to list all the foreign key relationship.…