INNER JOIN

plz replace ur code as follows:-

SELECT F.featureid, F.[name], IIF(PF.Featureid is null, 0,1) as [bol] FROM 
features as F LEFT JOIN propertyfeatures as PF on

PF.featureid =F.featureid WHERE PF.propertyid=2

DO U KNOW THE MAXIMUM SIZE OF A ROW?

ha..haaa....haaa.......dont get confused for such type questions.....u may ask in interview..

MAXIMUM SIZE OF A ROW IN SQL IS 8060 bytes

HOW TO TAKE FULL BACKUP

Syntax for taking fullbackup of TEST database


BACKUP DATABASE TEST
TO DISK='D:\SQLServerBackups\TEST.bak' WITH FORMAT , NORECOVERY
  

NOTE:- by default CREATE BACKUP takes the fuul bkup of the database

CREATE DATABASE SNAPSHOT

syntax fro creating DATABASE SNAPSHOT for TEST Database


CREATE DATABASE test ON
( NAME = test_Data, FILENAME =
'D:\test_data.ss' )
AS SNAPSHOT OF test;

BACKUP DEVICE IN SQL?

/*i got a mail that HOW TO TAKE THE BACKUP OF THE DATABASE FROM USING FRONT END WEBAPPLICATION?
very simple,u juz need to create a backup device in ur database

*/
SYNTAX:-

EXEC sp_addumpdevice 'disk', 'testdevice', 'c:\dump\dump1.bak';


explanation:-


'disk'-- device type i.e tape or disk

'testdevice'--device name
'd:\databse\test.bak'--local path to save the backup file

HOW TO CREATE SERVER LOGIN IS SQL?

CREATE LOGIN test WITH PASSWORD = 'test123' MUST_CHANGE;
/* see hw simple it is.......The word MUST_CHANGE is used bcoz
the password must be changed on its first login*/

HOW TO CREATE INDEX IN SQL?

CREATE NONCLUSTERED INDEX IX_itemid--The word IX is only optional for simple recognization of index
    ON itemlist (itemid);
   
    /* indexes are just like indexes on the last page of your study text books.
    indexes in sql are most usefel objects in any database fro spped up the database performance
    */

KNOW UR SQL EDITION

dont know which sql edition ur presently using? here the simple function

select  @@VERSION

one more interesting thing to know ur server name......

select @@SERVERNAME

feel the simple sql queries........i love it........

tweet 4m geeta

haaaaaaaa......nice experience got tweet from tollywood singer GEETHA MADHURI

new features in SQL 2K8

Transperent Data Encryption (TDE) and compressed backups are available in sql 2k8 but not in sql 2k5........

instead of using third party encryption tool, TDE is most securable encryption tool and i've succeeded from using it..

TO KNOW ABOUT IDENTITY COLUMNS

we can simply find the identity(auto increment) columns from the following syntax:-

select * from sys.identity_columns


a lot of information we can get from the system dynamic managed views...that is the beauty of SQL Server

hello

i feel very comfortable with SQL than DOTNET.....dont know why so.........

GET ONLY DATE BUT NOT TIME IN SQL

I have read one post in orkut that he was asked how to get only date instead of datetime in sql...........

it is very simple..then we have to create a small function for that......plz copy the code below:-

SYNTAX:-

create FUNCTION [dbo].[DateOnly](@DateTime datetime)
--Returns only the date by removing the time
RETURNS date
AS
BEGIN
    return dateadd(dd,0,datediff(dd,0,@DateTime))
END




 -----now u can check --------

select dbo.dateonly(GETDATE())

CONVERTING DATAT TYPE INT TO VARCHAR

One of my followers asked that how can we convert the data types.here i am giving a simple example for thesame.

syntax:-

select  convert(varchar, itemid), itemname from itemlist
                           datatype, column_name

SIMPLE SYNTAX TO CREATE TRIGGER IN SQL

here i'l show how simple is creating a trigger in sql.....

SYNTAX:-

create trigger delete_trigger on itemlist
after update
as
begin
select * from deleted
end

Note:-- U must have itemlist table before using this code

DIFFERENCE BETWEEN PRIMARY KEY AND UNIQUE KEY

One interest thing in sql is creation of primary and unique keys....

the difference between primary Key and Unique key is , Primary create clustered index on the column where as unique key creat non-clustered index on the column.

One major difference is primary Key doesn't allow NULL values but Unique allows One NULL value.

How interest it is..............


SQL is my passion.............i preferred SQL than any other RDBMS

USING TEMPORARY TABLES

Using of #tables while designing the database is awesome experience....#tables are most useful when the space boundary databases......now i'l just explain how to get it....

SYNTAX:-


declare @idno as int,
@itemid as int,
@itemname varchar(50)
declare iteminfo cursor local for select * from ITEMLIST
OPEN iteminfo
FETCH NEXT FROM iteminfo INTO @ITEMID, @ITEMNAME
while @@FETCH_STATUS=0
begin
set @idno=(select idno from itemid_list where itemid=@itemid)
create table #Itemmaster (idno int, itemid int, itemname varchar(100))
insert into #itemmaster values (@idno, @itemid, @itemname)
FETCH NEXT FROM iteminfo INTO @ITEMID, @ITEMNAME
end
close iteminfo
deallocate iteminfo



