Pages

Social Icons

Wednesday, 11 December 2013

SQL-Server: modify, exist in xml

Hi,
In sql-server we have modify,exist and delete functions to change the xml data. Below is the example of replacing the xml value when it is having some specified value.

Example: I want to change the Node A value only when it is having 1 as a value 


declare @tbl table(col xml)

declare @sql xml ='<SomeNode><A>1</A><B>1</B></SomeNode><SomeNode><A /><B /></SomeNode><SomeNode><A /><B /></SomeNode>'

insert into @tbl
select @sql

select * from @tbl

update @tbl
set    col.modify('replace value of (/SomeNode/A[. = "1"]/text())[1] 
                       with ""')
where  col.exist('/SomeNode[A = "1"]') = 1 and
       col.exist('/SomeNode[A = "1"]') = 1


select * from @tbl

Thx,
RS
11/12/13

Monday, 25 November 2013

Sorting comma separated values

Hi,

Below code will sort the comma separated values.

for using this code we need to use fnSplit method.

DECLARE @list VARCHAR(100)
set @list = '11,25,20,95,42,35,61,70,101,95,96,28';
WITH ComaSeparated AS
(
SELECT  *
FROM    dbo.fnSplit(@list,',')
)

SELECT TOP 1 SUBSTRING((SELECT ',' + item 
FROM ComaSeparated t2 
ORDER BY CAST(item AS INT) 
FOR XML PATH('')),2,LEN(@list)+1) FROM ComaSeparated

Thursday, 17 October 2013

Helpful Queries

Hi,

There are some queries which will be help full many times 

Below query will give the list of dependent objects on a table 

SELECT DISTINCT
SYSOBJECTS.NAME 'TABLE NAME',
PROCEDURES.NAME 'STORED PROCEDURE'
FROM SYSOBJECTS
JOIN (SYSOBJECTS PROCEDURES
JOIN SYSDEPENDS
ON PROCEDURES.ID = SYSDEPENDS.ID)
ON SYSDEPENDS.DEPID = SYSOBJECTS.ID
WHERE SYSOBJECTS.NAME = 'TABLE NAME'

Below query will help to find the which record is locked
        
SELECT [Table Name] FROM [Table Name] WITH(NOLOCK)
WHERE ID NOT IN
(
    SELECT ID FROM [Table Name] WITH(READPAST)
)

Below query will help to find who is looged in into database
          
SELECT host_name,session_id,login_time
FROM sys.dm_exec_sessions a
inner join sysdatabases b on a.database_id = b.dbid
where name = 'database name'

Below query will help to find in how many tables particular column is used.
     
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 '%ColumnName%' and SCHEMA_NAME(schema_id) = 'Schema Name' ORDER BY schema_name, table_name;

Below query will give us all the database name, creation date and file path in SQL-Server.
     
SELECT
name AS DBName,
crdate AS [Creation Date],
[filename] AS [File Path]
FROM
SYSDATABASES

Wednesday, 26 June 2013

Querying Microsoft SQL Server 2012 - 70461 Study Material


Create database objects (24%)


Create and alter tables using T-SQL syntax (simple statements)
  • Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE


Create and alter views (simple statements)
  • Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP


Design views
  • Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications


Create and modify constraints (simple statements)
  • Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints


Create and alter DML triggers.
  • Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers


Preparation resources


Work with data (27%)

Query data by using SELECT statements
  • Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce

Implement sub-queries
  • Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement

Implement data types
  • Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns

Implement aggregate queries
  • New analytic functions; grouping sets; spatial aggregates; apply ranking functions

Query and manage XML data
  • Understand XML datatypes and their schemas and interop w/, limitations and restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML; XML indexing

Preparation resources


Modify data (24%)

Create and alter stored procedures (simple statements)
  • Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL

Modify data by using INSERT, UPDATE, and DELETE statements
  • Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement

Combine datasets
  • Difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements

Work with functions
  • Understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)

Preparation resources


Troubleshoot and optimize (25%)

Optimize queries
  • Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in

