NIHAR'S BLOG

Hi friends , Yesterday I thought about temp. tables in database . After searching over same query , I got through some links regarding "Temp Table " , "global temporary table ", "Local Temp Table", "#"  etc. Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an   additional overhead and can causes performance issues. Number of rows and columns need to be as minimum as needed. Tables need to be deleted when they are done with their work.  Till the date I am using simple method to do so . But this concept will save my time and don't confuse between oracle and sql server. Its different. Concept : Temporary tables in Database Concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside system da...

Step 1:  Improving the ResultSet I had missed including all zeros in my sample set which was an overlook. Here is the new sample which includes all zero values as well. USE tempdb GO -- Create sample table CREATE TABLE Table1 ( Col1 VARCHAR ( 100 )) INSERT INTO Table1 ( Col1 ) SELECT '0001' UNION ALL SELECT '000100' UNION ALL SELECT '100100' UNION ALL SELECT '000 0001' UNION ALL SELECT '00.001' UNION ALL SELECT '01.001' UNION ALL SELECT '0000' Step 2: SELECT CASE PATINDEX ( '%[^0 ]%' , Col1 + ' ‘' ) WHEN 0 THEN '' ELSE SUBSTRING ( Col1 , PATINDEX ( '%[^0 ]%' , Col1 + ' ' ), LEN ( Col1 )) END FROM Table1 See the result.........

Assuming you're joining on columns with no duplicates, which is by far the most common case: An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection. An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union. Examples Suppose you have two Tables, with a single column each, and data as follows: A B - - 1 3 2 4 3 5 4 6 Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B. Inner join An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common. select * from a INNER JOIN b on a . a = b . b ; select a .*, b .* from a , b where a . a = b . b ; a | b --+-- 3 | 3 4 | 4 Left outer join A left outer join will give all rows in A, plus any common rows in B. select * from a LEFT OUTER JOIN b on a . a = b . b ; select a .*, b .* from a , b w...

Step 1:   Create sample table with one of the column defined as primary key constraint   Step 2:   Execute following command : SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM ALL_CONSTRAINTS cons, ALL_CONS_COLUMNS cols WHERE cols.table_name = 'TABLE_NAME' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position; :):)  Enjoy

We used this title for describing technologies , we are working in. Here we will see "  How to store files in oracle database using c# ?   " Step 1:           Use file uploader controller to browse directories. Step 2:          Before uploading file or its path , we need to understand BLOB of Oracle          BLOB  ( Binary Large Objects ) :                                                    File content store in binary format.In Oracle , BLOB and    CLOB  (Character Large Object) used.Technically BLOB prefered.For more info regarding BLOB click here  . ...

Hey some people thinking about its simple retreive data from  query rather than stored procedure. But is more simple and safe to so do changes if required. For that we need to follow steps as follows : [Note : Creating stored proc. and package only through sqlplus] 1)   We need to package of oracle to create it. Packages in oracle syntax :      CREATE OR REPLACE PACKAGE SELECT_EMP_HISTORY AS     TYPE T_CURSOR IS REF CURSOR;     PROCEDURE Sp_Test_Emp_Data     (             p_year IN VARCHAR2,            cur_JobHistory OUT T_CURSOR     );     END SELECT_EMP_HISTORY;    /   Create  Package Body :     CREATE OR REPLACE PACKAGE BODY SELECT_EMP_HISTORY AS      PROCEDURE Sp_Test_Emp_Data     ( ...

HOW TO DISPLAY ALL ROW DATA IN SINGLE STRING : SELECT Stuff(   (SELECT N', ' + Name FROM [TableName]  FOR XML PATH(''),TYPE)   .value('text()[1]','nvarchar(max)'),1,2,N'') Output : ACBCK12..1212sid... EX: SELECT Stuff(   (SELECT N', ' + Name FROM Test FOR XML PATH(''),TYPE)   .value('text()[1]','nvarchar(max)'),1,2,N'') output : Nihar, Rohan, Rohan, , Sw, Sw

COUNT(SomeColumn) will only return the count of rows that contain non-null values for SomeColumn.  COUNT(*) and COUNT('Foo') will return the total number of rows in the table. Other else no such big difference between them.....  

SQL ALTER TABLE Syntax Add a column in a table , use the following syntax:         ALTER TABLE table_name         ADD column_name datatype   Delete a column in a table , use the following syntax (notice that some database systems don't allow deleting a column):          ALTER TABLE table_name         DROP COLUMN column_name   Change the data type of a column in a table , use the following syntax: 1) My SQL / SQL Server / MS Access: ALTER TABLE table_name ALTER COLUMN column_name datatype   2) Oracle: ALTER TABLE table_name MODIFY column_name datatype     You can try it...  Hopes it will helps you