| |
|
Microsoft SQL Server
|
Written by Administrator
|
|
Friday, 08 September 2006 |
SQL Server's Enterprise Manager provides a simple results view to show the data contained in a table. With this view, it is possible to edit the data in the table but there is no obvious method of to set a nullable field to Null.
To set a Null value to the field, PRESS CTRL + 0 (Control + Zero) key combination.
Write Comment |
|
Last Updated ( Tuesday, 06 November 2007 )
|
|
|
Written by Administrator
|
|
Thursday, 07 September 2006 |
This function is very useful if you have a CSV string and you want to make a table of all the values or you have a sentance and you want to make a table of words.
Open Query Analyzer
Login as sa
Copy the code and paste into the Query Analyzer
Click on Query->Execute or Press F5
Test the function
SELECT * FROM dbo.MakeTable('Hari Kishan Charora', ' ')
CREATE FUNCTION MakeTable (@LongText varchar(8000), @Delimiter char(1))
RETURNS @RetResult TABLE (ID int IDENTITY (1, 1) NOT NULL, Value varchar(20))
AS
BEGIN
declare @Value varchar(10)
SET @Value = ''
while PATINDEX('%'+@Delimiter+'%', @LongText) > 0
begin
SET @Value = LTRim(SUBSTRING(@LongText, 1, PATINDEX('%'+@Delimiter+'%', @LongText)-1))
INSERT INTO @RetResult (Value) VALUES (@Value)
SET @LongText = LTRim(SUBSTRING(@LongText, PATINDEX('%'+@Delimiter+'%', @LongText)+1, LEN(@LongText)-PATINDEX('%'+@Delimiter+'%', @LongText)))
end
SET @Value = LTrim(@LongText)
INSERT INTO @RetResult (Value) VALUES (@Value)
RETURN
END
Write Comment |
|
Last Updated ( Tuesday, 06 November 2007 )
|
|
|
Written by Sushma Charora
|
|
Thursday, 07 September 2006 |
Open Query Analyzer
Login as sa
Copy the code and paste into the Query Analyzer
Click on Query->Execute or Press F5
Test the function
select dbo.TableFrom-a-Range(101, 200)
CREATE FUNCTION TableFrom-a-Range (@RangeFrom int, @RangeTo int)
RETURNS @retRangeTable TABLE (RowID int primary key)
AS
BEGIN
WHILE @RangeFrom <= @RangeTo
BEGIN
INSERT @retRangeTable (RowID) VALUES (@RangeFrom)
SET @RangeFrom = @RangeFrom + 1
END
RETURN
END
Write Comment |
|
Last Updated ( Thursday, 07 September 2006 )
|
|
|
Written by Administrator
|
|
Thursday, 07 September 2006 |
Identifying Long Running or Failed SQL Server Agent Jobs on all the Servers
While researching how to identify long running jobs and failed jobs I found out that Microsoft SQL Server does not provide a simple and straight forward method to identify how long a SQL Server Agent job has been running. Therefore I decided to build my own process. My own process of identifying long running jobs and failed jobs consisted of three functions and one stored procedure and one T-SQL script which has to be run once on every Server where you want to monitor SQL Agent Jobs.
Function "Format_Date" converts the next_run_date and run_date into mm/dd/yyyy format.
Function "Format_Time" converts the next_run_time and run_time into hh:mm.mm format.
Function "Convert_ToDate" converts the varchar date into datetime.
The stored procedure (SP) is called "failed_longrunning_jobs"
and it does the bulk of the work to identify long running and failed jobs.
Below you will find copies of the functions code and the stored procedure.
/*
Author: Hari Kishan Charora
Date: 9/7/2006
Format Date Function
*/
CREATE FUNCTION Format_Date(@DateValue int)
RETURNS varchar(10)
AS
BEGIN
RETURN Replicate('0', 2-Len(RTrim(Convert(varchar(4), (@DateValue%10000)/100))))+Convert(varchar(4), (@DateValue%10000)/100)+'/'+Replicate('0', 2-Len(Convert(varchar(4), @DateValue%100)))+Convert(varchar(4), @DateValue%100)+'/'+Convert(varchar(4), @DateValue/10000)
END
GO
/*
Author: Hari Kishan Charora
Date: 9/7/2006
Format Time Function
*/
CREATE FUNCTION Format_Time(@TimeValue int)
RETURNS varchar(10)
AS
BEGIN
RETURN Left(REPLICATE('0', 6-Len(@TimeValue))+Convert(varchar(6), @TimeValue), 2)+':'+SUBSTRING(REPLICATE('0', 6-Len(@TimeValue))+Convert(varchar(6), @TimeValue), 3, 2)+'.'+SUBSTRING(REPLICATE('0', 6-Len(@TimeValue))+Convert(varchar(6), @TimeValue), 5, 2)
END
GO
/*
Author: Hari Kishan Charora
Date: 9/7/2006
Convert varchar to datetime function
*/
CREATE FUNCTION Convert_ToDate(@DateValue varchar(10))
RETURNS datetime
AS
BEGIN
RETURN Convert(datetime, @DateValue)
END
Go
Write Comment |
|
Last Updated ( Tuesday, 06 November 2007 )
|
|
Read more...
|
|
|
Written by Administrator
|
|
Wednesday, 30 August 2006 |
Hardware Support:
Provides 64-bit support for both the Itanium IA-64 architecture and the AMD x64 architecture
There are 6 editions of Microsoft SQL 2005:
SQL Server 2005 Enterprise Edition
SQL Server 2005 Standard Edition
SQL Server 2005 Workgroup Edition
SQL Server 2005 Express Edition
SQL Server 2005 Developer Edition
SQL Server 2005 Mobile Edition
Non-Uniform Memory Architecture support
NUMA architecture groups the CPU and memory into local pods of multiple processors. These pods are connected to each other via an external bus that transports cross-pod data traffic. This pod arrangement addresses the contention issue by limiting the number of CPUs completing for access of memory. To realize the maximum benefits from this architecture, both the operating systems and the applications must be designed to minimize cross-pod data traffic and maximize the faster intra-pod memory access.
If the operating system and the application are designed correctly, the NUMA architecture enables nearly linear scaling as more processors are added.
Support for Hyper-Threading
Hyper-Threading is a CPU technology developed by Intel that creates two logical processors for each physical processor in a system. Each logical processor is capable of simultaneously executing separate threads.
The goal of hyper-threading is to provide better resource consumption for multithreaded applications or multiple applications running on a single machine. While hyper-threading provides the potential of increasing the throughput on a server, the logical processors do compete for system resources such as the data in the processor’s cache.
Benefits: Counts only physical processors for licensing & improved performance
SQL Server Engine:
.NET Framework Integration
By enabling the development of stored procedures, user-defined functions, triggers, aggregate’s, and user-defined types using any of the .NET languages (C#, VB.NET etc).
Enhanced Multiple Instance Support
Support for up to 50 instances (SQL Server 2000 supported maximum 16 instances)
New Data Types
varbinary(max) and XML data types. varbinary(max) data type provides a new method for using LOBs with SQL Server. It can be used as a variable, and programmatically it can be treated like the smaller data types.
The new XML data type is based on varbinary(max) data type, enables you to store XML documents in the database.
Database Snapshots & Mirroring
Database mirroring works by sending transaction logs between the primary server and the mirroring server, basically making the new Database Mirroring feature a real-time log shipping application.
You can create a Database snapshot on the mirrored database and then access the database view for reporting.
Native HTTP Support
The capability of SQL Server to process incoming HTTP requests enables SQL Server to provide SQL statement execution and stored procedure invocation via the SOAP protocol. This means that SQL Server 2005 is able to process incoming web service requests without the presence of IIS or another web server.
The new HTTP support gives SQL Server native HTTP listening capability.
Server Events and DDL Triggers
Data Partitioning
Database Partitioning allows you to break a single database object such as table or an index into multiple pieces. The new data partitioning feature facilitates the management of very large tables and indexes. The partitioning is transparent to the applications.
Only native “T-SQL” data types can be used as a partitioning key. There is also a limitation of 1000 partitions per table, and all partitions must exist on a single node.
Index Enhancements
First, rebuilding a clustered index no longer forces all of the non-clustered index to be rebuilt.
Second, ability to disable an index. Disabling an index stops that index from being maintained by the SQL Server engine and also prevents the index from being used. When an index is disabled, SQL Server deallocates the storage space used by the index but keeps the index’s metadata. Before a disabled index can be used again, it needs to be rebuilt using ALTER INDEX command.
Third, SQL SERVER 2005’s new online index operations feature enables applications to access the index as well as perform update, insert, and delete operations on a table while the index rebuilding operation is running.
System Catalog and Metadata Enhancements
Metadata now resides in the resource database, which the system stored as a sys object. SQL Server 2005 no longer allows any direct access to system tables.
Multiple Active Results Sets (MARS)
SQL Server 2005 is now capable of supporting multiple active result sets on a single connection.
Bulk Data Loading (BCP)
Provides some great improvements as well as performance increases. The bulk data loading process now uses an XML-based format file and support logging of bad rows and to continue even if invalid rows or data are encountered.
Full-Text Search
1. CREATE FULLTEXT CATALOG and CREATE FULLTEXT INDEX added
2. Ability to back up and restore Full-Text catalogs and indexes along with the database.
3. Another interesting enhancement in SQL Server 2005’s Full-Text search support is the ability to use a thesaurus to find synonyms of search words.
T-SQL Query Processor Enhancements
There are several enhancements including Command Table Expressions (CTE), an enhanced TOP clause, an enhanced WAITFOR statement, and a new OUTPUT clause for DML statements.
Security:
User-Schema Separation
In SQL Server 2005 database objects are owned by schemas. Users no longer directly own database objects; instead they own schemas.
A schema is a container of objects. The schema is identified by the third part of the four-part object naming syntax used by SQL Server.
Stored Procedure Execution Context
More Granular Permissions Control
ALTER, ALTER ANY ‘X’, ALTER TRACE, CONTROL, SELECT, EXECUTE, IMPERSONATE, TAKE OWNERSHIP, VIEW DEFINATION
Server permissions are found in the sys.server_permissions view, while permissions are found in the sys.database_permissions view
Password Policy Enforcement
The new password policy enforcement feature follows local Windows password policies and enables you to implement a consistent enterprise-wide security policy, not just for your Windows server systems but also for your SQL Server database systems. SQL Server 2005 now has the capability to enforce password strength, password expiration, and account lockout policies.
Database Administration and Development Tools
New SQL Server 2005 management tool (No Server Manager in System Tray)
SQL Server Enterprise is replaced with New Server Management Studio
Write Comment |
|
Last Updated ( Tuesday, 06 November 2007 )
|
|
| |
|
|
|
|
Newsflash |
|
"Keep away from people who try to belittle your ambitions. Small people always do that, but the really great make you feel that you, too, can become great."
- Mark Twain |
|
|
|