Manage transactions
  • Mark a transaction; understand begin tran, commit, and rollback; implicit vs explicit transactions; isolation levels; scope and type of locks; trancount

Evaluate the use of row-based operations vs. set-based operations
  • When to use cursors; impact of scalar UDFs; combine multiple DML operations

Implement error handling
  • Implement try/catch/throw; use set based rather than row based logic; transaction management

Preparation resources

Saturday, 4 May 2013

Date Puzzles...


Hi,

Some time we need to find different dates of the months. Here are some examples..

Date on last Tuesday

Everyday of the week can be identified as a number i.e. Sunday is 1, Monday is 2 and so on

declare   @date datetime
set       @date = getdate() -- You can set any date here 
select    @date - (DatePart(dw, @date)-3)


----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))

Thx,
RS

Monday, 18 March 2013

“Value cannot be null” when i connect SQL Server from SSMS



I got below error while connecting SQL-Server using SSMS from my friend machine.

Error :

Value cannot be null.
Parameter name: viewInfo (Microsoft.SqlServer.Management.SqlStudio.Explorer)

Solution :

Right click SSMS   “run as administrator”   

If the “run as administrator doesn’t solve the problem then go to My Computer > Right Click > Properties and then choose “Advanced”. Then click on “Environment Variables” Now, change TMP and TEMP to valid location. On my machines its “%USERPROFILE%\AppData\Local\Temp” 

Problem Solved

Thursday, 28 February 2013

Do's and Don'ts in SQL?


Do's

• Try to Limit The Result using where clause
• Try to use Default Value for columns 
• Use schema before the table
• Use the most efficient (smallest) data types possible. It is unnecessary and    sometimes dangerous to provide a huge data type when a smaller one will be more than sufficient to optimize your structure
• Use Union instead of OR, OR will not use benefits of index when you use or like below
  SELECT * FROM TABLE WHERE COLUMN1 = 'value1' OR COLUMN2 = 'value1'
  On the other hand, using Union such as this will utilize Indexes.

  SELECT * FROM TABLE WHERE COLUMN1 = 'value1'
  UNION
  SELECT * FROM TABLE WHERE COLUMN2 = 'value2'

Don'ts
• Don't Use SELECT *.
• Don't Use the server side cursor.
• Don't Use Sub queries in JOIN. In Sub-query Inner query is executed for each and every row in Outer query. This can be avoided using JOINS.
• Avoid Not in operator in where 
• Try to avoid NOT operator in SQL
• Avoid to use functions in where clause.
• Some of us might use COUNT operator to determine whether a particular data exist
  SELECT COLUMN FROM TABLE WHERE COUNT(COLUMN) > 0

Similarly, this is very bad query since count will search for all record exist on the table to determine the numeric value of field ‘COLUMN’. The better alternative will be to use the EXIST operator where it will stop once it found the first record. Hence, it exist.
• Try to avoid Wildcard character in your select query

Wednesday, 13 February 2013

SQL-Server :Script to see from how long database is not used



Hi,

Below script will help to find that from how long the database is not used.

SELECT ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases WHERE name='tempdb'), D.name,ds.last_read,ds.last_write FROM sys.databases d
INNER JOIN  (SELECT database_id,MAX(last_read) AS last_read,MAX(last_write) AS last_write FROM 
(SELECT database_id,last_user_seek AS last_read, NULL AS last_write FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT database_id,last_user_scan, NULL FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT database_id,last_user_lookup, NULL FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT database_id,NULL, last_user_update FROM sys.dm_db_index_usage_stats) A
GROUP BY database_id) ds
ON d.database_id = ds.database_id
ORDER BY name

Thx,
RS

Wednesday, 30 January 2013

Find a string in whole database of SQL-Server

Hi,

I want to search a string in my database, Is there any way to do this?

Ans : yes, we can do this using Red Gate Search tool.

Can we do the same using SQL Query?

Now this is a tricky one but we can do this using a simple SQL-Query.


SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS ObjectName, obj.type, obj.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS obj ON sm.object_id = obj.object_id
WHERE sm.definition LIKE '%YOUR_WORD%'

