European Windows 2019 Hosting BLOG

BLOG about Windows 2019 Hosting and SQL 2019 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2014 Hosting - HostForLIFE.eu :: Make an Index on #temp tables on SQL Server

clock March 8, 2016 21:12 by author Peter

Today let me explain you about how to make an Index on #temp tables. #Temp tables are much like SQL tables that are defined and stored in TempDB. Difference between them and a permanent table is they are not allowed to have foreign keys.


One of the feature of temp table (#temp) is that we can add a clustered or non clustered index. Also, #temp tables allow for the auto-generated columns (fields) to be created. This can help the optimizer when determining the number of records. Below is an example of creating both a clustered and non-clustered index on a temp table.
-- creating temp table - #employees
CREATE TABLE #Employees 
( 
ID INT IDENTITY(1,1), 
EmployeeID INT, 
EmployeeName VARCHAR(50) 
) 

INSERT INTO #Employees 
( 
EmployeeID, 
EmployeeName 
) 
SELECT 
EmployeeID = e.EmployeeID 
,EmployeeName = e.EmployeeName 
FROM dbo.Employees e 
 
-- creating clustered index 
CREATE CLUSTERED INDEX IDX_C_Employees_EmployeeID ON #Employees(EmployeeID)
 
-- creating non-clustured index 
CREATE INDEX IDX_Users_UserName ON #Employees(EmployeeName) 

 

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.


Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Categories:
Actions: E-mail | Kick it! | Permalink | commentComments (0) | RSS comment feedComment RSS

AngularJS Hosting - HostForLIFE.eu :: How to create Strong Password for AngularJS?

clock March 3, 2016 21:33 by author Peter

This code snippet helps us in choosing a strong password for AngularJS UI pages. All of us have seen , for choosing a password we need combination of special characters, Capital letter , small letters, digits etc to make it strong.

Write the following code:

<!DOCTYPE html>  
<html>  
<head>  
<title>Strong Password for Angular UI Pages</title>  


<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.8/angular.min.js"></script>    


<script>  


var app = angular.module("myApp", []);  


app.controller("myCtrl", function ($scope) {  




    var strongRegularExp = new RegExp("^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\$%\^&\*])(?=.{8,})");  




    var mediumRegularExp = new RegExp("^(((?=.*[a-z])(?=.*[A-Z]))|((?=.*[a-z])(?=.*[0-9]))|((?=.*[A-Z])(?=.*[0-9])))(?=.{6,})");  




    $scope.checkpwdStrength = {  


        "width": "150px",  


        "height": "25px",  


        "float": "right"  


    };  




    $scope.validationInputPwdText = function (value) {  


        if (strongRegularExp.test(value)) {  


            $scope.checkpwdStrength["background-color"] = "green";  


            $scope.userPasswordstrength = 'You have a Very Strong Password now';  


        } else if (mediumRegularExp.test(value)) {  


            $scope.checkpwdStrength["background-color"] = "orange";  


            $scope.userPasswordstrength = 'Strong password, Please give a very strong password';  


        } else {  


            $scope.checkpwdStrength["background-color"] = "red";  


            $scope.userPasswordstrength = 'Weak Password , Please give a strong password';  


        }  


    };  




});  


</script>  


</head>  


<body ng-app="myApp">  


<div ng-controller="myCtrl" style="border:5px solid gray; width:800px;">  


<div>  


    <h3>Strong Password for Angular UI Pages </h3>  


</div>  


<div style="padding-left:25px;">  


    <div ng-style="checkpwdStrength"></div>  


    <input type="password" ng-model="userPassword" ng-change="validationInputPwdText(userPassword)" class="class1" />  


    <b> {{userPasswordstrength}}</b>  


</div>  


<br />  


<br />  


<br />  


</div>  


</body>  


</html>  




HostForLIFE.eu AngularJS Hosting


HostForLIFE.eu    is European Windows Hosting Provider which focuses on Windows  Platform   only. We deliver on-demand hosting solutions including Shared  hosting,   Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT  as a  Service  for companies of all sizes.



    
    

    

        Tags: , , , , , , , , , , , , 

        Categories: 

        Actions: 
        E-mail | 
        Kick it! |
        Permalink |
        
             commentComments (0)
         |   
        RSS comment feedComment RSS
    

    






    
How To Collect Performance Metrics From SQL Server Query Analyzer?

    
clock March 1, 2016 20:37 by author Peter

    
In this topic, I will show you how to collect value of performance counters from query analyzer. Microsoft® SQL Server SQL Query Analyzer is a graphical tool that allows you to: 




    

  • Create queries and other SQL scripts and execute them against SQL Server databases. (Query window)
    • 

  • Quickly create commonly used database objects from predefined scripts. (Templates)
    • 

  • Quickly copy existing database objects. (Object Browser scripting feature)
    • 

  • Execute stored procedures without knowing the parameters. (Object Browser procedure execution feature)
    • 

  • Debug stored procedures. (T-SQL Debugger)
    • 

  • Debug query performance problems. (Show Execution Plan, Show Server Trace, Show Client Statistics, Index Tuning Wizard)
    • 

  • Locate objects within databases (object search feature), or view and work with objects. (Object Browser)
    • 

  • Quickly insert, update, or delete rows in a table. (Open Table window)
    • 

  • Create keyboard shortcuts for frequently used queries. (custom query shortcuts feature)
    • 

  • Add frequently used commands to the Tools menu. (customized Tools menu feature)
    • 



The following code is demo query to do same. You can create collect any counter vale for instant. Now write the code:

/*************************************************/ 
USE Master
GO
Create Table Perfmon (Object_name Varchar (200),
Counter_name varchar (300) , Instance_name varchar (100),
cntr_value bigint, cntr_type bigint , date varchar (20))
GO 
Insert into Perfmon
SELECT object_name , counter_name , instance_name , cntr_value, cntr_type
, convert (varchar (20),getdate () , 120) as date
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME in 
(
'MSSQL$Servername:Databases' ,
'MSSQL$Servername:General Statistics',
'MSSQL$Servername:Buffer Manager' ,
'MSSQL$Servername:Locks'
)
AND counter_name in 
( 
'Transactions/sec' ,'User Connections','Page life expectancy',
'Buffer cache hit ratio', 'Buffer cache hit ratio base' ,
'Free pages','Total pages','Target pages','Lock Wait Time (ms)'
)
Select @@servername
GO
Select *, case
when cntr_type  = 65792  Then 'instant_value'
when cntr_type = 272696576 Then 'Incremental'
When cntr_type = 1073939712 Then 'Instant fraction'
When cntr_type = 537003264 Then 'Use this with Base value'
else 'null' end  
from Master.dbo.perfmon
/*************************************************/


HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu    is European Windows Hosting Provider which focuses on Windows  Platform   only. We deliver on-demand hosting solutions including Shared  hosting,   Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT  as a  Service  for companies of all sizes.



    
    

    

        Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , 

        Categories: SQL 2014 Hosting

        Actions: 
        E-mail | 
        Kick it! |
        Permalink |
        
             commentComments (0)
         |   
        RSS comment feedComment RSS
    

    






    
SQL Server 2014 Hosting - HostForLIFE.eu :: String comparison and Collation in SQL Server

    
clock February 23, 2016 19:39 by author Peter

    
You probably already know that string comparison is dependent on the collation. but sometimes this dependency is slightly strange. I have installed an SQL Server with LATIN1_GENERAL_CI_AS as default collation (like is also the default setting during installation). Some weeks ago I came across a specific behavior in combination of this collation and the german language.




German is a stunning language! really. You may know that we have a tendency to Germans have some special vowels (the “Umlaut”: ä, ö, and ü) and also that extra ß which is often (but not always) simply interchangeable with “ss”. look at this query that uses the default collation for comparison:
select case
 when 'ss' collate latin1_general_ci_as
     = 'ß' collate latin1_general_ci_as
      then 'Yes'
 else 'No'
end [ss=ß?]

Considering that LATIN1_GENERAL_CI_AS is the default collation, the query above is identical to the following query:
select case
 when 'ss' = 'ß' then 'Yes'
 else 'No'
end [ss=ß?]

In both cases the result is Yes, so “ß” is considered as to be identical to “ss” when using the LATIN1_GENERAL_CI_AS collation. Now it's time to look at this query:

select case
 when 'ä' = 'ae' then 'Yes'
 else 'No'
end [ä=ae?]

This time the answer is no which is somewhat surprising, since one could consider the overall behavior inconsistent. In German, “ä” and “ae” (and also “ö” and “oe” in addition as “ü” and “ue”) are just as interchangeable as “ß” and “ss”. When this happened to me, I started browsing books online. There’s a hint, where you're advised setting the default collation to LATIN1_GENERAL_BIN for new installations. I didn’t know this so far, but ok: let’s retry our experiment:

select case
 when 'ss' collate latin1_general_bin
     = 'ß' collate latin1_general_bin
      then 'Yes'
 else 'No'
end [ss=ß?]

Now the answer is no. but considering the fact that most newer installations use LATIN1_GENERAL_CI_AS, simply because that’s the default during installation, I can’t just change the collation to LATIN1_GENERAL_BIN, since this will certainly produce other problems with queries spanning multiple databases with totally different collations. Not taking into account that changing the collation for an existing server and all of its databases/columns is a cumbersome and also very risky task on its own.


 


HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu   is European Windows Hosting Provider which focuses on Windows Platform   only. We deliver on-demand hosting solutions including Shared hosting,   Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a  Service  for companies of all sizes.



    
    

    

        Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , 

        Categories: SQL 2014 Hosting

        Actions: 
        E-mail | 
        Kick it! |
        Permalink |
        
             commentComments (0)
         |   
        RSS comment feedComment RSS
    

    






    
SQL Server 2014 Hosting - HostForLIFE.eu :: How to Return Value in SQL Server  from EXEC Function ?

    
clock February 16, 2016 20:15 by author Peter

    
In this tutorial, I will show you how to Return Value in SQL Server  from EXEC Function. If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. The following Stored Procedure is used which returns an Integer value 1 if the StudentId exists and 0 if the StudentId does not exists.




CREATE PROCEDURE CheckStudentId  
@StudentId INT  
AS  
BEGIN  
SET NOCOUNT ON;  
DECLARE @Exists INT  
IF EXISTS(SELECT StudentId FROM Students WHERE StudentId = @StudentId)  
BEGIN  
SET @Exists = 1  
END  
ELSE  
BEGIN  
SET @Exists = 0  
END  
RETURN @Exists  
END  

Returned value from EXEC function:
The returned integer value from the Stored Procedure, you need to make use of an Integer variable and use along with the EXEC command while executing the Stored Procedure.

    DECLARE @ReturnValue INT  
    EXEC @ReturnValue = < Store Procedure Name > < Parameters > Select @ReturnValue  
    Example: DECLARE @ReturnValue INT  
    EXEC @ReturnValue = CheckStudentId 34  
    SELECT @ReturnValue  

If There are valid StudentId then Output will be : 1


 


HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu   is European Windows Hosting Provider which focuses on Windows Platform   only. We deliver on-demand hosting solutions including Shared hosting,   Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a  Service  for companies of all sizes.



    
    

    

        Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , 

        Categories: SQL 2014 Hosting

        Actions: 
        E-mail | 
        Kick it! |
        Permalink |
        
             commentComments (0)
         |   
        RSS comment feedComment RSS
    

    






    
SQL Server 2014 Hosting -  HostForLIFE.eu :: How to Empty and Deleter All SQL Database?

    
clock February 9, 2016 23:06 by author Peter

    
Today, let me show you how to empty and delete all SQL Database. Now write the following code snippet for Clear Blank SQL Database:




    DECLARE @name VARCHAR(128)  


    DECLARE @SQL VARCHAR(254)  


    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])  


    WHILE @name is not null  


    BEGIN  


        SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'  


        EXEC (@SQL)  


        PRINT 'Dropped Procedure: ' + @name  


        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])  


    END  


    GO  


    /* Drop all views */  


    DECLARE @name VARCHAR(128)  


    DECLARE @SQL VARCHAR(254) 


 


    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])  


    WHILE @name IS NOT NULL  


    BEGIN  


        SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'  


        EXEC (@SQL)  


        PRINT 'Dropped View: ' + @name  


        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])  


    END  


    GO  


    /* Drop all functions */  


    DECLARE @name VARCHAR(128)  


    DECLARE @SQL VARCHAR(254)      


    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])       


    WHILE @name IS NOT NULL  


    BEGIN  


        SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'  


        EXEC (@SQL)  


        PRINT 'Dropped Function: ' + @name  


        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])  


    END  


    GO       


    /* Drop all Foreign Key constraints */  


    DECLARE @name VARCHAR(128)  


    DECLARE @constraint VARCHAR(254)  


    DECLARE @SQL VARCHAR(254)       


    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)       


    WHILE @name is not null  


    BEGIN  


        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)  


        WHILE @constraint IS NOT NULL  


        BEGIN  


            SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'  


            EXEC (@SQL)  


            PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name  


            SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)  


        END  


    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)  


    END  


    GO       


    /* Drop all Primary Key constraints */  


    DECLARE @name VARCHAR(128)  


    DECLARE @constraint VARCHAR(254)  


    DECLARE @SQL VARCHAR(254)       


    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)       


    WHILE @name IS NOT NULL  


    BEGIN  


        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)  


        WHILE @constraint is not null  


        BEGIN  


            SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'  


            EXEC (@SQL)  


            PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name  


            SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)  


        END  


    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)  


    END  


    GO       


    /* Drop all tables */  


    DECLARE @name VARCHAR(128)  


    DECLARE @SQL VARCHAR(254)       


    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])       


    WHILE @name IS NOT NULL  


    BEGIN  


        SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'  


        EXEC (@SQL)  


        PRINT 'Dropped Table: ' + @name  


        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])  


    END  


    GO  


HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu    is European Windows Hosting Provider which focuses on Windows  Platform   only. We deliver on-demand hosting solutions including Shared  hosting,   Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT  as a  Service  for companies of all sizes.




 

    
    

    

        Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , 

        Categories: SQL 2014 Hosting

        Actions: 
        E-mail | 
        Kick it! |
        Permalink |
        
             commentComments (0)
         |   
        RSS comment feedComment RSS
    

    






    
SQL Server 2014 Hosting - HostForLIFE.eu :: How To Check Available Column in A Table

    
clock January 5, 2016 21:50 by author Rebecca

    
In this tutorial, we will share to you how to check available colum in a table. If you want to check if the column is already available in the table, you can use system views like sys.columns or INFORMATION_SCHEMA.COLUMNS.




Step 1


First, let us create the dataset:


USE TEMPDB;
CREATE TABLE TESTING(ID INT, NAME VARCHAR(100))


Step 2


Suppose you want to find out the existence of the column named NAME and print a message. You can do it by using any of the following methods:


IF EXISTS
(
SELECT * FROM SYS.COLUMNS
WHERE NAME='NAME' AND OBJECT_ID=OBJECT_ID('TESTING')
)
PRINT 'COLUMN EXISTS'
--
IF EXISTS
(
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='NAME' AND TABLE_NAME='TESTING'
)
PRINT 'COLUMN EXISTS'


