Category: sql-syntax

Oct 29 2013

Great article on SQL Command utility.

I thought I knew about the Sql Server sqlcmd utility ... until I read this article... https://www.simple-talk.com/sql/sql-tools/sql-server-sqlcmd-basics/ One of the coolest things I learned is that DOS has an "if exist" command. Seth
Jun 07 2013

AWESOME sql script for monitor the progress of a db restore

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE') Found at stackoverflow here...
http://stackoverflow.com/questions/152447/is-there-a-sql-script-that-i-can-use-to-determine-the-progress-of-a-sql-server-b
Apr 02 2011

Rebuild all index for all tables in Sql Server with one line of code.

Found this here... http://www.sqlservercurry.com/2008/06/rebuild-all-indexes-of-all-tables-in.html USE escore GO EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO Pretty cool. Seth
Jul 29 2010

Can you define a NULL on a column definition in the Create Table statement in a Firebird DB?

I am using SQL Manager Lite to try to run a DDL Create Table script. I am new to Firebird and I don't know why it isn't working. The following script...

create table Contacts (
    ID                      integer            not null,
    FirstName               varchar(64)        not null,
    LastName                varchar(64)        not null,
    MiddleInitial           varchar(1)             null
);

Is causing a parsing error (UNIDENTIFIED TOKEN) on the null constraint for the MiddleInitial column.

Here is the exact error returned by Sql Lite...

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 5, column 52.
Null.

Is the NULL constraint not allowed in Create Table DDL for firebird?

Jul 29 2010

Can you define a NULL on a column definition in the Create Table statement in a Firebird DB?

I am using SQL Manager Lite to try to run a DDL Create Table script. I am new to Firebird and I don't know why it isn't working. The following script...

create table Contacts (
    ID                      integer            not null,
    FirstName               varchar(64)        not null,
    LastName                varchar(64)        not null,
    MiddleInitial           varchar(1)             null
);

Is causing a parsing error (UNIDENTIFIED TOKEN) on the null constraint for the MiddleInitial column.

Here is the exact error returned by Sql Lite...

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 5, column 52.
Null.

Is the NULL constraint not allowed in Create Table DDL for firebird?

Jul 27 2010

Great SQL Server tip on easy way to flatten records from SQL into a delimited string

I found this tip today.  It flattens records from a select statement without using a loop.  Who'd a thunk it. http://ryanfarley.com/blog/archive/2005/02/17/1712.aspx

53 DECLARE @retval nvarchar(1024)

54

55 SET @retval = ''

56

57 SELECT @retval = @retval + i.FormattedName2 + ','

58 FROM <SELECT STATEMENT>

78

79 IF @retval<>''

80 SET @retval=left(@retval,len(@retval)-1)

81 ELSE

82 SET @retval = null

83 RETURN @retval

Seth
Apr 06 2010

To return a padded string from an INT in SQL do the following…

In my example, I wanted to pad the ID (int) of a table with leading zeros(0).  Here is the select to do it.
SELECT DivisionID, right( replicate( '0', 5 ) + cast( DivisionID as varchar(10)) ,5) as ID_STRING FROM tblDivisions
Notice that the number 5 in the statement has to be the number of chars (length) of the padded string.  The '0' is the padding.  This could just as easily be an space or and underscore. This select returns the following:
DivisionID  ID_STRING ----------- --------- 827         00827 825         00825 766         00766 767         00767 ...
Seth
Feb 18 2010

When dropping a constraint will the supporting indexes also be dropped?

Hello,

I am trying to memorize some sql syntax and I have gotten the ALTER TABLE ADD CONSTRAINT syntax down. I believe I am correct when I say that when you use this syntax to add a FOREIGN KEY or PRIMARY KEY constraint, that sql server automatically creates indexes to support the constraint operations. (Is that true...or is it only true on the PK but not the FK?)

If so, when you use the ALTER TABLE DROP CONSTRAINT syntax...are the supporting indexes automatically dropped as well? Can these implicit supporting indexes be explicitly dropped? If so is the CONSTRAINT automatically removed?

I am just wanting to know how it works "under the covers". Googling has not helped. I imagine I could have queried some sys tables to discover the truth but thought I would try here instead.

Thanks for your help.

Seth

Feb 08 2010

SQL Server PIVOT syntax

Here is the Sql Server PIVOT syntax
-----------------------------------------------
SELECT virtual_columns_list-actual_data_values-EXAMPLE
     data1_value as col1_name,
     data2_value as col2_name
FROM
    (source_select_statement-columns) ps
    PIVOT
    (
        SUM (field_to_aggregate)
        FOR field_to_pivot IN
        (
              actual_data_values-EXAMPLE
              data1_value,
              data2_value
        )
    ) AS pvt
Dec 22 2009

How to you WEIGHT returned values by a Group By value

I am trying to determine a SCORE from 11 rows in a table.

Those 11 rows are being aggregated into five rows using a ScoringCategoryID column as follows...

ScoringCategoryID   CategoryScore       PercentOfTotal
---------------------------------------------------------
7                   15.00	            0.40  
8                   15.00	            0.30  
9                   14.50	            0.20  
10                  4.50	            0.05  
11                  4.50	            0.05

I need to get a RawScore from this data. Unfortunately my customer does not want me to merely sum the CategoryScore column (53.5 total). Do you see how the PercentOfTotal column sums to 1. Each ScoringCategoryID therefore has a WEIGHT. So of the total score...ScoringCategoryID is supposed to be 40% of the score. ScorCatID 8 is supposed to be 30% of the total etc.

I am not sure how to do this in a query. How do I get the Score?

Here is the current query...

SELECT jc.ScoringCategoryID, 
       SUM(etjs.CalculatedScore) as CategoryScore,  
       Max(sc.PercentOfTotal) PercentOfTotalScore  
  FROM tblEventTurnJudgeScores etjs  
INNER JOIN tblJudgingCriteria jc ON  jc.JudgingCriteriaID = etjs.JudgingCriteriaID  
INNER JOIN tblScoringCategories sc ON jc.ScoringCategoryID = sc.ScoringCategoryID  
WHERE etjs.EventTurnJudgeID = 1068  
GROUP BY jc.ScoringCategoryID

This is supposed to be aggregated into a single score. Any ideas.