---------------to see the data in #table-------------
select * from #itemmaster

Note:-   Before using this code , u must created itemlist and itemid_list tables.then enjoy the programming...........

Puri Jagan reveals the secret

Ya.......finally Puri Jagan revealed the secret that he and Mahesh Babu is looking forward to remake the chiru's biggest hit movie KHAIDI..............fans ready to accept....

Political Game

Yeddyurappa shifted to another hospital

Windows Tablet Computer

Only 12 more days to go for the launch of the new Windows tablet computer.....eagerly waiting ........ 

amazing response

thanx for all viewers in india as well as abroad.....getting awesome reponse for my blog.......thanx for one and all

fetch next from syntax

i feel very odd thing in sql is when writing cursor syntax, we have to use the fetch next from   
fjjtwice.

V I SIVA RAO: Search the desired text in Stored Procedures

V I SIVA RAO: Search the desired text in Stored Procedures: hello guys............one interesting thing in sql is you can search text you desired which you used in stored peocedures..... SYNTAX:- ...

Search the desired text in Stored Procedures

hello guys............one interesting thing in sql is you can search text you desired which you used in stored peocedures.....

SYNTAX:-

SELECT definition FROM sys.all_sql_modules WHERE definition LIKE '%FINANCIAL%'

missing index details in SQL 2K8

We can find out the missing indexes and as well as the statistics by using DTA(Data Tuning Adviser) in sql 2k8...

The other way to find out the missing index details is from using the syntax below:-

select * from sys.dm_db_missing_index_details

Note:-   Please keep in mind that too many indexes will affect the performance of the database.

cricket

two back to back wins for dhoni sena......young guys are showing awesome performance......vshing gd luck for rest of the series

V I SIVA RAO: USING CURSOR

V I SIVA RAO: USING CURSOR: one of my frnd asked about the use of CURSORS while developing database.... here i will clarify one thing......we can insert bulk rows int...

chiru

ram gopal verma has tweeted that puri jagan's movie BUSINESSMAN is the remake of chiru's KHAIDI movie............good stuff to the media

sa account

an interesting thing in SQL server is by using sa login , we can do any type of activities. My suggestion is don't expose sa login credentials to anyone.

steve jobs

my deepest condolences to gr8 steve jobs for making our lives very simplified.....

wishes

happy dusserah to all u guys........may this event strengthen ur life than ever before

TO KNOW ABOUT DEPENDS

yesterday i got a mail from one of my follower. his questions is how to know the depends of particular object?

woooh.........its very simple-----

syntax is:-

sp_depends '<tablename>' | '<stored procedure> |

example:-

sp_depends 'test' --test is a table name

press f5

you will find the depends of test table in result box.........

see how simple it is.........

USING CURSOR

one of my frnd asked about the use of CURSORS while developing database....

here i will clarify one thing......we can insert bulk rows into the table by using of cursors......stored procedures or by using of normal syntaxes we cant insert bulk rows......

syntax:

declare @name varchar(50),
@address varchar(50)

set @name='shiva'
set @address='vizag'

declare cursor test local for insert into test values (@name, @address)
open test
fetch next from test
while @@fetch_status=0
fetch next from test
close test
deallocate test

so happy

ya.really........coz , one of my favorite frnd's mail address i got............after such a long time.......its very thrilling experience i had..............moreover her cute baby......awesome experience........harith, really u r a lucky girl

FIRST STEP

every body thinks that sql server is  very tough to learn.................which is absolutely wrong.......

take example of me............................i am very knew to sql........earlier me too dont know about the sql.........but when i asked the IT professonals, they said sql server is very tough to learn.........then i start thinking about the sql .........i just satrted develeping the databse in sql............but i dont know first how to install sql server in my system.......then i approched some books.........but relevant info i couldn't able to get.............then i dare to install sql server 2005 in my system...........it was shown so many errors..................first error that i got was 'your operating system is not compatible'

then i start searching in internet to know the compatibility................after visiting several web sites......i found nothing....it is a waste of time.......and waste of money.......

finally installation was complete with using of my in-house learning...........

how to create a procedure?

its very simple.......................

syntax:-



create proc test_proc
@customer_id int,
@name varchar(50),
@address varchar(50)

as

------------inserting values into test table-------------

begin

insert into test values (@customer_id, @name, @address)

end

--------executing the procedure-------
exec test_proc 1, 'shiva', 'visakhapatnam'
-------testing the result--------
select * from test

know your sql server

Here i will explain what are the minimum requirements to work with sql server

1.  winxp sp4, win2k3 sp2, win 2k8 sp1 with minimum 512 MB RAM

if you feel any difficulty while installing sql server 2005/2008 , feel free to email me.....chinna7485@gmail.com

need any clarifications regarding database develeping...write an email to me..........

sample syntax for create table:

create table test (idno int, name varchar(50), address vayrchar(50)

press F5..............YOUR TABLE IS READY.....