There is also a shorter way to do this. You can use COL_LENGTH system function:


IF (SELECT COL_LENGTH('TESTING','NAME')) IS NOT NULL
PRINT 'COLUMN EXISTS'


What it does is that it finds the length of the column. If it is null, the column does not exist in the table otherwise it exists.


Simple and fast, right?


HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu  is European Windows Hosting Provider which focuses on Windows Platform  only. We deliver on-demand hosting solutions including Shared hosting,  Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service  for companies of all sizes.



    
    

    

        Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , 

        Categories: SQL 2014 Hosting

        Actions: 
        E-mail | 
        Kick it! |
        Permalink |
        
             commentComments (0)
         |   
        RSS comment feedComment RSS
    

    






    
SQL Server 2012 Hosting - HostForLIFE.eu :: Managing Number of SQL Server ErrorLog

    
clock January 5, 2016 21:36 by author Peter

    
Microsoft SQL Server saves 7 errorlog files by default. When new errorlog is made, the recent one will be deleted at identical time. If you wish to keep a lot of errorlog, you'll follow 2 ways below to manage number of SQL Server ErrorLog, including both increasing and decreasing ErrorLog number.
Method 1: configure SQL Server ErrorLog number in SSMS
Step 1: Open SQL Server Management Studio. connect with SQL Server with SQL Server Authentication. 




