Showing posts with label Sql. Show all posts
Showing posts with label Sql. Show all posts
Monday, June 25, 2018
ROW_NUMBER() OVER Dynamic @sortExpression
Step 1: Create Table
CREATE TABLE [dbo].[Student](
[ID] [int] NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL
) ON [PRIMARY]
GO
Step 2: Insert Records in the table
insert into Student values (1,'Jack','california')
insert into Student values(2,'Lira','texas')
insert into Student values(3,'Aron','sweden')
CREATE TABLE [dbo].[Student](
[ID] [int] NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL
) ON [PRIMARY]
GO
Step 2: Insert Records in the table
insert into Student values (1,'Jack','california')
insert into Student values(2,'Lira','texas')
insert into Student values(3,'Aron','sweden')
ID Name City
1 Jack california
3 Aron sweden
2 Lira texas
Step 3: Execute Query
Declare @SortExp varchar(50) ='City'
Declare @strSql varchar(2000)
set @strSql='SELECT ROW_NUMBER() OVER ( ORDER BY '+@SortExp+' Asc) RowNumber,*
FROM Student Order By RowNumber'
exec (@strSql)
Result:
RowNumber ID Name City
1 1 Jack california
2 3 Aron sweden
3 2 Lira texas
1 Jack california
3 Aron sweden
2 Lira texas
Step 3: Execute Query
Declare @SortExp varchar(50) ='City'
Declare @strSql varchar(2000)
set @strSql='SELECT ROW_NUMBER() OVER ( ORDER BY '+@SortExp+' Asc) RowNumber,*
FROM Student Order By RowNumber'
exec (@strSql)
Result:
RowNumber ID Name City
1 1 Jack california
2 3 Aron sweden
3 2 Lira texas
5:44 AM 0
Find and Remove Duplicate Rows from a SQL Server Table
Delete Record from table without creating Temp table.
Step 1: Create Table
CREATE TABLE [dbo].[Student](
[ID] [int] NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL
) ON [PRIMARY]
GO
Step 2: Insert Records in the table
insert into Student values (1,'Jack','california')
insert into Student values(1,'Jack','california')
insert into Student values(1,'Jack','california')
insert into Student values(2,'Lira','texas')
insert into Student values(2,'Lira','texas')
Step 1: Create Table
CREATE TABLE [dbo].[Student](
[ID] [int] NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL
) ON [PRIMARY]
GO
Step 2: Insert Records in the table
insert into Student values (1,'Jack','california')
insert into Student values(1,'Jack','california')
insert into Student values(1,'Jack','california')
insert into Student values(2,'Lira','texas')
insert into Student values(2,'Lira','texas')
ID Name City
1 Jack california
1 Jack california
1 Jack california
2 Lira texas
2 Lira texas
Step 3: Execute Query
;
--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY ID, Name, City
ORDER BY ( SELECT 0)) RN
FROM Student)
delete FROM cte
WHERE RN > 1;
Result:
ID Name City
1 Jack california
2 Lira texas
12:14 AM 0
How to Create alphanumeric sequence in sql
To create an alphanumeric sequence like this:
spAlphaNumericIDGeneration will return next alphanumeric number.
tblItinerary : table Name
TagName : fileld Name
CREATE PROCEDURE spAlphaNumericIDGeneration
AS
declare
@alphabet varchar(4),
@number int,
@alphanumeric varchar(6),
@strNumber varchar(6),
@strAlphabet varchar(6),
@strAlphaNumeric varchar(7),
@Year varchar(4),
@intYear int
BEGIN
select @alphanumeric=max(TagName) from tblItinerary
if @alphanumeric=''
BEGIN
set @alphanumeric='AAA000'
END
set @alphabet=SUBSTRING(@alphanumeric,1,3)
set @number=SUBSTRING(@alphanumeric,4,3)
select @Year=Datepart(YEAR,getdate())
set @intYear=SUBSTRING(@Year,3,2)
if @number=999
BEGIN
set @number=1
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),
CTE1 (A) AS (SELECT CHAR(N+64) FROM Tally WHERE N between 1 and 26),
CTE2 (B) AS (SELECT c1.A + c2.A FROM CTE1 c1 CROSS JOIN CTE1 c2),
CTE3 (C) AS (SELECT c1.A + c2.A + c3.A FROM CTE1 c1 CROSS JOIN CTE1 c2 CROSS JOIN CTE1 c3),
CTE4 (D) AS (SELECT c1.A + c2.A + c3.A + c4.A FROM CTE1 c1 CROSS JOIN CTE1 c2 CROSS JOIN CTE1 c3 CROSS JOIN CTE1 c4),
CTE AS (SELECT A, RN = 1 FROM CTE1 UNION ALL
SELECT B, RN = 2 FROM CTE2 UNION ALL
SELECT C, RN = 3 FROM CTE3 UNION ALL
SELECT D, RN = 4 FROM CTE4)
SELECT top 1 @strAlphabet = A FROM CTE WHERE RN =3 and A > @alphabet order by A
END
ELSE
BEGIN
set @number=@number+1
set @strAlphabet = @alphabet
END
set @strNumber=right(replicate('0',3)+cast(@number as varchar(15)),3)
set @strAlphaNumeric=ltrim(rtrim(@strAlphabet)) + ltrim(rtrim(@strNumber)) + Char(64 +@intYear)
Select @strAlphaNumeric As ID
END
AAAA0000
AAAA0001
AAAA0002
AAAA0003
.
.
.
AAAA9999
AAAB0000
AAAB0001
.
.
.
ZZZZ9999spAlphaNumericIDGeneration will return next alphanumeric number.
tblItinerary : table Name
TagName : fileld Name
CREATE PROCEDURE spAlphaNumericIDGeneration
AS
declare
@alphabet varchar(4),
@number int,
@alphanumeric varchar(6),
@strNumber varchar(6),
@strAlphabet varchar(6),
@strAlphaNumeric varchar(7),
@Year varchar(4),
@intYear int
BEGIN
select @alphanumeric=max(TagName) from tblItinerary
if @alphanumeric=''
BEGIN
set @alphanumeric='AAA000'
END
set @alphabet=SUBSTRING(@alphanumeric,1,3)
set @number=SUBSTRING(@alphanumeric,4,3)
select @Year=Datepart(YEAR,getdate())
set @intYear=SUBSTRING(@Year,3,2)
if @number=999
BEGIN
set @number=1
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),
CTE1 (A) AS (SELECT CHAR(N+64) FROM Tally WHERE N between 1 and 26),
CTE2 (B) AS (SELECT c1.A + c2.A FROM CTE1 c1 CROSS JOIN CTE1 c2),
CTE3 (C) AS (SELECT c1.A + c2.A + c3.A FROM CTE1 c1 CROSS JOIN CTE1 c2 CROSS JOIN CTE1 c3),
CTE4 (D) AS (SELECT c1.A + c2.A + c3.A + c4.A FROM CTE1 c1 CROSS JOIN CTE1 c2 CROSS JOIN CTE1 c3 CROSS JOIN CTE1 c4),
CTE AS (SELECT A, RN = 1 FROM CTE1 UNION ALL
SELECT B, RN = 2 FROM CTE2 UNION ALL
SELECT C, RN = 3 FROM CTE3 UNION ALL
SELECT D, RN = 4 FROM CTE4)
SELECT top 1 @strAlphabet = A FROM CTE WHERE RN =3 and A > @alphabet order by A
END
ELSE
BEGIN
set @number=@number+1
set @strAlphabet = @alphabet
END
set @strNumber=right(replicate('0',3)+cast(@number as varchar(15)),3)
set @strAlphaNumeric=ltrim(rtrim(@strAlphabet)) + ltrim(rtrim(@strNumber)) + Char(64 +@intYear)
Select @strAlphaNumeric As ID
END
12:00 AM 0
Wednesday, November 16, 2011
How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005?
Important The DBCC MEMORYSTATUS command is intended to be a diagnostic tool for Microsoft Customer Support Services. The format of the output and the level of detail that is provided are subject to change between service packs and product releases. The functionality that the DBCC MEMORYSTATUS command provides may be replaced by a different mechanism in later product versions. Therefore, in later product versions, this command may no longer function. No additional warnings will be made before this command is changed or removed. Therefore, applications that use this command may break without warning.
The output of the DBCC MEMORYSTATUS command has changed from earlier releases of SQL Server. The output now contains several sections that were unavailable in earlier product versions.
Memory Manager
The first section of the output is Memory Manager. This section shows overall memory consumption by SQL Server.
Memory Manager KB
------------------------------ --------------------
VM Reserved 1761400
VM Committed 1663556
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
The elements in this section are the following:
- VM Reserved: This value shows the overall amount of virtual address space (VAS) that SQL Server has reserved.
- VM Committed: This value shows the overall amount of VAS that SQL Server has committed. VAS that is committed has been associated with physical memory.
- AWE Allocated: This value shows the overall amount of memory that is allocated through the AWE mechanism on the 32-bit version of SQL Server. Or, this value shows the overall amount of memory that locked pages consume on the 64-bit version of the product.
- Reserved Memory: This value shows the memory that is reserved for the dedicated administrator connection (DAC).
- Reserved Memory In Use: This value shows the reserved memory that is being used.
Summary of memory usage
The Memory Manager section is followed by a summary of memory usage for each memory node. In a Non-uniform memory access (NUMA) enabled system, there will be a corresponding Memory node entry for each hardware NUMA node. In an SMP system, there will be a single Memory node entry.
Note The memory node ID may not correspond to the hardware node ID.
Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 1757304
VM Committed 1659612
AWE Allocated 0
MultiPage Allocator 10760
SinglePage Allocator 73832
(5 row(s) affected)
Note These values show the memory that is allocated by threads that are running on this NUMA node. These values are not the memory that is local to the NUMA node.
The elements in this section are the following:
- VM Reserved: This value shows the VAS that is reserved by threads that are running on this node.
- VM Committed: This value shows the VAS that is committed by threads that are running on this node.
- AWE Allocated: This value shows the memory that is allocated through the AWE mechanism on the 32-bit version of the product. Or, this value shows the overall amount of memory that is consumed by locked pages on the 64-bit version of the product.
In a NUMA-enabled system, this value can be incorrect or negative. However, the overall AWE Allocated value in the Memory Manager section is a correct value. To track memory that is allocated by individual NUMA nodes, use SQL Server: Buffer Node performance objects. (For more information, see SQL Server Books Online.) - MultiPage Allocator: This value shows the memory that is allocated through the multipage allocator by threads that are running on this node. This memory comes from outside the buffer pool.
- SinglePage Allocator: This value shows the memory that is allocated through the single-page allocator by threads that are running on this node. This memory is stolen from the buffer pool.
Note The sums of the VM Reserved values and the VM Committed values on all memory nodes will be slightly less than the corresponding values that are reported in the Memory Manager section.
Aggregate memory
The next section contains aggregate memory information for each clerk type and for each NUMA node. For a NUMA-enabled system, you may see output that is similar to the following.
Note The following table contains only part of the output.
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 592
MultiPage Allocator 2160
(7 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 1) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 136
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 728
MultiPage Allocator 2160
(7 row(s) affected)
Note These node IDs correspond to the NUMA node configuration of the computer that is running SQL Server. The node IDs include possible software NUMA nodes that are defined on top of hardware NUMA nodes or on top of an SMP system. To find mapping between node IDs and CPUs for each node, view Information event ID number 17152. This event is logged in the Application log in Event Viewer when you start SQL Server.
For an SMP system, you will see only one section for each clerk type. This section is similar to the following.
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 768
MultiPage Allocator 2160
(7 row(s) affected)
Other information in these sections is about shared memory:
- SM Reserved: This value shows the VAS that is reserved by all clerks of this kind that are using the memory-mapped files API. This API is also known as shared memory.
- SM Committed: This value shows the VAS that is committed by all clerks of this kind that are using memory-mapped files API.
You can obtain summary information for each clerk type for all memory nodes by using thesys.dm_os_memory_clerks dynamic management view (DMV). To do this, run the following query:
select
type,
sum(virtual_memory_reserved_kb) as [VM Reserved],
sum(virtual_memory_committed_kb) as [VM Committed],
sum(awe_allocated_kb) as [AWE Allocated],
sum(shared_memory_reserved_kb) as [SM Reserved],
sum(shared_memory_committed_kb) as [SM Committed],
sum(multi_pages_kb) as [MultiPage Allocator],
sum(single_pages_kb) as [SinlgePage Allocator]
from
sys.dm_os_memory_clerks
group by type
Buffer distribution
The next section shows the distribution of 8-kilobyte (KB) buffers in the buffer pool.
Buffer Distribution Buffers
------------------------------ -----------
Stolen 553
Free 103
Cached 161
Database (clean) 1353
Database (dirty) 38
I/O 0
Latched 0
(7 row(s) affected)
The elements in this section are the following:
- Stolen: Stolen memory describes 8-KB buffers that the server uses for miscellaneous purposes. These buffers serve as generic memory store allocations. Different components of the server use these buffers to store internal data structures. The lazywriter process is not permitted to flush Stolen buffers out of the buffer pool.
- Free: This value shows committed buffers that are not currently being used. These buffers are available for holding data. Or, other components may request these buffers and then mark these buffers as Stolen.
- Cached: This value shows the buffers that are used for various caches.
- Database (clean): This value shows the buffers that have database content and that have not been modified.
- Database (dirty): This value shows the buffers that have database content and that have been modified. These buffers contain changes that must be flushed to disk.
- I/O: This value shows the buffers that are waiting for a pending I/O operation.
- Latched: This value shows the latched buffers. A buffer is latched when a thread is reading or modifying the contents of a page. A buffer is also latched when the page is being read from disk or written to disk. A latch is used to maintain physical consistency of the data in the page while it is being read or modified. A lock is used to maintain logical and transactional consistency.
Buffer pool details
You can obtain detailed information about buffer pool buffers for database pages by using thesys.dm_os_buffer_descriptors DMV. And you can obtain detailed information about buffer pool pages that are being used for miscellaneous server purposes by using the sys.dm_os_memory_clerks DMV.
The next section lists details about the buffer pool plus additional information.
Buffer Counts Buffers
------------------------------ --------------------
Committed 1064
Target 17551
Hashed 345
Stolen Potential 121857
External Reservation 645
Min Free 64
Visible 17551
Available Paging File 451997
(8 row(s) affected)
The elements in this section are the following:
- Committed: This value shows the total buffers that are committed. Buffers that are committed have physical memory associated with them. The Committed value is the current size of the buffer pool. This value includes the physical memory that is allocated if AWE support is enabled.
- Target: This value shows the target size of the buffer pool. If the Target value is larger than theCommitted value, the buffer pool is growing. If the Target value is less than the Committed value, the buffer pool is shrinking.
- Hashed: This value shows the data pages and index pages that are stored in the buffer pool.
- Stolen Potential: This value shows the maximum pages that can be stolen from the buffer pool.
- ExternalReservation: This value shows the pages that have been reserved for queries that will perform a sort operation or a hash operation. These pages have not yet been stolen.
- Min Free: This value shows the pages that the buffer pool tries to have on the free list.
- Visible: This value shows the buffers that are concurrently visible. These buffers can be directly accessed at the same time. This value is usually equal to the total buffers. However, when AWE support is enabled, this value may be less than the total buffers.
- Available Paging File: This value shows the memory that is available to be committed. This value is expressed as the number of 8-KB buffers. For more information, see the "GlobalMemoryStatusEx function" topic in the Windows API documentation.
Procedure cache
The next section describes the makeup of the procedure cache.
Procedure Cache Value
------------------------------ -----------
TotalProcs 4
TotalPages 25
InUsePages 0
(3 row(s) affected)
The elements in this section are the following:
- TotalProcs: This value shows the total cached objects that are currently in the procedure cache. This value will match the entries in the sys.dm_exec_cached_plans DMV.
Note Because of the dynamic nature of this information, the match may not be exact. You can use PerfMon to monitor the SQL Server: Plan Cache object and the sys.dm_exec_cached_plans DMV for detailed information about the type of cached objects, such as triggers, procedures, and ad hoc objects. - TotalPages: This value shows the cumulative pages that you must have to store all the cached objects in the procedure cache.
- InUsePages: This value shows the pages in the procedure cache that belong to procedures that are currently running. These pages cannot be discarded.
Global memory objects
The next section contains information about various global memory objects. This section also contains information about how much memory the global memory objects use.
Global Memory Objects Buffers
------------------------------ --------------------
Resource 126
Locks 85
XDES 10
SETLS 2
SE Dataset Allocators 4
SubpDesc Allocators 2
SE SchemaManager 44
SQLCache 41
Replication 2
ServerGlobal 25
XP Global 2
SortTables 2
(12 row(s) affected)
The elements in this section are the following:
- Resource: This value shows the memory that the Resource object uses. The Resource object is used by the storage engine and for various server-wide structures.
- Locks: This value shows the memory that Lock Manager uses.
- XDES: This value shows the memory that Transaction Manager uses.
- SETLS: This value shows the memory that is used to allocate the Storage Engine-specific per-thread structure that uses thread local storage.
- SE Dataset Allocators: This value shows the memory that is used to allocate structures for table access through the Access Methods setting.
- SubpDesc Allocators: This value shows the memory that is used for managing subprocesses for parallel queries, backup operations, restore operations, database operations, file operations, mirroring, and asynchronous cursors. These subprocesses are also known as parallel processes.
- SE SchemaManager: This value shows the memory that Schema Manager uses to store Storage Engine-specific metadata.
- SQLCache: This value shows the memory that is used to store the text of ad hoc statements and of prepared statements.
- Replication: This value shows the memory that the server uses for internal replication subsystems.
- ServerGlobal: This value shows the global server memory object that is used generically by several subsystems.
- XP Global: This value shows the memory that extended stored procedures use.
- Sort Tables: This value shows the memory that sort tables use.
Query memory objects
The next section describes Query Memory grant information. This section includes a snapshot of the query memory usage. Query memory is also known as workspace memory.
Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 14820
Maximum (Buffers) 14820
Limit 10880
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 11520
(11 row(s) affected)
Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 640
Maximum (Buffers) 640
Limit 640
(5 row(s) affected)
If the size and the cost of a query satisfy “small” query memory thresholds, the query is put in a small query queue. This behavior prevents smaller queries from being delayed behind larger queries that are already in the queue.
The elements in this section are the following:
- Grants: This value shows the running queries that have memory grants.
- Waiting: This value shows the queries that are waiting to obtain memory grants.
- Available: This value shows the buffers that are available to queries for use as hash workspace and as sort workspace. The Available value is updated periodically.
- Maximum: This value shows the total buffers that can be given to all queries for use as workspace.
- Limit: This value shows the query execution target for the big query queue. This value differs from the Maximum (Buffers) value because the Maximum (Buffers) value is not updated until there is change in the queue.
- Next Request: This value shows the memory request size, in buffers, for the next waiting query.
- Waiting For: This value shows the amount of memory that must be available to run the query to which the Next Request value refers. The Waiting For value is the Next Request value multiplied by a headroom factor. This value effectively guarantees that a specific amount of memory will be available when the next waiting query is run.
- Cost: This value shows the cost of the next waiting query.
- Timeout: This value shows the time-out, in seconds, for the next waiting query.
- Wait Time: This value shows the elapsed time, in milliseconds, since the next waiting query was put in the queue.
- Last Target: This value shows the overall memory limit for query execution. This value is the combined limit for both the big query queue and the small query queue.
Optimization
The next section is a summary of the users who are trying to optimize queries at the same time.Optimization Queue Value
------------------------------ --------------------
Overall Memory 156672000
Last Notification 1
Timeout 6
Early Termination Factor 5
(4 row(s) affected)
Small Gateway Value
------------------------------ --------------------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway Value
------------------------------ --------------------
Configured Units 2
Available Units 2
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
Note This amount does not include the memory that is required to run the query.
When a query starts, there is no limit on how many queries can be compiled. As the memory consumption increases and reaches a threshold, the query must pass a gateway to continue. There is a progressively decreasing limit of simultaneously compiled queries after each gateway. The size of each gateway depends on the platform and the load. Gateway sizes are chosen to maximize scalability and throughput.
If the query cannot pass a gateway, the query will wait until memory is available. Or, the query will return a time-out error (Error 8628). Additionally, the query may not acquire a gateway if the user cancels the query or if a deadlock is detected. If a query passes several gateways, the query does not release the smaller gateways until the compilation process has completed.
This behavior lets only a few memory-intensive compilations occur at the same time. Additionally, this behavior maximizes throughput for smaller queries.
Memory brokers
The next three sections show information about memory brokers that control cached memory, stolen memory, and reserved memory. Information that these sections provide can only be used for internal diagnostics. Therefore, this information is not detailed here.
MEMORYBROKER_FOR_CACHE Value
-------------------------------- --------------------
Allocations 1843
Rate 0
Target Allocations 1843
Future Allocations 0
Last Notification 1
(4 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 380
Rate 0
Target Allocations 1195
Future Allocations 0
Last Notification 1
(4 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 0
Rate 0
Target Allocations 1195
Future Allocations 0
Last Notification 1
(4 row(s) affected)
3:57 AM 0
How Can SQL Server 2008 is going to rock?
Here are the top 10 reasons why.
10. Plug-in model for SSMS. SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind. Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom.9. Inline variable assignment. I often wondered why, as a language, SQL languishes behind the times. I mean, it has barely any modern syntactic sugar. Well, in this version, they are at least scratching the the tip of the iceberg.Instead of:DECLARE @myVar int
SET @myVar = 5
you can do it in one line:DECLARE @myVar int = 5
Sweet.8. C like math syntax. SET @i += 5. Enough said. They finally let a C# developer on the SQL team.7. Auditing. It's a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws. It's a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it. SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.6. Compression. You may think that this feature is a waste of time, but it's not what it sounds like. The release will offer row-level and page-level compression. The compression mostly takes place on the metadata. For instance, page compression will store common data for affected rows in a single place.The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes). For instance, length of the varchar will be stored in 3 bits.Anyway, I don't really care about space savings - storage is cheap. What I do care about is that the feature promised (key word here "promises") to reduce I/O and RAM utilization, while increasing CPU utilization. Every single performance problem I ever dealt with had to do with I/O overloading. Will see how this plays out. I am skeptical until I see some real world production benchmarks.5. Filtered Indexes. This is another feature that sounds great - will have to see how it plays out. Anyway, it allows you to create an index while specifying what rows are not to be in the index. For example, index all rows where Status != null. Theoretically, it'll get rid of all the dead weight in the index, allowing for faster queries.4. Resource governor. All I can say is FINALLY. Sybase has had it since version 12 (that's last millennium, people). Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to. At the very least, it'll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.Actually Sybase is still ahead of MS on this feature. Its ASE server allows you to prioritize one user over another - a feature that I found immensely useful.3. Plan freezing. This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc...). If you've achieved your optimal query plan, now you can stick with it. Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server - well, this is the chill pill.2. Processing of delimited strings. This is awesome and I could have used this feature...well, always. Currently, we pass in delimited strings in the following manner:exec sp_MySproc 'murphy,35;galen,31;samuels,27;colton,42'
Then the stored proc needs to parse the string into a usable form - a mindless task.In 2008, Microsoft introduced Table Value Parameters (TVP).CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int)
DECLARE @myPeeps PeepsType
INSERT @myPeeps SELECT 'murphy', 35
INSERT @myPeeps SELECT 'galen', 31
INSERT @myPeeps SELECT 'samuels', 27
INSERT @myPeeps SELECT 'colton', 42
exec sp_MySproc2 @myPeeps
And the sproc would look like this:CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) ...
The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc. Say goodbye to all those string parsing routines.1. Intellisense in the SQL Server Management Studio (SSMS). This has been previously possible in SQL Server 2000 and 2005 withuse of 3rd party add-ins like SQL Prompt ($195). But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing).
Built-in intellisense is huge - it means new people can easily learn the database schema as they go.
There are a ton of other great features - most of them small, but hugely useful. There is a lot of polishing all over the place, like server resource monitoring right in SSMS, a la Vista.
3:42 AM 0
How Can use data types in Sql Server?
SQL Server has a variety of data types, and as with anything, the more options you have, the more confusing a choice can be. Most misunderstandings arise from data type limitations rather than functionality. Here are the most common questions I receive about using SQL Server data types.
Note: This information is also available as a PDF download.
#1: Which character data type should I use?
Use character data types to store values you don’t evaluate in mathematical equations, even if the data consists of numeric characters. For instance, use a character data type to store names, addresses, ZIP codes, and phone numbers. SQL Server offers several character data types and deciding which to apply is confusing only if you don’t know the differences between them. Table Agives a quick comparison of char, varchar, nchar, and nvarchar.
Table A: Character data types
| Data Type | Length | Storage Size | Max Characters | Unicode |
| char | Fixed | Always n bytes | 8,000 | No; each character requires 1 byte |
| varchar | Variable | Actual length of entry in bytes | 8,000 | No; each character requires 1 byte |
| nchar | Fixed | Twice n bytes | 4,000 | Yes; each character requires 2 bytes |
| nvarchar | Variable | Twice actual length of entry in bytes | 4,000 | Yes; each character requires 2 bytes |
Here are a few general rules that should help:
#2: Which integer type should I use?
Use integer data types to store numeric data that the application evaluates as numbers. Table Bcompares the four integer data types.
Table B: Integer data types
| Data type | Minimum value | Maximum value | Storage size |
| tinyint | 0 | 255 | 1 byte |
| smallint | -32,768 | 32,767 | 2 bytes |
| int | -2,147,483,648 | 2,147,483,674 | 4 bytes |
| bigint | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 | 8 bytes |
Assigning the appropriate integer data type isn’t as confusing as choosing a character data type. Simply use the smallest integer data type that accommodates the largest possible value.
#3: What’s the difference between numeric and decimal?
There’s no difference between the numeric and decimal data types. Use them interchangeably or use one or the other to store integer and floating-point numbers scaled from 1 to 38 places, inclusive of both sides of a decimal. Use this data type when you need to control the accuracy of your calculations in terms of the number of decimal digits. The following table lists the exact storage size for this data type, depending on size, as listed in Table C.
Table C: Precision storage requirements
| Total characters (precision) | Storage size |
| 1 - 9 | 5 bytes |
| 10 - 19 | 9 bytes |
| 20 - 28 | 13 bytes |
| 29 - 38 | 17 bytes |
#4: What’s the difference between float and real?
The only differences between float and real are their minimum and maximum values and their required storage, as compared in Table D. Use float or real to store approximate values, where precision can’t be represented (e.g., Pi).
Table D: Float and real data type restrictions
| Data type | n | Minimum Value | Maximum value | Precision | Storage size |
| float(n) | 1 - 24 | -1.79E + 308 | 1.79 + 308 | 7 digits | 4 bytes |
| 25 - 53 | -1.79E + 308 | 1.79E + 308 | 15 digits | 8 bytes | |
| real | n/a | -3.40E + 38 | 3.40E + 38 | 7 digits | 4 bytes |
The real data type is the same as float(24) — a floating data type with 24 digits to the right of the decimal point.
#5: What’s the difference between smalldatetime and datetime?
Both smalldatetime and datetime store a combination date and time value, but the minimum and maximum values, accuracy, and storage size are different, as compared in Table E. Use datetime even when all dates fall into smalldatetime’s range, if you require up-to-the-second accuracy.
Table E: Smalldatetime and datetime restrictions
| Data type | Minimum value | Maximum value | Accuracy | Storage size |
| smalldatetime | January 1, 1900 | June 6, 2079 | Up to a minute | 4 bytes (the first 2 bytes store the date; the second 2 bytes store the time) |
| datetime | January 1, 1753 | December 31, 9999 | One three-hundredth of a second | 8 bytes (the first 4 bytes store the date; the second 4 bytes store the time) |
#6: What’s the difference between smallmoney and money?
Use both smallmoney and money to store currency values. However, the minimum and maximum values for both differ, as compared in Table F. Both data types are accurate up to ten-thousandths of a monetary unit.
Table F: Smallmoney and money restrictions
| Data type | Minimum value | Maximum value | Storage size |
| smallmoney | -214,748.3648 | 214,748,3647 | 4 bytes |
| money | -922,337,203,685,477.5808 | 922,337,203.685,477.5807 | 8 bytes |
#7: Where’s the Boolean data type?
SQL Server doesn’t have a Boolean data type, at least not by that name. To store True/False, Yes/No, and On/Off values, use the bit data type. It accepts only three values: 0, 1, and NULL. (NULL is supported by SQL Server 7.0 and later.)
#8: What happened to text, ntext, and image?
SQL Server is phasing out text, ntext, and image. There’s no way to know how long SQL Server will support the older data types. Upgrade legacy applications to varchar, nvarchar, and varbinary.
#9: How do I assign a cursor or table data type?
You don’t, at least not in the traditional manner. You don’t assign these data types to a column. You can use cursor and table only as variables:
#10: What is a user-defined data type?
SQL Server lets you create custom data types that are based on system data types. Create a user-defined data type when you specify the same limitations often. For instance, if many tables contain a state column, base a user-defined data type on SQL Server’s nchar (see #1) with a length of 2 and name it State. Then, choose State as the column’s data type, instead of specifying nchar(2). It requires about as much work, but it’s self-documenting and easy to remember. This example is simple; usually a user-defined data type is a bit more complex.
#11: Does SQL Server 2008 have any new data types?
SQL Server 2008 has several new data types:
3:41 AM 0
Subscribe to:
Posts (Atom)
