For Eg., Use Convert function as follow
SELECT CONVERT(VARCHAR(20), GETDATE(), 108) As dtm
Monday, January 9, 2012
DateTime Formats in SQL Server
The conversion of datetime data into many formats is everyday requirement. Here is a table of commonly used formats, to deal with datetime data.
For Eg., Use Convert function as follow
SELECT CONVERT(VARCHAR(20), GETDATE(), 108) As dtm
For Eg., Use Convert function as follow
SELECT CONVERT(VARCHAR(20), GETDATE(), 108) As dtm
SQL Query to select a random row from a table
There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic,but each database server requires different SQL syntax.
Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()
Select a random record with Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1
Select a random row with IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Select a random row with MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
Select a random row with PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()
Select a random record with Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1
Select a random row with IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Select a random row with MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
Select a random row with PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
Microsoft SQL Server 2008 Sys.Objects Catalog View object types
For Eg., Select * From Sys.Objects where type = 'P'
It returns the all the user defined stored procedure in the database.
I have listed below the various objects types of sysojects table.
type='AF' 'Aggregate function (CLR)'
type='C' 'CHECK constraint'
type='D' 'DEFAULT (constraint or stand-alone)'
type='F' 'FOREIGN KEY constraint'
type='FN' 'SQL scalar function'
type='FS' 'Assembly (CLR) scalar-function'
type='FT' 'Assembly (CLR) table-valued function'
type='IF' 'SQL inline table-valued function'
type='IT' 'Internal table'
type='P' 'SQL Stored Procedure'
type='PC' 'Assembly (CLR) stored-procedure'
type='PG' 'Plan guide'
type='PK' 'PRIMARY KEY constraint'
type='R' 'Rule (old-style, stand-alone)'
type='RF' 'Replication-filter-procedure'
type='S' 'System base table'
type='SN' 'Synonym'
type='SQ' 'Service queue'
type='TA' 'Assembly (CLR) DML trigger'
type='TF' 'SQL table-valued-function'
type='TR' 'SQL DML trigger'
type='U' 'Table (user-defined)'
type='UQ' 'UNIQUE constraint'
type='V' 'View'
type='X' 'Extended stored procedure'
It returns the all the user defined stored procedure in the database.
I have listed below the various objects types of sysojects table.
type='AF' 'Aggregate function (CLR)'
type='C' 'CHECK constraint'
type='D' 'DEFAULT (constraint or stand-alone)'
type='F' 'FOREIGN KEY constraint'
type='FN' 'SQL scalar function'
type='FS' 'Assembly (CLR) scalar-function'
type='FT' 'Assembly (CLR) table-valued function'
type='IF' 'SQL inline table-valued function'
type='IT' 'Internal table'
type='P' 'SQL Stored Procedure'
type='PC' 'Assembly (CLR) stored-procedure'
type='PG' 'Plan guide'
type='PK' 'PRIMARY KEY constraint'
type='R' 'Rule (old-style, stand-alone)'
type='RF' 'Replication-filter-procedure'
type='S' 'System base table'
type='SN' 'Synonym'
type='SQ' 'Service queue'
type='TA' 'Assembly (CLR) DML trigger'
type='TF' 'SQL table-valued-function'
type='TR' 'SQL DML trigger'
type='U' 'Table (user-defined)'
type='UQ' 'UNIQUE constraint'
type='V' 'View'
type='X' 'Extended stored procedure'
SSRS Interview Questions
What are sub reports and how to create them?
A sub report is like any other reports which can be called in main report and can be generate
through main report. Parameters can be passed from main report to sub report and basis of
that report can be generated.
What is the report model project?
Report model project is for creating Adhoc reporting. You can create the adhoc reports
through report builder. Report model project can be created on bids or report server. This
model can have simple view.
What is report server project?
Report Server Project contains the RDL file and it need to be deployed on report server to
view the report files to application and user.
It a solution where we design our reports. You can add it by going into BIDS clicking on
new item and then selecting reports server project. Once the solution is created you can start
creating reports.
What is the report builder?
Report builder is used to create small reports and it a define interface. You can’t change the
report interface in report builder it pre designed. You can just drag columns in the report.
Report builder creates reports on database objects available with report model project.
In which SQL Server version report builder introduced?
Report builder introduced in SQL Server 2005. While creating or deploying report model
project on report server you can get error or it might not get created. For this you need to
check whether the service pack 22 is installed or not.
How to deploy the Report?
Report can be deployed in three ways.
1. Using visual studio: In visual studio you can directly deploy the report through
solution explorer by providing the report server URL in project properties at Target
Server URL. This will deploy entire project or single report as per you selection.
2. Using report server: Can directly go to the report server and deploy the report by
browsing the report from the disk location on server.
3. Creating the utility: SQL server provides the utility using that which can be used to
create a customize utility for your report deployment in bulk.
What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It comes with the report
server and can be customize accordingly.
What is the name of reporting services config file and what’s it’s used for?
Reporting service config file is used for report configuration details. It contains the report
format and also the report import types. Report service config reside at ISS.
What are the three different part of RDL file explain them?
In visual studio RDL files has three parts.
1. Data: It contains the dataset on which we write the query. Data set is connected with
data source.
2. Design: In design you can design report. Can create tables and matrix reports. Drag
columns values from source.
3. Preview: to check the preview after the report run.
Which language rdl files made of?
RDL files are written in XML.
What is the chart in report?
Chart reports are for graphical representation. You can get pie charts columns harts and
various other options.
3d charts are also available in reporting services.
What is Data Set in report?
Data set are the set of data which we want to show in report. Data creates on data source.
Data source is the source of data from where we are getting this data i.e. database server and
database name connection string.
What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed below.
1. Microsoft SQL Server
2. OLEDB
3. Oracle
4. ODBC
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
8. Hyperion
9. Teradata
10. XML
What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing this web service you can access all
report server component and also get the report deployed on report server.
How to add the custom code in Report?
To add the custom codes in report go to report tab on top then properties and there you will
find the options for custom code.
A sub report is like any other reports which can be called in main report and can be generate
through main report. Parameters can be passed from main report to sub report and basis of
that report can be generated.
What is the report model project?
Report model project is for creating Adhoc reporting. You can create the adhoc reports
through report builder. Report model project can be created on bids or report server. This
model can have simple view.
What is report server project?
Report Server Project contains the RDL file and it need to be deployed on report server to
view the report files to application and user.
It a solution where we design our reports. You can add it by going into BIDS clicking on
new item and then selecting reports server project. Once the solution is created you can start
creating reports.
What is the report builder?
Report builder is used to create small reports and it a define interface. You can’t change the
report interface in report builder it pre designed. You can just drag columns in the report.
Report builder creates reports on database objects available with report model project.
In which SQL Server version report builder introduced?
Report builder introduced in SQL Server 2005. While creating or deploying report model
project on report server you can get error or it might not get created. For this you need to
check whether the service pack 22 is installed or not.
How to deploy the Report?
Report can be deployed in three ways.
1. Using visual studio: In visual studio you can directly deploy the report through
solution explorer by providing the report server URL in project properties at Target
Server URL. This will deploy entire project or single report as per you selection.
2. Using report server: Can directly go to the report server and deploy the report by
browsing the report from the disk location on server.
3. Creating the utility: SQL server provides the utility using that which can be used to
create a customize utility for your report deployment in bulk.
What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It comes with the report
server and can be customize accordingly.
What is the name of reporting services config file and what’s it’s used for?
Reporting service config file is used for report configuration details. It contains the report
format and also the report import types. Report service config reside at ISS.
What are the three different part of RDL file explain them?
In visual studio RDL files has three parts.
1. Data: It contains the dataset on which we write the query. Data set is connected with
data source.
2. Design: In design you can design report. Can create tables and matrix reports. Drag
columns values from source.
3. Preview: to check the preview after the report run.
Which language rdl files made of?
RDL files are written in XML.
What is the chart in report?
Chart reports are for graphical representation. You can get pie charts columns harts and
various other options.
3d charts are also available in reporting services.
What is Data Set in report?
Data set are the set of data which we want to show in report. Data creates on data source.
Data source is the source of data from where we are getting this data i.e. database server and
database name connection string.
What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed below.
1. Microsoft SQL Server
2. OLEDB
3. Oracle
4. ODBC
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
8. Hyperion
9. Teradata
10. XML
What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing this web service you can access all
report server component and also get the report deployed on report server.
How to add the custom code in Report?
To add the custom codes in report go to report tab on top then properties and there you will
find the options for custom code.
How to find the SQL Server version?
The Microsoft SQL Server T-SQL system variable @@version contains the version signature of the server and also SERVERPROPERTY provides specific information.
USE master;
GO
Select @@Version As SQLServerVersion
GO
SELECT
'Microsoft SQL Server ' +
convert(varchar, SERVERPROPERTY('ProductVersion') ) + ' -- ' +
convert(varchar, SERVERPROPERTY('ProductLevel') ) + ' -- ' +
convert(varchar, SERVERPROPERTY('Edition') ) As SQLServerVersion;
USE master;
GO
Select @@Version As SQLServerVersion
GO
SELECT
'Microsoft SQL Server ' +
convert(varchar, SERVERPROPERTY('ProductVersion') ) + ' -- ' +
convert(varchar, SERVERPROPERTY('ProductLevel') ) + ' -- ' +
convert(varchar, SERVERPROPERTY('Edition') ) As SQLServerVersion;
How to import and export image/file column data in SQL Server?
Several methods are available for importing & exporting images / photos / videos / documents / BLOBs.
Here i have used "OPENROWSET" for importing image to database and BCP command (Bulk copy) for exporting. We can also use BCP command for import images/documents.
VARBINARY(MAX) Datatype using for store images that consisting approximately 2 GB.
USE AdventureWorks;
GO
-- Create image warehouse for importing image into sql server database
CREATE TABLE dbo.PhotoLibrary
(
PhotoLibraryID INT IDENTITY ( 1 , 1 ) PRIMARY KEY,
ImageName VARCHAR(100),
Photo VARBINARY(MAX)
)
GO
-- SQL Server import image - storing images to sql server database
INSERT INTO dbo.PhotoLibrary
([ImageName])
VALUES ('nature.JPG')
GO
UPDATE dbo.PhotoLibrary
SET Photo = (SELECT *
FROM OPENROWSET(BULK 'D:\image\photo\nature.JPG',
SINGLE_BLOB) AS x)
WHERE [ImageName] = 'nature.JPG'
GO
-- Check table population
SELECT *
FROM dbo.PhotoLibrary
GO
-- SQL Server export image - Using BCP Command (Bulk Copy)
DECLARE @Command NVARCHAR(4000)
-- Keep the command on ONE LINE - SINGLE LINE!!!
SET @Command = 'bcp "SELECT Photo FROM AdventureWorks.dbo.PhotoLibrary" queryout "D:\image\photo\natureNew.jpg" -T -n -S <Server name\Instance name>'
-- PRINT @Command -- debugging
EXEC xp_cmdshell @Command
GO
Here i have used "OPENROWSET" for importing image to database and BCP command (Bulk copy) for exporting. We can also use BCP command for import images/documents.
VARBINARY(MAX) Datatype using for store images that consisting approximately 2 GB.
USE AdventureWorks;
GO
-- Create image warehouse for importing image into sql server database
CREATE TABLE dbo.PhotoLibrary
(
PhotoLibraryID INT IDENTITY ( 1 , 1 ) PRIMARY KEY,
ImageName VARCHAR(100),
Photo VARBINARY(MAX)
)
GO
-- SQL Server import image - storing images to sql server database
INSERT INTO dbo.PhotoLibrary
([ImageName])
VALUES ('nature.JPG')
GO
UPDATE dbo.PhotoLibrary
SET Photo = (SELECT *
FROM OPENROWSET(BULK 'D:\image\photo\nature.JPG',
SINGLE_BLOB) AS x)
WHERE [ImageName] = 'nature.JPG'
GO
-- Check table population
SELECT *
FROM dbo.PhotoLibrary
GO
-- SQL Server export image - Using BCP Command (Bulk Copy)
DECLARE @Command NVARCHAR(4000)
-- Keep the command on ONE LINE - SINGLE LINE!!!
SET @Command = 'bcp "SELECT Photo FROM AdventureWorks.dbo.PhotoLibrary" queryout "D:\image\photo\natureNew.jpg" -T -n -S <Server name\Instance name>'
-- PRINT @Command -- debugging
EXEC xp_cmdshell @Command
GO
Tuesday, January 3, 2012
Len() and DataLength() function in SQL Server
Let me show the difference of the Len() and DataLength() function in sql server through a simple example.
Declare @TestTable Table
(
ID int,
Name varchar(50)
)
Insert Into @TestTable
values(1,'SQL Server')
,(2,'SQL Server ')
,(3,' SQL Server')
,(4,' SQL Server ')
Select Len(Name) As LengthOfString From @TestTable
Select DataLength(Name) As DataLengthOfString From @TestTable
Regarding the above sample query, the Len() function just avoiding the trailing space from the column data and returns the length of the string.But DataLength function returns the full length of column data , it won't avoid the trailing space.
Declare @TestTable Table
(
ID int,
Name varchar(50)
)
Insert Into @TestTable
values(1,'SQL Server')
,(2,'SQL Server ')
,(3,' SQL Server')
,(4,' SQL Server ')
Select Len(Name) As LengthOfString From @TestTable
Select DataLength(Name) As DataLengthOfString From @TestTable
Regarding the above sample query, the Len() function just avoiding the trailing space from the column data and returns the length of the string.But DataLength function returns the full length of column data , it won't avoid the trailing space.
Subscribe to:
Posts (Atom)