Tips: If user account password forgot, you'll only reset user password or change user forgotten password. So login to SQL Server with SQL Server Authentication and new user password. Otherwise, even though you'll successfully connect with SQL Server with Windows Authentication, the following error still occurs as soon as you want to configure SQL Server logs.




Step 2: Navigate to Management > SQL Server Logs. Right-click on SQL Server Logs and choose Configure.




Step 3: In pop-up window Configure SQL Server Error Logs, tick the box "Limit the number of error log files before they are recycling". And set "Maximum number of error log" with willing number. Save changes at last before you close this window. 




Method 2: Change Number of SQL Server ErrorLog in Registry Editor

Step 1: First, type regedit in Start box, and press Enter to run Registry Editor.

Step 2: Now, Locate to the following path(1 or 2) and create a new entry in registry editor.
1. Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer
2. Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ MSSQLServer\MSSQLServer

Right-click in the right blank pane of registry editor, and tap New > QWORD (64-bit) Value button in pop-up options.





Step 3: Rename the entry as NumErrorLogs and double-click it to edit its value. Type a number that you want to save SQL Server ErrorLog file. Tick Decimal under Base and click OK.




Close Registry Editor and finish setting on increasing or decreasing number of SQL Server Errorog file.




HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu   is European Windows Hosting Provider which focuses on Windows Platform   only. We deliver on-demand hosting solutions including Shared hosting,   Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a  Service  for companies of all sizes.



    
    

    

        Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , 

        Categories: 

        Actions: 
        E-mail | 
        Kick it! |
        Permalink |
        
             commentComments (0)
         |   
        RSS comment feedComment RSS
    

    






    
