Ms Sql Server Technology Discussions

[mtkr]

let me giv u some links....

[mtkr]

http://msdn.microsoft.com/en-us/library/ms189039.aspx



http://msdn.microsoft.com/en-us/library/ms189049.aspx

[deals2buy]

[b] SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor[/b]


I just wrote down following script very quickly for one of the project which I am working on. The requirement of the project was that every index existed in database should be rebuilt with fillfactor of 80. One common question I receive why fillfactor 80, answer is I just think having it 80 will do the job.Fillfactor determines how much percentage of the space on each leaf-level page are filled with data.

The space which is left empty on leaf-level page is not at end of the page but the empty space is reserved between rows of data. This ensures that rearrangement of the data does not happen every time new data rows are inserted.

Following is [b]incorrect [/b]image of leaf-level page of fillfactor.

[img]http://www.pinaldave.com/bimg/fillfactor1.jpg[/img]
Following is [b]correct [/b]image of leaf-level page of fillfactor.

[img]http://www.pinaldave.com/bimg/fillfactor2.jpg[/img]

Let us see the T-SQL script which will rebuild each index of all tables of any particular database. Following script will work with SQL Server 2005 (SP2) and SQL Server 2008. It is simple cursor going over each table and rebuilding every index of database.

[CODE]
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
[/CODE]

[deals2buy]

[b] T-SQL Script for FizzBuzz Logic[/b]

Following is quite common Interview Question asked in many interview questions. FizzBuzz is popular but very simple puzzle and have been very popular to solve. FizzBuzz problem can be attempted in any programming language. Let us attempt it in T-SQL.

[b]Definition of FizzBuzz Puzzle[/b] : Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”.

[CODE]
DECLARE @counter INT
DECLARE @output VARCHAR(8)
SET @counter = 1
WHILE @counter < 101
BEGIN
SET @output = ''
IF @counter % 3 = 0
SET @output = 'Fizz'
IF @counter % 5 = 0
SET @output = @output + 'Buzz'
IF @output = ''
SET @output = @counter
PRINT @output
SET @counter = @counter + 1
END
[/CODE]

[img]http://www.pinaldave.com/bimg/fizzbuzz.jpg[/img]

[deals2buy]

[b] Several Readers Questions and Readers Answers[/b]

[b]Q. How the records of a table can be scripted in INSERT INTO statements?[/b]
A. In SQL Server 2008 : Right click Database > Tasks > Generate Scripts > In the wizard on Choose Script Option page, set Script Data option to True and complete the wizard.For SQL 2005 or earlier versions, use Database Publishing Wizard. For more details about Database Publishing wizard, please visit the blog [url="http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/"]http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/[/url]

[b]Q. How can I track the changes or identify the latest insert-update-delete from a table?[/b]
A. In SQL Server 2005 and earlier versions, there is no inbuilt functionality to know which row was recently changed and what were the changes. However, in SQL Server 2008, a new feature known as Change Data Capture (CDC) has been introduced to capture the changed data. To get more details about CDC, please visit [url="http://blog.sqlauthority.com/2009/08/15/sql-server-introduction-to-change-data-capture-cdc-in-sql-server-2008/"]http://blog.sqlauthority.com/2009/08/15/sql-server-introduction-to-change-data-capture-cdc-in-sql-server-2008/[/url]

In versions prior to 2008, the best method to track changes at the table level is Triggers and at the database level is SQL Profiler. Another workaround is to add one more column in the table like “ModifiedDate” to keep the current date-time of the change. Timestamp, rowversion and checksum columns are for optimistic concurrency. These are not suitable to track changes later.

[b]Q. I am getting collation conflict error when joining columns of two tables?[/b]
A. When we try to compare, join or union two columns of different collations, the following error occurs: [i]“Cannot resolve the collation conflict for equal to operation”.[/i]

Collation represents the character set of a data and can be specified at server, database and column levels. Collation of server is default for databases and collation of database is default for all columns. The collation is matched in following cases while executing a SELECT statement:

[CODE]
SELECT col1, col2
FROM Table1.col1 = Table2.col4
UNION
SELECT col5, col6
FROM Table3
Here make sure that collation of following columns should match
col1 = col5
col1 = col4
col2 = col6
[/CODE]

