Indexing on frequently updated table
I was asked a question in an interview, where I was given a situation which is: i have registered to an online GAMIng website. It displays my rank on the basis of my score when I play a game. database table has 2 columns: 1. Username 2. Score. since the login page was loading very slowly so the programmers...
How to use stored procedures with examples?
What is describe command in SQL server?
What is its purpose?How to use it?
Answered by: suji
View all questions by suji View all answers by suji
Member Since Sep-2005 | Answered On : Nov 5th, 2011
Here are multiple ways to get the table information. The DESCRIBE command does not exist in MS SQL SERVER. This is an Oracle command used to describe the structure of objects within a given database. To achieve the same task in MSSQL Server, there are a series of stored procedures with the prefix SP_ that can be used. To view the structure of a table within the current database, use the command
If you would like to see more details, Create your custom procedureCode
sp_help 'TABLE_NAME';
Here is another alternative way to get the same informationCode
CREATE procedure DESCRIBE ( @tablename varchar(256) ) AS begin SELECT DISTINCT sCols.colid AS 'order', sCols.name, sTyps.name, sCols.length FROM [syscolumns] sCols INNER JOIN [systypes] sTyps ON sCols.xtype = sTyps.xtype INNER JOIN [sysobjects] sObjs ON sObjs.id = sCols.[id] AND UPPER(sObjs.name) = UPPER(@tablename) ORDER BY sCols.colid end
Here is the usage :Code
sp_columns 'TableName' (e.g. sp_columns 'Employee') sp_columns [ @table_name = ] object [ , [ @table_owner = ] owner ] [ , [ @table_qualifier = ] qualifier ] [ , [ @column_name = ] COLUMN ] [ , [ @ODBCVer = ] ODBCVer ]
Contributors for the editorial answer : Kewlshiva, srilakshmi.b, raaghav,kevaburgCode
EXEC sp_columns @table_name = 'Department', @table_owner = 'sa'; SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName'
sp_help dbo.customers;
worked for me just fine.
Thanks to the OP, this runs perfectly on SQL 2008 R2, do not know about other versions
To see the structure (description ) of the table in Sql Server the followed command surely works....
sp_help person
Note:***
to display the structure of the table in Sql Server wer use the followed command ...it has the accurate rating ****
sp_help
sp_help person
Can I update views in SQL if yes in what scenarios? if your view is joining multiple tables
yes,we can update the view as like a table.view is virtual table,basically it is not having any storage data. in a view data will be coming from table.if u delete the table .we wont update the view.
Yes but you remember views, represents a virtual table but if you delete the table you wont be able to update the view
SQLdataadapter ,sqlcommand and SQLdatareader
What are difference between SQLdataadapter ,sqlcommand and SQLdatareader
sqldatareader means only read the datas in the table..
sqldataadapter means the datas read and writes
Answered by: Parished.D Chennai India
Answered On : Apr 24th, 2007create table ee (eno int, ename varchar(200), sal int)
insert into ee values(1, 'a', 2000)
insert into ee values(2, 'b', 6000)
insert into ee values(3, 'c', 8000)
select ENO, ENAME, min(sal) AS SAL from ee group by eno,ename having min(sal) > 5000
select ename from emp group by ename,sal having sal>5000;
If we want to check any values to retrieve the data,case is the best.Case statement is simple and easy to understand.
Code
SELECT case when sal>1000 then ename end FROM emp
How to remove duplicate records from a table?
SELECT ID FROM TBLSAMPLE GROUP BY ID OR SELECT DISTINCT (ID) FROM TBLSAMPLE
Code
SELECT ID FROM TBLSAMPLE GROUP BY ID OR SELECT DISTINCT (ID) FROM TBLSAMPLE
Good Code...however the code will delete all the duplicates... you may want to add:
delete top(n) clause in your delete statement to exactly delete the required number of records.
Explain normalization and denormalization with examples?
It is good.I understood if you put some brief explanation with data, is very usable to the persons who are not understanding about normalization like me. ...
Your Explnation may be good but it was too dificult to understand with out examples
How to find out duplicate records in SQL server?
Answered by: Hanif
Answered On : Apr 12th, 2006we have to use the group by with having command to get the duplicate values. this query shall show the result of only the users have duplicate values in the employee table.
Syntex:
Select columnName From Table_name
Group By columnName
Having count (*) > 1
Example:
SELECT UserID FROM employee
GROUP BY userid
HAVING count( * ) > 1
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
Code
SELECT EMAIL, COUNT(*) "REPETED EMAIL" FROM EMP GROUP BY EMAIL;
How to configure ssis package in SQL server 2008 r2?
Can we replace where clause by having clause. ?
Interviewer ask.. Can we replace where clause by group by clause let say select * from table_name where some_condition so how we replace where clause by group by clause
As a basic rule of query building: 1. The WHERE clause should be used to limit the number of rows returned by or affected by the a SQL statement. 2. The GROUP BY clause is used to group a selec...
Is there an import command in SQL 2008 for an excel spreadsheet?
From your SQL Server Management Studio, you open Object Explorer, go to your database where you want to load the data into, right click, then pick Tasks > Import Data. Select the options based on the excel file
How is the error handling in stored proc of T-SQL ?
1 how is the error handling in stored proc of T-SQL2 what is clustered index and non-clustered index? How many clustered indexes and non-clustered indexes can be created in one table? 3-what is disconnected mode?
Begin Try
--
--
End Try
Begin Catch
Declare @errMsg varchar(1000),@errSev int
select @errMsg=error_message(),@errSev=error_severity()
RaisError(@errMsg,@errSev,1)
End Catch
1) CLR is integrated in SQL Server 2005. Hence we can use below code for Exception Handling. BEGIN TRY SELECT * FROM Employees; END TRY BEGIN CATCH SELECT ERROR_NUMBER() ...
Representing SQL server objects
How do represent objects in SQL server? Is it possible? If yes, how?If not? Why?
Try using Server Management Objects (SMO) classes
List down the advantage and disadvantages of covering indexes.
Covering Indexes are useful for Input / Output intensive workloads.
Covering indexes are smaller in size
Covering Indexes are sorted by values
It is easier to cache date in Covering Indexes
Covering Indexes cannot be used to select all the columns / entire table.
Run PL/SQL block from SQL server
How do you run PL/SQL block from SQL server
Try these and see...
Go To Options -> Run SQL
Set "Statement Delimiter" to None
Go To Options -> Technical Parameters
Tick "Use SQLExecDirect"
What is high water mark? Give an example where it can be useful ?
High Water Mark (HWM) represents total number of extent blocks used by a table. The value of High Water Mark could sometime be more then the actual size required/used by a table. This is because, some...
How to set GLobal setting for query studio?
Steps to set / modify Global Setting for Query Studio: 1. Stop the Cognos 8|ReportNet server. 2. On the Cognos 8|ReportNet Server, go to the directory /c8|crn/templates/ps/async/ 3. Either create a n...
Define frequently used joins, projections, and selections as views so that users need not specify all the conditions and qualifications each time an operation is performed on that data. Display diffe...
if you have created view and if you want to update something in view it will also be update in table