SQL Server 2014 Hosting - HostForLIFE.eu :: How to Add Primary Key to Existing Table

    
clock December 8, 2015 23:28 by author Peter

    
I have an existing table referred to as Persion. in this table I actually have five columns:
persionId
Pname
PMid
Pdescription
Pamt




When I created this table, I set PersionId and Pname because the primary key. I now wish to include an extra column within the primary key - PMID. however can i write an ALTER statement to try and do this? (I already have 1000 records in the table). First, you can drop constraint and recreate it with the following code:
alter table Persion drop CONSTRAINT <constraint_name>
alter table Persion add primary key (persionId,Pname,PMID)

You can find the constraint name by using the code below:
select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='Persion'
and type_desc LIKE '%CONSTRAINT'

I think something like this should work
-- drop current primary key constraint
ALTER TABLE dbo.persion 
DROP CONSTRAINT PK_persionId;
GO

-- add new auto incremented field
ALTER TABLE dbo.persion 
ADD pmid BIGINT IDENTITY;
GO

-- create new primary key constraint
ALTER TABLE dbo.persion 
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);
GO


HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu   is European Windows Hosting Provider which focuses on Windows Platform   only. We deliver on-demand hosting solutions including Shared hosting,   Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a  Service  for companies of all sizes.



    
    

    

        Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , 

        Categories: SQL 2014 Hosting

        Actions: 
        E-mail | 
        Kick it! |
        Permalink |
        
             commentComments (0)
         |   
        RSS comment feedComment RSS
    

    






    
SQL Server 2012 Hosting - HostForLIFE.eu:: How to Configuring a Server Side Trace in SQL Server 2012?

    
clock November 24, 2015 19:37 by author Peter

    
When I’m after SQL Server performance problems, SQL Server Profiler is still my beloved tool. Allthough i do know that extended events offer a mor light-weight answer, those are still a bit cumbersome to use (but I’ve seen that we are able to expect some enhancements with SQL Server 2012).




When I’m using profiler to isolate performance problems, I attempt to tack server side traces, whenever doable. luckily, SQL Server Profiler can assist you making a script for a server side trace (File/Export/Script Trace Definition), so you don’t have to figure out all the event- and column-codes. As i used to be doing a similar configuration again and again, i made a decision to separate the TSQL code for the configuration within a stored procedure.