To resolve this issue, you can either specify collation with column name in t-sql statement or change the collation of column permanently by altering the table, database or even server. For more details on how to specify collation in t-sql statement, please check following article: [url="http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/"]http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/[/url]

Sometimes, this error occurs while comparing the table with temporary tables. In this case, check the default collation of your working database and tempdb. If they do not match, then apply the above mentioned resolution.

[b]Q. How can I get data from a database on another server?[/b]
A. If you want to import data only through t-sql query, then either use OPENDATASOURCE function. To repeatedly get data from another server, create a linked server and then use OPENQUERY function or use 4 part naming. If you are not adhered with T-SQL, then better use import/export wizard, and you can save it as a SSIS package for future use.

[b]Q. My multilanguage data is not inserting correctly and returning as ???. How can I store Multilanguage data in a table?[/b]
A. There are two things to keep in mind while storing unicode data. First, the column must be of unicode data type (nchar, nvarchar, ntext). Second, the value must be prefixed with N while insertion. For example,

[CODE]INSERT INTO table (Hindi_col) values (N’hindi data’)[/CODE]

[deals2buy]

[b] Location of Resource Database in SQL Server Editions[/b]

The Resource database is a read-only database that contains all the system objects that are included with SQL Server.

Location of this database is at different places in the different version of SQL Server.

[b]In SQL Server 2008:[/b]

[i]<drive>[/i]:\Program Files\Microsoft SQL Server\MSSQL10.[i]<instance_name>[/i]\Binn\.

The [b]Resource[/b] database cannot be moved.

[img]http://www.pinaldave.com/bimg/resourcedb.jpg[/img] ResourceDB location in SQL Server 2008

[b]In SQL Server 2005:[/b]

Same place where master database is located.

The [b]Resource[/b] database have to move with master database.

You can run following commands to know resourcedb version and last updated time.

[CODE]
SELECT SERVERPROPERTY('ResourceVersion') ResourceVersion,
SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime
GO
[/CODE]

[img]http://www.pinaldave.com/bimg/resourcedbver.jpg[/img]

[deals2buy]

[b] Introduction to SEQUENCE – Simple Example of SEQUENCE[/b]

SEQUENCE allows you to define a single point of repository where SQL Server will maintain in memory counter. [color=blue]USE [/color][color=black]AdventureWorks2008R2
GO[/color]


[CODE]
CREATE SEQUENCE [Seq]
AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000
GO
[/CODE]

Let us create a sequence. We can specify various values like start value, increment value as well maxvalue.

[CODE]
-- First Run
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO
-- Second Run
SELECT NEXT VALUE FOR Seq, c.AccountNumber
FROM Sales.Customer c
GO

[/CODE]

Once the sequence is defined, it can be fetched using following method. Every single time new incremental value is provided, irrespective of sessions. Sequence will generate values till the max value specified. Once the max value is reached, query will stop and will return error message.