Thx,
RS


Monday, 28 January 2013

Difference between SQL-Server 2005/2008/2012

Difference between SQL-Server 2005 and SQL-Server 2008

1. In sql server 2005 we can not encrypt the database, while in sql server 2008 we can encrypt the entire database.

2. No table datatype is in sql-server 2005.

3. Central management system is introduced in 2008.

4. Policy based management(PBM) server is Introduced in sql 2008.

5. In sql server 2005,There is no option to compress backup files, but in sql server 2008.

6. Merge statement is introduced in sqlsever 2008

7. Value assignment at the time of vaiable declaration in SQL 2008

8. Ability to pass table variables into the stored procedures in SQL 2008


Difference between SQL-Server 2008 and SQL-Server 2012


1. The Maximum number concurrent connections to SQL Server 2008 is 32767. SQL server 2012 has unlimited concurrent connections.

2. The SQL Server 2008 uses 27 bit precision for spatial calculations. The SQL Server 2012 uses 48 bit precision for spatial calculations

3. TRY_CONVERT() and FORMAT() functions are not available in SQL Server 2008. 

4. ORDER BY Clause now have OFFSET / FETCH options to use paging to show required rows per page in applications and allow the user to scroll through each page of results rather than download the entire setIn the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time. 

SELECT BusinessEntityID, FirstName, LastName FROM Person.Perso ORDER BY BusinessEntityID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

5. Sequence is included in SQL Server 2012.Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence.

/****** Create Sequence Object ******/
CREATE SEQUENCE MySequence
START WITH 1
INCREMENT BY 1;

/****** Create Temp Table ******/
DECLARE @Person TABLE
(
ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL
);
/****** Insert Some Data ******/
INSERT @Person (ID, FullName) VALUES (NEXT VALUE FOR MySequence, 'Umar Ali'),
(NEXT VALUE FOR MySequence, 'John Peter'),
(NEXT VALUE FOR MySequence, 'Mohamed Iqbal');

/****** Show the Data ******/
SELECT * FROM @Person;

The results would look like this:
ID FullName
1 Umar Ali
2 John Peter
3 Mohamed Iqbal

6. The Full Text Search in SQL Server 2012 has been enhanced by allowing us to search and index data stored inextended properties or metadata. Consider a PDF document that has "properties" filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but it's possible now.

Tuesday, 22 January 2013

Hi,

In SQL query we say avoid to use of NOT Exist this is because of if we have index on our table then because of NOT Exist our query will not take the benefit of index. We can avoid the use of not exist using the below query.


In the above image i have shown the example of NOT EXIST and How to replace the NOT EXIST with LEFT JOIN.

Thanks,
Rahul



Friday, 18 January 2013

Remove splash screen of visual studio


Hi,

We can remove the splash screen of visual studio and can save 5 seconds each time when we open visual studio. For this we need to do only some things.

1. Click on Start>Right Click on visual studio icon>Click Properties.


2. In properties window go on target text-box, in the end type " -NOSPLASH"


3. Now hit the Apply and Ok button.

Thanks,
RS

Tuesday, 15 January 2013

SQL Server : Script for finding which variable is declared but never used


Hi,

Below query will help to get the list of all the variable which are declared in your sql code but never used.


-- Variable declared but never used Script
-- Original Author : Rahul Singi
DECLARE @str AS VARCHAR(MAX)=''
DECLARE @chrFound AS VARCHAR(255)
DECLARE @i AS INT=0
DECLARE @temp AS TABLE(colm1 VARCHAR(255))

SET @str=LOWER(@str)

DECLARE @NewLine AS CHAR(2)

SET @NewLine=char(13)+char(10)