And here comes dbo.configureServerSideTrace:
if object_id('dbo.configureServerSideTrace', 'P') is not null
drop procedure dbo.configureServerSideTrace
go

-- Example for:
-- Start Trace
--   declare @traceID int
--   exec dbo.configureServerSideTrace @traceStatus = 1
--                                    ,@traceID = @traceID output
--                                    ,@maxFileSize = 10000
--                                    ,@traceFileName = N'e:\VMITrace\Undo'
--                                    ,@spId = @@spid
-- 
-- End Trace
--   exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID

create procedure dbo.configureServerSideTrace
             (@traceStatus   bit                  -- 1 => Start Trace
                                                  -- 0 => Stop Trace
             ,@traceID       int output           -- If the Trace is started, this param will return the TraceID
                                                  -- For stopping the trace, the param has to be provided
             ,@spId          int           = null -- provide the @@spid, if you want to filter only events for this conection
                                                  -- Optional. If not provided => no filter. Not needed for stopping the trace
             ,@maxFileSize   bigint        = 5000 -- Maximum Trace File Size in Megabyte. Trace will be stopped, if the filesize is reached.
             ,@traceFileName nvarchar(200) = null -- Name of the trace file (server side!)
                                                  -- Optional. Not neded for stoping the trace
                                                  -- Attention! If the file already exists, the SP will yield an error
                                                  -- and no trace is started.
             ) as
begin

if (@traceStatus = 0)
begin
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2
return;
end

-- Create a Queue
declare @rc int

exec @rc = sp_trace_create @TraceID output, 0, @traceFileName, @maxfilesize, NULL
if (@rc != 0) goto error

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 48, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 43, 51, @on
exec sp_trace_setevent @TraceID, 43, 4, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 22, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 42, 51, @on
exec sp_trace_setevent @TraceID, 42, 4, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 51, @on
exec sp_trace_setevent @TraceID, 45, 4, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 22, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
-- XML Statistics Profile
exec sp_trace_setevent @TraceID, 146, 1, @on
exec sp_trace_setevent @TraceID, 146, 51, @on
exec sp_trace_setevent @TraceID, 146, 4, @on
exec sp_trace_setevent @TraceID, 146, 12, @on

-- Filter: Log only events for the provided @@spid
if @spId is not null
exec sp_trace_setfilter @TraceID, 12, 0, 0, @spID

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

goto finish

error:
select ErrorCode=@rc

finish:
end
go

Some annotations:


    

  • For the parameters, see the comments.
    • 

  • Don’t specify a string for the trace file. .TRC will be added automatically.
    • 

  • ensure that the output file doesn't already exist. Otherwise you’ll get an error.
    • 

  • very often I replace the code for beginning and stopping the trace inside “interesting code” inside a stored procedure. That is, I’m wrapping some more or less awkward code by starting and stopping a trace like this:
    • 



declare @traceID int
exec dbo.configureServerSideTrace @traceStatus = 1
                                 ,@traceID = @traceID output
                                 ,@maxFileSize = 10000
                                 ,@traceFileName = N'e:\MyTrace\Test'
                                 ,@spId = @@spid

-- 
-- Code of interest
-- 

exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID


HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu   is European Windows Hosting Provider which focuses on Windows Platform   only. We deliver on-demand hosting solutions including Shared hosting,   Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a  Service  for companies of all sizes.



    
    

    

        Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , 

        Categories: 

        Actions: 
        E-mail | 
        Kick it! |
        Permalink |
        
             commentComments (0)
         |   
        RSS comment feedComment RSS
    

    





      

            

            

            
About HostForLIFE
HostForLIFE is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.


We have offered the latest Windows 2019 Hosting, ASP.NET 5 Hosting, ASP.NET MVC 6 Hosting and SQL 2019 Hosting.

 






  









 


Month List


Tag cloud

            
                
Sign in

              
            

            

            



	
	








        

    
       
    

        Powered by BlogEngine.NET 1.5.0.7 | 
        Original Design by Arcsin, Adapted by RazorAnt