[color=#ff0000]Msg 11728, Level 16, State 1, Line 2[/color]
The sequence object ‘Seq’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
We can restart the sequence from any particular value and it will work fine.

[CODE]
-- Restart the Sequence
ALTER SEQUENCE [Seq]
RESTART WITH 1
GO
-- Sequence Restarted
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO
Let us do final clean up.
-- Clean Up
DROP SEQUENCE [Seq]
GO
[/CODE]

There are lots of things one can find useful about this feature. We will see that in future posts. Here is the complete code for easy reference.

[CODE]
USE AdventureWorks2008R2
GO
CREATE SEQUENCE [Seq]
AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000
GO
-- First Run
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO
-- Second Run
SELECT NEXT VALUE FOR Seq, c.AccountNumber
FROM Sales.Customer c
GO
-- Restart the Sequence
ALTER SEQUENCE [Seq]
RESTART WITH 1
GO
-- Sequence Restarted
SELECT NEXT VALUE FOR Seq, c.CustomerID
FROM Sales.Customer c
GO
-- Clean Up
DROP SEQUENCE [Seq]
GO
[/CODE]

[deals2buy]

[b] SEQUENCE is not IDENTITY[/b]

The reality is that SEQUENCE not like IDENTITY. There is very clear difference between them. Identity is about single column. Sequence is always incrementing and it is not dependent on any table.
Here is the quick example of the same.

[CODE]
USE AdventureWorks2008R2
GO
CREATE SEQUENCE [Seq]
AS [int]
START WITH 1
INCREMENT BY 1
MAXVALUE 20000
GO
-- Run five times
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
SELECT NEXT VALUE FOR Seq AS SeqNumber;
GO
-- Clean Up
DROP SEQUENCE [Seq]
GO
[/CODE]

Here is the resultset.

[img]http://www.pinaldave.com/bimg/seq1.jpg[/img]

[deals2buy]

[b] Wait Stats – Wait Types – Wait Queues[/b]

http://blog.sqlauthority.com/2011/02/01/sql-server-wait-stats-wait-types-wait-queues-day-0-of-28-2/

[SonyVaio]

*****Create new table it will be used to identify the Sales associate gets the credit for the customer order. This attributes associated with table: Salesassoc_id, sales assoc_name, and sales location. The primary key is salesassoc_id. The table has a relationship with the order table Provide SQL statement below********

ee above query ki ila rasina, correctay na?

USE [IST7000]
GO

/****** Object: Table [dbo].[Tb1_identify_asso_custo_amj] Script Date: 02/03/2013 21:25:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Tb1_identify_asso_custo_amj](
[salesassoc_id] [int] NOT NULL,
[sales_assoc_name] [varchar](50) NULL,
[sales_location] [varchar](50) NULL,
CONSTRAINT [PK_Tb1_identify_asso_custo_amj] PRIMARY KEY CLUSTERED
(
[salesassoc_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

[loveindia]

[quote name='SonyVaio' timestamp='1359946223' post='1303219774']
*****Create new table it will be used to identify the Sales associate gets the credit for the customer order. This attributes associated with table: Salesassoc_id, sales assoc_name, and sales location. The primary key is salesassoc_id. The table has a relationship with the order table Provide SQL statement below********

ee above query ki ila rasina, correctay na?

USE [IST7000]
GO

/****** Object: Table [dbo].[Tb1_identify_asso_custo_amj] Script Date: 02/03/2013 21:25:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Tb1_identify_asso_custo_amj](
[salesassoc_id] [int] NOT NULL,
[sales_assoc_name] [varchar](50) NULL,
[sales_location] [varchar](50) NULL,
CONSTRAINT [PK_Tb1_identify_asso_custo_amj] PRIMARY KEY CLUSTERED
(
[salesassoc_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
[/quote]

The best thing in SQL Server is the answer will be in the question only. So all you have to do is read the question carefully.

The above mentioned table has an association with the Order table (which means a Foreign Key), but in the DDL(Data Definition Language) statement you have written it is not present. So the DDL should be like this.

CREATE TABLE SalesAssociate_CustOrder
(
salesassoc_id BIGINT NOT NULL PRIMARY KEY,
salesassoc_name VARCHAR(64) NOT NULL,
sales_loc VARCHAR(64) NOT NULL,
order_id BIGINT NOT NULL
)
GO

ALTER TABLE SalesAssociate_CustOrder
ADD CONSTRAINT FK_SalesAssociateCustOrder_orderid_Order_id FOREIGN KEY (order_id) REFERENCES ORDER(order_id)

****This statement assumes there is an Order table present in the database already with a column name order_id****

[deals2buy]

[quote name='mtkr' timestamp='1359758659' post='1303207754']
deals2 bayyaa..... paina unna sample data n queries try chei...
vaatitho ne xpected results e vastunnaiii... but nuvvemoo where lo max teesukovatle antunnavvv!!!
[/quote]
Bhayya nuvvu cheppina query chesthe motham table lo ne unna max vastundemo kadaa

I want the max date for each id

[reddy001]

Folks,

I have two tables with same structure, need to compare the data between two tables, please share any query for that.

Thanks,

[ChampakDas]

kaarthikeya..raju vayya maharaju vayya

[deals2buy]

[quote name='reddy001' timestamp='1359990150' post='1303222145']
Folks,

I have two tables with same structure, need to compare the data between two tables, please share any query for that.

Thanks,
[/quote]

compare ante same records existing unnayo ledo check cheyyala?

Try something like:

SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN
Table2 T2
ON T1.ID = T2.ID