WHILE @i<len(@str)
BEGIN
    IF((CHARINDEX('@',@str,@i)=0) OR (CHARINDEX(' ',@str,CHARINDEX('@',@str,@i))-CHARINDEX('@',@str,@i))<0)
    BEGIN
        BREAK;
    END
    
    SELECT @chrFound=SUBSTRING(@str,CHARINDEX('@',@str,@i),CHARINDEX(' ',@str,CHARINDEX('@',@str,@i))-CHARINDEX('@',@str,@i))
    
    INSERT INTO @temp
    SELECT rtrim(ltrim(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@chrFound,'!',''),'#',''),'$',''),'&',''),'=',''),')',''),';',''),@NewLine,'')))
    WHERE @chrFound NOT IN ('@@ERROR','@@IDENTITY','@@PACK_RECEIVED','@@ROWCOUNT','@@TRANCOUNT')
    
    SET @i=CHARINDEX(' ',@str,CHARINDEX('@',@str,@i))
    
    IF(CHARINDEX('@',@str,@i)=0)
    BEGIN
        BREAK;
    END
    
    PRINT(@i)
END

UPDATE @temp SET colm1=substring(colm1,0,charindex(char(9),colm1,0))
WHERE charindex(char(9),colm1,0)>0

SELECT colm1,'Variable declared but never used' AS Comment
FROM @temp
WHERE colm1 NOT IN(SELECT colm1
                   FROM @temp
                   GROUP BY colm1
                   HAVING COUNT(colm1)>1)



In the @str variable you need to give your sql code

Thx,
RS

Wednesday, 9 January 2013

Create minified version of java-script file


using System;
using System.IO;
using Yahoo.Yui.Compressor;
using System.Xml;
using System.Text;
using System.Linq;
using System.Collections.Generic;

namespace LCCA.Clinical.Tools
{
    public class Program
    {
        static void Main()
        {
            //Replace the project folder path as per your local environment
            const string projectDir = @"Paths"; // Local path where JavaScript files are stored in your machine
            const string operation = "mergejs";

            if (operation.ToLower().Equals("mergejs"))
            {
                CompressJS(projectDir);
                MergeJS(projectDir);
            }
            else if (operation.ToLower().Equals("compresscss"))
            {
                CompressCSS(projectDir);
            }
            else if (operation.ToLower().Equals("compressjs"))
            {
                CompressJS(projectDir);
            }
            else if (operation.ToLower().Equals("all"))
            {
                CompressCSS(projectDir);
                CompressJS(projectDir);
                MergeJS(projectDir);
            }
        }

        private static void MergeJS(string projectDir)
        {
            string scriptCatalog = Path.Combine(projectDir, "Scripts\\script-catalog.xml");
            string mergedFolderPath = Path.Combine(projectDir, "Scripts\\merged");
            string scriptsFolder = Path.Combine(projectDir, "Scripts");

            IList<string> scriptFiles =
                Directory.GetFiles(scriptsFolder, "*.js", SearchOption.AllDirectories).ToListIfNotNullOrEmpty();

            string content = File.ReadAllText(scriptCatalog);
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(content);

            XmlNodeList files = doc.SelectNodes("//files/file");

            foreach (XmlElement file in files)
            {
                StringBuilder sb = new StringBuilder();

                XmlNodeList references = file.SelectNodes("reference");
                string targetFilePath = Path.Combine(mergedFolderPath, file.Attributes["name"].Value);
                foreach (XmlElement reference in references)
                {
                    string referenceFileName = reference.Attributes["name"].Value;

                    string referenceFilePath = scriptFiles.Single(x => new FileInfo(x).Name.Equals(referenceFileName));

                    string referenceContent = File.ReadAllText(referenceFilePath);

                    sb.AppendLine("//" + referenceFileName);
                    sb.AppendLine();
                    sb.AppendLine(referenceContent);
                    sb.AppendLine();
                }

                File.WriteAllText(targetFilePath, sb.ToString());
            }
        }

        private static void CompressJS(string projectDir)
        {
            string jsSourceFolder = Path.Combine(projectDir, "Scripts\\custom\\debug");
            string jsTargetFolder = Path.Combine(projectDir, "Scripts\\custom");
            CompressJS(jsSourceFolder, jsTargetFolder);
        }

        private static void CompressJS(string sourceFolder, string targetFolder)
        {
            foreach (string sourceFilePath in Directory.GetFiles(sourceFolder))
            {
                string sourceFilePathWithoutExtn = Path.GetFileNameWithoutExtension(sourceFilePath);
                string targetFilePath = Path.Combine(targetFolder, sourceFilePathWithoutExtn + ".min.js");
                if (new FileInfo(sourceFilePath).Extension.EqualsIgnoreCase(".js"))
                {
                    string content = File.ReadAllText(sourceFilePath);
                    string compressedContent = JavaScriptCompressor.Compress(content, true, true, false, false, 73);
                    File.WriteAllText(targetFilePath, compressedContent);
                }          
            }
        }

        private static void CompressCSS(string projectDir)
        {
            //First Run
            string cssSourceFolder = Path.Combine(projectDir, "Content\\css");
            string cssTargetFolder = Path.Combine(projectDir, "Content\\css");
            CompressCSS(cssSourceFolder, cssTargetFolder);

            //Second Run
            cssSourceFolder = Path.Combine(projectDir, "Content");
            cssTargetFolder = Path.Combine(projectDir, "Content");
            CompressCSS(cssSourceFolder, cssTargetFolder);
        }

        private static void CompressCSS(string sourceFolder, string targetFolder)
        {
            foreach (string sourceFilePath in Directory.GetFiles(sourceFolder))
            {
                if (new FileInfo(sourceFilePath).Extension.EqualsIgnoreCase(".css"))
                {
                    string sourceFilePathWithoutExtn = Path.GetFileNameWithoutExtension(sourceFilePath);
                    string targetFilePath = Path.Combine(targetFolder, sourceFilePathWithoutExtn + ".css");
                    string content = File.ReadAllText(sourceFilePath);
                    string compressedContent = CssCompressor.Compress(content, 73, CssCompressionType.StockYuiCompressor);
                    File.WriteAllText(targetFilePath, compressedContent);
                }
            }
        }
    }
}

SSRS Interview Questions II

1. What does rdl stand for?
    RDL stand for Report Definition Language. RDL is the extension for reports file.

2. What is Report Manager?
    Report Manager is a web based tool that allows to access and run reports.

3. What is Report Builder?
    Report Builder is a self-service tool for end users

4. What permission do you need to give to users to enable them to use Report Builder?
    "Report Builder" role and "system user". Report builder should also be enable in report server properties

5. What do you need to restore report server database on another machine?
    SSRS Encryption key

6. How to pass multi-valued parameter to stored procedure in dataset?
    Join function in SSRS and split function in T-SQL

7. How to create "dependent" parameter "Make, Model, Year"
    They need to be in correct order, and previous parameter should filter next parameter dataset. For instance Model dataset should be filtered using Make parameter

8. How to create "alternate row colour"?
    IIF(ROWNUMBER(NOTHING)%2=0,"White","White Smoke")

9. How to create percentile function?
    Custom code is required for this
    Code : 
       Public Function Percent(ByVal CurrVal As Long, ByVal MaxVal As Long) As Decimal
                 If CurrVal = 0  or MaxVal = 0 Then
                       Return 0
                 Else
                       Return (CurrVal / MaxVal * 100)
                 End If
        End Function

10. How to find a value in another dataset based on current dataset field (SSRS 2008 R2)?
      using lookup function
      Example : =JOIN(LookupSet(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "Departments"),","

11. How to identify current user in SSRS Report?
      User!UserID

12. What is the shared data source in ssrs?
      A shared data source is a set of data source connection properties that can be referenced by multiple reports, models, and data-driven subscriptions that run on a Reporting Services report server. Shared data sources provide an easy way to manage data source properties that often change over time. If a user account or password changes, or if you move the database to a different server, you can update the connection information in one place.
Shared data sources are optional for reports and data-driven subscriptions, but required for report models. If you plan to use report models for ad hoc reporting, you must create and maintain a shared data source item to provide connection information to the model.
13. What is the report rendering?
     Report rendering is to call the report from server to application. Report rendering can be possible through different ways.