User Windows login name has been changed in AD yet session in SQL 2008 Profiler shows the old Windows login name Background:  a user had her name changed in Active Directory from Domain\oldname to Domain\newname and successfully logged into the network with Domain\newname and yet  Domain\oldname appears in columns LoginName and NTUserName in Profiler.

User's SQL Server permissions are granted via a group set up in SQL Server security.

The question:  Has anyone observed this behavior and know why SQL Server is still picking up Domain\oldname (sp_who2 shows same information)?

BTW, have spent considerable time researching before posting question here and it appears to me that is no real issue with Profiler/SQL 2008 R2.

I guess I can't comment on your question since this is the first time I've used stack exchange but is this an AD group that the user is in?  Sometimes people say group when they mean SQL role so I just wanted to get some clarification.

The reason I ask is because if the user has a login on the server of domain\olduser when you change the name in AD to domain\newuser you have to go into SQL and change the login to domain\newuser.  I've done this and it works perfectly fine and you don't have to do anything as drastic as restart SQL.  I do not know if the account name will eventually change to the new one over time but my guess would be no.

I do not know, however, what happens when the user is just part of an AD group that has permissions and doesn't actually have a login on the server.  Hence the need for clarification so I can look into that.


Update sys.job$ set this_date=:1 where job=:2 as one of the most time-consuming queries - oracle I am currently helping some organization with their Oracle database performance. They are using Oracle 11gR2 Standard Edition in Windows 2008. As  there are no enterprise performance packages available on this standard edition, I've had to use v$sql to query longest-running ones to identify performance bottlenecks. Using the query below :

SELECT *
FROM   (
              SELECT sql_text,
                     sql_id,
                     elapsed_time,
                     plsql_exec_time,
                     cpu_time,
                     executions,
                     fetches,
                     loads,
                     buffer_gets,
                     elapsed_time / executions
              FROM   v$sql -- order by elapsed_time / executions)         )  where rownum <= 10;

It returned some queries which I will look into, but there are two rows in the results that I cannot understand the reason why they should exist here:

As you can see, the query executes on sys.job$, so it should be related to oracle jobs set on this database. My questions are :

1) What does these two rows mean? Should I query dba_jobs table for more information? How should I query them and what should I look for?

2) Both of the queries look identical, why they are echoed twice in the query result?

If that is your most time consuming query, then your database does almost nothing.

That is 0.211599 and 0.002923 seconds per execution with a total runtime of 39.9 and 1.7 seconds.

order by elapsed_time / executions)         )  where rownum <= 10;

This returns the shortest running queries, not the longest-running ones. Use order by ... desc.

No. This is an internal statement that updates the dictionary with the start time of a job on starting it. There is nothing to investigate about it.
You should find this information in  V$SQL_SHARED_CURSOR. Look for the column with a value of Y, and the name of that column specifies the reason for having another child cursor.


Trace Flag 3688 not working on SQL Server 2014 We are using some third party tools that fill up the SQL Server logs with entries like:

SQL Trace ID 1 was started by login "sa".
SQL Trace ID 2 was started by login "XX\xxxxx".
SQL Trace stopped. Trace ID = '2'. Login Name = 'XX\xxxxx'.
SQL Trace ID 2 was started by login "XX\xxxxx".

I am trying to suppress these by enabling TF 3688 but it is not working on SQL Server 2014, 2012 or SQL 2008 R2. Here is a snapshot of the configuration. Please let me know if I am missing anything:

I have restarted the instance and verified that the trace flag is active using DBCC TRACESTATUS:

That trace flag isn't documented and shouldn't be used in production environments.

That said, it appears that the trace flag stopped working in SQL Server 2008 R2 and newer.  As it's undocumented Microsoft won't take a support ticket to fix it as you shouldn't have been using an undocumented trace flag.

All undocumented commands and features can be changed at Microsoft's whim.


How to capture missing information in SQL Server tables I have a few tables with nullable columns in a SQL Server database. Those nullable attributes are either not supplied at the time of row creation or they are not applicable in some cases.

I realized that I cannot use the same NULL value to mean 'Not Supplied' in some cases and 'Not Applicable' in other cases. These optional columns are of type int, datetime, and string. Even if I mark string columns as mandatory (NOT NULL) and set the attributes to either 'Not Supplied' and 'Not applicable' as appropriate, what can I do for int and datetime columns? I read chapter 23 from the book:

Database Explorations: Essays on The Third Manifesto by C.J. Data and Hugh Darwen

Looks like the column Status only has numeric values but since the authors have set 'n/a' and 'd/k' (don't know), not sure if they created Status as a string column. Is it a good idea to create all the optional columns as string columns and capture the reasons in the attributes when they don't have a value?

Retain the correct data types in each column.  Use what is referred to as a magic number to indicate "not applicable", or "unknown".

For instance, with an int column, you might use -2147483647 to indicate "not applicable", and -2147483646 to indicate "unknown", presuming those two values do not show up in the "normal" data.  For dates, use values like N'1900-01-01T00:00:00' and N'1900-01-01T00:00:01'.  Of course, you'll need to programmatically exclude these values when performing statistical analysis or reporting, since they would skew things like averages and sums.

An example table containing an int column using magic numbers:

CREATE TABLE dbo.Items
(
    SomeValue int NOT NULL
    , CONSTRAINT CK_SomeValue
        CHECK (
            SomeValue = -2147483647 /* Not Applicable */
            OR SomeValue = -2147483646 /* Unknown */
            OR (SomeValue > 0 AND SomeValue < 2147483648)
            )
);

Note, the check constraint that "documents" the magic/canary values.  As Erik Darling details in a blog post, canary values remove the need for the column to allow NULLs, which in turn can alleviate some nasty surprises when using NOT IN (...) and JOIN ON ISNULL(...) = ISNULL(...), etc.

An example may be the best way to show the problems associated with using magic/canary values, and using NULL values instead.  We'll do this work in tempdb, and create two almost identical tables.  One uses magic/canary values, one uses NULL instead.

USE tempdb;

IF OBJECT_ID(N'dbo.ItemsNoNulls', N'U') IS NOT NULL
DROP TABLE dbo.ItemsNoNulls;
IF OBJECT_ID(N'dbo.ItemsNulls', N'U') IS NOT NULL
DROP TABLE dbo.ItemsNulls;

CREATE TABLE dbo.ItemsNoNulls
(
    SomeValue int NOT NULL
    , CONSTRAINT CK_ItemsNoNulls_SomeValue
        CHECK (
            SomeValue = -2147483647 /* Unknown */
            OR SomeValue = -2147483646 /* Not Applicable */
            OR (SomeValue > 0 AND SomeValue < 2147483648)
            )
);

CREATE TABLE dbo.ItemsNulls
(
    SomeValue int NULL
    , CONSTRAINT CK_ItemsNulls_SomeValue
        CHECK (
            SomeValue IS NULL
            OR (SomeValue > 0 AND SomeValue < 2147483648)
            )
);

Here, we'll insert a load of rows where 2% of the rows are "magic" values of either -2147483647 or -2147483646:

;WITH src AS (
    SELECT val = ABS(CONVERT(int, CRYPT_GEN_RANDOM(4)))
    FROM sys.columns c1
        CROSS JOIN sys.columns c2
)
INSERT INTO dbo.ItemsNoNulls(SomeValue)
SELECT CASE src.val % 50
    WHEN 1 THEN -2147483647
    WHEN 2 THEN -2147483646
    ELSE src.val
END
FROM src;

This will insert those previously generated rows into the table where NULL represents Unknown or Not Available:

INSERT INTO dbo.ItemsNulls (SomeValue)
SELECT CASE inn.SomeValue
        WHEN -2147483647 THEN NULL
        WHEN -2147483646 THEN NULL
        ELSE inn.SomeValue
    END
FROM dbo.ItemsNoNulls inn

Compare the output of these two simple queries:

SELECT [Avg] = AVG(CONVERT(bigint, SomeValue))
    , [Count] = COUNT(SomeValue)
FROM dbo.ItemsNoNulls;

╔═══════════╦════════╗
║ Avg       ║ Count  ║
╠═══════════╬════════╣
║ 944303347 ║ 549081 ║
╚═══════════╩════════╝

SELECT [Avg] = AVG(CONVERT(bigint, SomeValue))
    , [Count] = COUNT(SomeValue)
    , [CountNull] = COUNT(COALESCE(SomeValue, 0))
FROM dbo.ItemsNulls

╔════════════╦════════╦═══════════╗
║ Avg        ║ Count  ║ CountNull ║
╠════════════╬════════╬═══════════╣
║ 1073162998 ║ 527112 ║ 549081    ║
╚════════════╩════════╩═══════════╝

The "average" in the first resultset is artificially low because of the presence of the canary values.  The "count" in the second query is lower than expected since aggregates eliminate NULL values, as shown by the warning:

  Warning: Null value is eliminated by an aggregate or other SET operation.

If we modify the first query to eliminate the canary values:

SELECT [Avg] = AVG(CONVERT(bigint, SomeValue))
    , [Count] = COUNT(SomeValue)
FROM dbo.ItemsNoNulls
WHERE SomeValue <> -2147483647
    AND SomeValue <> -2147483646;

We get the correct average value, however the count no longer reflects the actual number of rows in the table:

╔════════════╦════════╗
║ Avg        ║ Count  ║
╠════════════╬════════╣
║ 1073162998 ║ 527112 ║
╚════════════╩════════╝

To get the accurate average, and an accurate count, we need to do something like:

SELECT [Avg] = AVG(CONVERT(bigint, SomeValue))
    , [Count] = (SELECT COUNT(SomeValue) FROM dbo.ItemsNoNulls)
FROM dbo.ItemsNoNulls
WHERE SomeValue <> -2147483647
    AND SomeValue <> -2147483646;

╔════════════╦════════╗
║ Avg        ║ Count  ║
╠════════════╬════════╣
║ 1073162998 ║ 549081 ║
╚════════════╩════════╝

As you can see, both NULL columns and magic/canary values introduce some unexpected problems that can be bad for reporting.  Making sure you and other team members fully understand the implications is extremely important for accuracy.

One other method of recording "unknown" or "not applicable" values is to allow NULL values on those columns, combined with a separate column that indicates the status of the value.  For example:

USE tempdb;
IF OBJECT_ID(N'dbo.Items', N'U') IS NOT NULL
DROP TABLE dbo.Items;
IF OBJECT_ID(N'dbo.StatusValues', N'U') IS NOT NULL
DROP TABLE dbo.StatusValues;

CREATE TABLE dbo.StatusValues
(
    StatusValue int NOT NULL
        CONSTRAINT PK_StatusValues
        PRIMARY KEY CLUSTERED
    , StatusDescription varchar(30) NOT NULL
);

INSERT INTO dbo.StatusValues (StatusValue, StatusDescription)
VALUES (0, 'Normal')
    , (1, 'Unknown')
    , (2, 'Not Applicable');

CREATE TABLE dbo.Items
(
    SomeValue int NULL
    , SomeValueStatus int NOT NULL
        CONSTRAINT FK_StatusValue
        FOREIGN KEY REFERENCES dbo.StatusValues(StatusValue)
    , CONSTRAINT CK_ValueStatus
        CHECK ((SomeValue IS NULL AND (SomeValueStatus = 1 OR SomeValueStatus = 2))
            OR (SomeValue IS NOT NULL AND (SomeValueStatus = 0)))
);

This series of statements shows how the check constraint prevents invalid inserts:

--this fails, since 0 indicates a status of "normal"
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (NULL, 0);

--this succeeds since status "1" is Unknown
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (NULL, 1);

--this succeeds since status "2" is Not Applicable
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (NULL, 2);

--this succeeds since the SomeValue column contains a non-null value
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (1, 0);

--this fails since "Unknown" is incompatible with a non-null SomeValue
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (1, 1);

--this fails since "Not Applicable" is incompatible with a non-null SomeValue
INSERT INTO dbo.Items (SomeValue, SomeValueStatus)
VALUES (1, 2);

The contents of the table:

SELECT i.SomeValue
    , sv.StatusDescription
FROM dbo.Items i
    INNER JOIN dbo.StatusValues sv ON i.SomeValueStatus = sv.StatusValue;

╔═══════════╦═══════════════════╗
║ SomeValue ║ StatusDescription ║
╠═══════════╬═══════════════════╣
║ NULL      ║ Unknown           ║
║ NULL      ║ Not Applicable    ║
║ 1         ║ Normal            ║
╚═══════════╩═══════════════════╝

The inserts above that are invalid will cause the following error to be returned to the client:

  Msg 547, Level 16, State 0, Line 30
  The INSERT statement conflicted with the CHECK constraint "CK_ValueStatus". The conflict occurred in database "tempdb", table "dbo.Items".


Syntax error in SQL Server while making Dynamic SQL Scenario:

I have a table with 100M+ rows of data so I have created an index with something as:

CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt
    ON dbo.base_voter (name_voter,home_street_address_1, home_address_city)
    WHERE deleted_at IS NULL ;

Now when I execute the following code, it executed in less time:

SELECT id,
name_voter,
home_street_address_1,
home_address_city
FROM dbo.base_voter
WITH(NOLOCK)
WHERE deleted_at IS NULL
order by name_voter asc
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Now, I want to create this query as dynamic SQL as these parts name_voter asc, 0 , 50 need to be dynamic and will be sent through the backend.

But, when I try to execute

DECLARE @sql nvarchar(MAX);
DECLARE @params nvarchar(1000);

SET @sql =
     N'SELECT id, name_voter, home_street_address_1,home_address_city
       FROM dbo.base_voter
       WITH(NOLOCK)
       ORDER BY @sort_col @sort_dir
       OFFSET @offset ROWS
       FETCH NEXT @limit ROWS ONLY';
SET @params = N'@sort_col nvarchar(100),@sort_dir nvarchar(10), @offset int, @limit int';

EXECUTE sp_executesql @sql, @params,
                      'name_voter','asc', 0, 50;

I receive error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@sort_dir'.
Msg 153, Level 15, State 2, Line 9
Invalid usage of the option NEXT in the FETCH statement.

I am not sure but is it because I am passing column name as a parameter? Or is there some other thing. I just want that query to be executed while preventing from SQL injection and with minimal execution time.

I will be here if you need any further information.

from the comments of @sepupic and @Scott Hodgin, I put this together:

conditional order by

DECLARE @sql nvarchar(MAX);
DECLARE @params nvarchar(100);
declare @sort_col nvarchar(20) = 'name_voter'
declare @sort_dir nvarchar(20) = 'asc'

SET @sql =
     N'SELECT id, name_voter, home_street_address_1,home_address_city
       FROM dbo.base_voter
       WITH(NOLOCK)' + NCHAR(10) +
      N'ORDER BY ' + @sort_col + SPACE(2) + @sort_dir + NCHAR(10) +
      N'OFFSET  @offset  ROWS ' + NCHAR(10) +
      N'FETCH NEXT  @limit  ROWS ONLY; '

SET @params = N' @offset int, @limit int';

--print @sql

EXECUTE sp_executesql @sql, @params, 0, 50;


How to Identify Who or What is Restarting SQL services via Service Control? I investigated an instance that experienced an unexpected restart and came across the usual service control event but no user login associated with it. Is it still possible that an authorized user initiated the restart or is this due to Windows issuing the command? If an authorized user, outside of the SQL error logs and event logs, where could I go about attempting to identify the user?

Log Name:      System
Source:        Service Control Manager
Date:          12/24/2014 9:02:24 AM
Event ID:      7036
Task Category: None
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      XXX
Description:
The SQL Server Agent (MSSQLSERVER) service entered the stopped state.

If you have the default trace running, and you should have it running, it may be able to help you see what happened. The default trace is lightweight and among other things does track Server Stop and Server Start. See Feodor Georgiev's article:

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

The trace logs recycle fairly quickly on a busy server, so you would need to check as soon as possible after a Server Stop and Server Start. Toward the bottom of the heading Security Audit Events there is code to ‘Audit Server Starts and Stops’.  (Tweaked only slightly to include the default count of trace logs.)

SELECT  TE.name AS [EventName] ,
        v.subclass_name ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.StartTime ,
        t.SessionLoginName
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), 4) T  -- 4 is the default number of trace log files
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
                                            AND v.subclass_value = t.EventSubClass
WHERE   te.name IN ( 'Audit Server Starts and Stops' )


Restoring a mysql database using --single-transaction I learned that specifying "--single-transaction" (yes we do have InnoDB) option into the mysqldump tool produces a dump utilizing transactions. However, I am failing to find a way to restore the database from my dump file while also utilizing transactions. That is I have no idea how to make the dump file in such a way that it's execution will restore the dumped databases in a single transaction, without issuing any locks.

Anyone encountered such problem?

--single-transaction is used to ensure the dump is consistent when dumping, it has nothing to do with import/restore.

Loading a dump is not generally possible in a single transaction because many CREATE statements do an implicit commit.

It is often not even desirable because for a rollback to be possible, the transaction needs to keep undo logs and if you import many rows over many tables, the logs will grow too much, eat memory and disk space and slow down the import and all other operations.

If your issue is import speed, check that extended inserts are being used in the dump (that each insert statement inserts multiple rows at once) and you have proper buffer pool and log size configured for InnoDB.

If you want to import the data without issuing any locks as stated in your question then I am afraid that's not possible, because DML (inserts, updates, deletes) just have to lock to keep things ACID.

You should not restore from a dump a database of any service while it is running and restoring a specific database to a server instance hosting other databases should not issue any locks hindering these other services.


sp_tables blocking my other statements. Who runs it? In our application we sporadically experience a locking issue caused by the execution of an sp_tables statement.

We use SQL Server 2012. In our code and queries in our stored procedures we don't make use of sp_tables.

How can I trace who is the responsible of this call? At least how can I log when this call happens?

I read that it's also called by third parties, in this case how can I track who is responsible?

Edit:

in our application we use Hibernate as ORM and c3p0 for connection pooling.

EDIT 2:
we didn't succed in understanding when and by who the sp_tables was called but today we succeded in replicate the issue.

Seems that the sp_tables is called in responde to a failed delete tried on the schema.

The delete fails because there are other rows referencing the row that we try to delete, so we get an exception

SQLServerException: The DELETE statement conflicted with the REFERENCE constraint..

Is there evidence somewhere that this kind of errors triggers an sp_tables on the db?

You can set up an extended event session with following code. I tested this and it works (at least the way I was calling).

    CREATE EVENT SESSION [CallSP_tables] ON SERVER ADD EVENT
    sqlserver.sp_statement_completed
   (SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,
    sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,
    sqlserver.plan_handle,sqlserver.query_hash)
    WHERE ( [sqlserver].[like_i_sql_unicode_string]
            ([object_name],N'sp_tables')
        )
    ),
ADD event sqlserver.sql_statement_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash))
ADD TARGET package0.event_file(SET filename=N'C:\callspTables.xel',
        max_file_size=(50),
        max_rollover_files=(100)) WITH (max_memory=4096 kb,
        event_retention_mode=allow_multiple_event_loss,
        max_dispatch_latency=120 seconds,
        max_event_size=0 kb,
        memory_partition_mode=none,
        track_causality=OFF,
        startup_state=ON)
        GO


Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL? I'm using PostgreSQL 9.3.5.

Suppose I have the following table:

CREATE TEMPORARY TABLE point_test ("name" varchar(255), "pt" point);

I then insert a row, leaving pt NULL:

INSERT INTO point_test (name) VALUES ('me');

Then, I want to update pt using the array-like syntax:

UPDATE point_test SET pt[0] = 42, pt[1] = -42 WHERE name = 'me';

which appears to succeed (UPDATE 1) - EXPLAIN VERBOSE shows the following:

Update on pg_temp_65.point_test  (cost=0.00..11.75 rows=1 width=538)
  ->  Seq Scan on pg_temp_65.point_test  (cost=0.00..11.75 rows=1 width=538)
        Output: name, (pt[0] := 42::double precision)[1] := (-42)::double precision, ctid
        Filter: ((point_test.name)::text = 'me'::text)

However, pt is still NULL. If I use a slightly different syntax, it works in this case:

UPDATE point_test SET pt = '(42,-42)' WHERE name = 'me';

results in the point (42,-42) as expected.

Further, now that there is something in the field, I can update the point using the first syntax:

UPDATE point_test SET pt[0] = 84, pt[1] = -84 WHERE name = 'me';

results in the point (84,-84) as expected.

Why do the behaviors of the two syntaxes differ only when the pt column is NULL?

Per documentation:

  It is possible to access the two component numbers of a point as
  though the point were an array with indexes 0 and 1.

But geometric types are not arrays.
 While the value as a whole can be NULL, parts of a point (or any geometric type) cannot.

All geometric types can be input as string literals and cast to the respective type. None of them accept NULL as part of the input. This results in an error message:

SELECT '(NULL,1)'::point;

  ERROR:  invalid input syntax for type point: "(NULL,1)"
  LINE 1: SELECT '(NULL,1)'::point

On the other hand, if you try passing one or more NULL values to a constructor function, you get NULL for the whole value - which is a bit inconsistent, but still makes sense: if one component is unknown, the whole value is unknown. All of these tests return TRUE:

SELECT point(NULL, NULL) IS NULL
      ,point(1, NULL) IS NULL
      ,point(NULL, 1) IS NULL
      ,point(NULL, 1) IS NULL
      ,circle(point(1, 1), NULL) IS NULL;

This leaves the case where an UPDATE silently ignores an assignment of NULL to pt[0] or pt[1]. This should really do one of two things:

Set the point to NULL - like SELECT point(NULL,1) does.
Raise an exception - like SELECT '(NULL,1)'::point does.

I would say this is a bug and should be reported.


How to import DB with .sql extension into SQL Server 2012? I have a database with .sql extension which is exported from server using putty.

How to import this into SQL Server 2012 in Management Studio?

It is not accepting .sql as database, throwing error.

If I try to restore using

RESTORE HEADERONLY FROM DISK = N'D:\LogFile\accesslog.sql';

I am getting the following error:

  Msg 262, Level 14, State 1, Line 1
  CREATE DATABASE permission denied in database 'master'.

  Msg 3013, Level 16, State 1, Line 1
  RESTORE HEADERONLY is terminating abnormally.

Please help me with this.

From BOL:

  If the database being restored does not exist, the user must have
  CREATE DATABASE permissions to be able to execute RESTORE. If the
  database exists, RESTORE permissions default to members of the
  sysadmin and dbcreator fixed server roles and the owner (dbo) of the
  database (for the FROM DATABASE_SNAPSHOT option, the database always
  exists).

You're creating a database on your server, regardless of the fact that it already existed on another server, therefore you need CREATE DATABASE permissions.


CPU vs Elapsed & Parallelism I have read threads here and here and I get that elapsed time is the total duration of the task - and if the elapsed time is less than CPU time, the query went parallel.

After typing that, I was trying to improve a stored procedure's performance in an area in which we are experiencing some slowness.

The existing TSQL:
Paste the Plan

As you can see, we have 6 parameters that are all optional - a customer lookup where you can use a variety or just one variable to search.

When executed, this procedure goes parallel and the statistics IO and time are as follows:

My initial thought was to rewrite the WHERE clause to replace the COALESCE & ISNULL functions to simply ( @paramCustomerID IS NULL or c.id = @paramCustomerID )

And because I find CTE's more readable than a sub-query, I did change that portion of the query as well.

Here is the execution plan for the rewrite: Paste the Plan

And the results of the Statistics IO & Time:

The logical reads from the Customers table was cut by nearly a 1/3 and the CPU time was drastically cut, but the elapsed time is nearly double: new version was 1.2 seconds to .545 for the existing version.

I'm not an expert by any means and I am trying to learn, but the main differences I see is that the new version is performing a Key Lookup and the existing version is using Parallelism.

The advice or knowledge I'm hoping to gain here is which version of the stored procedure would give the best performance? And if the new version should be better, is there anything that could be done to make it run parallel so the elapsed time would be shorter?

Trying to clarify the question -

1) This maybe purely subjective and possibly frowned upon on this site, but based on the information provided; which procedure would you use to get the results to the end user the quickest? The proc with COALESCE/ISNULL functions in the WHERE clause the goes parallel or the revised procedure that has fewer logical reads but a greater elapsed time?

2) If we choose not to use dynamic SQL, what advice would you give to improve query performance for the revised procedure?

As I am typing the edits to try and clarify, I do see that Max has given some very useful information.

Just wanted to add, the Statistic Parser information was provided by this site.

Your first query plan shows parallelism, whereas your second query is purely serial; this is why the second version is showing longer "duration".

The key lookup operations could be prevented by a suitable covering index for the tables where the key lookup is occurring.  The standard warning about not blindly creating indexes applies here - don't create duplicate indexes, and check to see if you can leverage an existing index by possibly adding an include clause.  For instance, the key lookup on the Customers table is pulling these columns, which it couldn't get by scanning the IX_CustomersSocialSecurityNumber index:

[GoOutdoorsTN_TEST].[dbo].[Customers].driversLicenseNumber
, [GoOutdoorsTN_TEST].[dbo].[Customers].lastName
, [GoOutdoorsTN_TEST].[dbo].[Customers].DocTypeNumber
, [GoOutdoorsTN_TEST].[dbo].[Customers].driversLicenseState

If you added those columns to the index in an INCLUDE clause, that scan would not need to go back to the table to get those columns, making the output that much faster.

Your query uses the "kitchen sink" pattern; i.e. this:

WHERE (@x IS NULL OR someCol = @x)
     AND (@y IS NULL OR someOtherCol = @y)

You can typically get much better query plans, customized for each variation, using dynamic SQL instead of the @x IS NULL piece.  Pseudo-code would be:

IF @x IS NULL AND @y IS NOT NULL
    SET @where = 'WHERE someOtherCol = @y';
IF @y IS NULL AND @x IS NOT NULL
    SET @where = 'WHERE someCol = @x';
IF @y IS NULL AND @x IS NULL
    SET @where = '';

This allows the query optimizer to use column statistics in a far more effective manner, since it only needs to think about the columns presented in each unique where clause.

Also of note, I see you're using WITH (NOLOCK) in an effort to prevent your query being affected by blocking.  You may want to ensure you understand the effects of reading uncommitted rows inherent in the READ UNCOMMITTED isolation level used by the NOLOCK hint.  Aaron Bertrand has a great article about that here

I've noticed the plans show a couple of computer scalar operators where you're doing:

= LTRIM(RTRIM(lastName))

Does your data really have blank space around the real content of lastName?  If not, getting rid of those needless functions will really help the query processor provide better plans.

As a way of showing how you might approach the kitchen sink problem, and strictly for learning purposes, consider the below code.

CREATE TABLE dbo.Customers
(
    CustomerID int NOT NULL
        CONSTRAINT PK_Customers
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FirstName nvarchar(100) NOT NULL
    , LastName nvarchar(100) NOT NULL
    , SSN char(9) NULL
);

Some sample data:

INSERT INTO dbo.Customers (FirstName, LastName, SSN)
VALUES ('Joe', 'Belfiore', '012345678')
    , ('Bill', 'Gates', '876543210')
    , ('Max', 'Vernon', '123123123');
GO

A stored procedure to perform searches:

CREATE PROCEDURE dbo.SearchCustomers
(
    @FirstName nvarchar(100) = NULL
    , @LastName nvarchar(100) = NULL
    , @SSN varchar(9) = NULL
)
AS
BEGIN
    /*
        BE AWARE THIS IS PROTOTYPE CODE THAT IS NOT SAFE
        AGAINST SQL INJECTION VULNERABILIES.

        IT IS STRICTLY TO SHOW HOW TO COMPILE A DYNAMIC
        WHERE CLAUSE!
    */
    SET NOCOUNT ON;
    DECLARE @Where nvarchar(max);
    DECLARE @connector nvarchar(max);
    DECLARE @qry nvarchar(max);
    SET @qry = 'SELECT CustomerID, FirstName, LastName, SSN
FROM dbo.Customers c
';
    SET @where = 'WHERE ';
    SET @connector = '';
    IF @LastName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + 'c.LastName LIKE ''%' + @LastName + '%''';
        SET @connector = ' AND ';
    END
    IF @FirstName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + 'c.FirstName LIKE ''%' + @FirstName + '%''';
        SET @connector = ' AND ';
    END
    IF @SSN IS NOT NULL
    BEGIN
        SET @where = @where + @connector + 'c.SSN LIKE ''%' + @SSN + '%''';
        SET @connector = ' AND ';
    END
    IF @connector <> '' SET @qry = @qry + @Where + ';';
    EXEC sys.sp_executesql @qry;
    PRINT @qry;
END
GO

Some test searches:

EXEC dbo.SearchCustomers @FirstName = N'Max';
EXEC dbo.SearchCustomers @LastName = N'Vernon';
EXEC dbo.SearchCustomers @SSN = N'994', @LastName = N'V'

The queries show in the "Messages" tab are:

SELECT CustomerID, FirstName, LastName, SSN
    FROM dbo.Customers c
    WHERE c.FirstName LIKE '%Max%';

SELECT CustomerID, FirstName, LastName, SSN
    FROM dbo.Customers c
    WHERE c.LastName LIKE '%Vernon%';

SELECT CustomerID, FirstName, LastName, SSN
    FROM dbo.Customers c
    WHERE c.LastName LIKE '%V%' AND c.SSN LIKE '%994%';

Before you implement that code, you really need to read Erland Sommarskog's seminal work on dynamic SQL.  He also has a great article about dynamic search which should help.


Can sharding speed up insertion performance in postgresql Referenced from this question, the most popular answer does not mention sharding. As far as I know, postgresql will acquire auto-inc lock every time it inserts, so I think sharding can improve insertion performance. Am I right? If sharding can really improve performance, how does it benefit and cost compared other methods?

As has been clarified in the comments, accessing a sequence will not be a bottleneck in PostgreSQL.
If you are faster inserting into a sharded database depends on where the bottleneck is.
If it is disk I/O or CPU on the database, you will probably be faster spreading the workload across several databases (sharding), as long as they have independent storage and network bandwidth is not a bottleneck.
If the bottleneck is on the client side, you probably won't gain much.


use of crows foot or chens notation to draw an extended ER diagram I am trying to understand if we can use crows foot or chens notation to get an extended ER diagram.

or does extended ER diagram has its own notations that should be used ?

From what i understand, crows foot or chens notations do not support class/subclass, inheritance/generalization representations using their existing items.

Please correct me if I have got the concept of ER and EER diagrams wrong.

It seems to me that you should be able to draw your ERD in the same way that you would for a regular relational database. Take a look at the wiki article for Object-Relational databases; there is a lovely example image on the page that might be of use.

While the functionality of an object-relational database might be somewhat different than that of a regular relational database (joins are understood etc.), it seems that they can both be represented in the same way.

It should appear that it is not so much the design of the database that is different, but instead the functionality of the underlying database management system. Crow's foot notation is currently the recommended standard.


SSRS 2016 report is failing with 'EXCELOPENXML' render format error Few reports are failing with 'ExcelOpenXML' render format error after rs migration from 2014 to 2016.
I made following change to the RS config file so I can use old excel format however some of the reports are using 'ExcelopenXML' format.
How can I use both 'Excel' and 'ExcelOpenXML' format so report will be sent out based on render format setup in subscription?

<!--Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/-->
<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>
<Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/>
<!--Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/-->
<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>
<Extension Name="WORDOPENXML" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/>

It looks like you have a couple of issues going on here.

You have the *OPENXML extension paths listed for both the legacy and new versions of both Excel and Word.  It looks like the proper extension paths for the legacy renderers are commented out in your config file.
The other issue is that you've got the *OPENXML extensions listed with the visibility flag set to false.

I think if you change the lines listed in your question to the following, all renderer options should show up in the dropdown on the subscription page:

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
<Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>
<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering">
<Extension Name="WORDOPENXML" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>

More information on the Excel renderers can also be found here.


How to identify the column used to partition a table from the Postgres system catalogs Given a table created as so...

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

How do I identify which column it's been partitioned on? - in this case 'logdate' solely by querying the postgres catalog.

I've looked in the obvious places in the catalog (pg_class, pg_index) but nothing springs out.

(Using version 10.5)

I'm not a PostgreSQL pro, but digging a bit I've founded a solution that perhaps can help you. (Works only for version 10 or above)

First I've slightly modified you table by adding two columns to the partition definition (just to show you the final result):

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (city_id,logdate);

This is my solution:

select
    par.relnamespace::regnamespace::text as schema,
    par.relname as table_name,
    partnatts as num_columns,
    column_index,
    col.column_name
from
    (select
         partrelid,
         partnatts,
         case partstrat
              when 'l' then 'list'
              when 'r' then 'range' end as partition_strategy,
         unnest(partattrs) column_index
     from
         pg_partitioned_table) pt
join
    pg_class par
on
    par.oid = pt.partrelid
join
    information_schema.columns col
on
    col.table_schema = par.relnamespace::regnamespace::text
    and col.table_name = par.relname
    and ordinal_position = pt.column_index;

schema                      | table_name  | num_columns | column_index | column_name
:-------------------------- | :---------- | ----------: | -----------: | :----------
fiddle_ctcqwfrzpcyngmgnqkdy | measurement |           2 |            1 | city_id
fiddle_ctcqwfrzpcyngmgnqkdy | measurement |           2 |            2 | logdate

db<>fiddle here

pg_partitioned_table

  The catalog pg_partitioned_table stores information about how tables
  are partitioned.

Unnesting partattrs you can get column index of each row involved in the partition. Then you can join information_schema.columns just to retrieve the name of every column.

+-----------+------------+---------------------+-------------------------------------------------------------|
| partattrs | int2vector | pg_attribute.attnum | This is an array of partnatts values that indicate          |
|           |            |                     | which table columns are part of the partition key.          |
|           |            |                     | For example, a value of 1 3 would mean that the first       |
|           |            |                     | and the third table columns make up the partition key.      |
|           |            |                     | A zero in this array indicates that the corresponding       |
|           |            |                     | partition key column is an expression, rather than a simple |
|           |            |                     | column reference.                                           |
+-----------+------------+---------------------+-------------------------------------------------------------|


In Postgresql, is it possible to change the maximum number of columns a table can have? I'm tasked with keeping a postgres table in sync with a non-postgres table. The non-postgres table has more than 1600 columns. When I try to create the table in postgres, I get:

  ERROR:  tables can have at most 1600 columns

I want to know if it is possible to raise the maximum number of columns. Is there a configuration variable I can change? Do I have to compile postgres with special options? Is there a good reason why the maximum number of columns is 1600 instead of 1700?

I have searched for this answer on Google and all answers are essentially: "you should never have a table with 1600 columns".

There's no run-time configuration option that will let you have more than about 1600 columns in a table.  You can redefine values and compile PostgreSQL from source. That might turn out to be harder than it sounds in your case.

A quick look at the source code seems to say the number of columns depends on the values of MaxTupleAttributeNumber and MaxHeapAttributeNumber. These are defined in src/include/access/htup_details.h.

So you might be able to increase the number of columns by

redefining those values, while
paying close attention to how their current values have been determined, then
recompiling.

Details are in source code comments.  Search for htup_details.h in the search dialog there.

I suggest you try building from source without making any changes first. After you can do that well, then try making the changes above. Consider digging around in the source code to determine whether there are any other dependencies that are not as well documented as these two.


As a minor note, COPY .. (WITH BINARY) doesn't have brackets. It's the flags (which represent amongst other things the brackets).

COPY ... (WITH BINARY)

From the docs on COPY

  To determine the appropriate binary format for the actual tuple data you should consult the PostgreSQL source, in particular the *send and *recv functions for each column's data type (typically these functions are found in the src/backend/utils/adt/ directory of the source distribution).

Further the docs say binary format (currently) has

11 bytes signature
4 bytes for flags
4 byte potential variable-width field, currently not in use so we skip the size (4 byte \0\0\0\0) this technically not nice. If these four bytes had 15, we'd have to skip over not just the four, but an addition 15.

Then the tuple has

2 bytes for field count

Then the fields have

4 byte length qualifier followed by that many bytes of field data. (Which we already in the case of timestampor tsrange)

So essentially we skip 25 bytes to get to the first column

The tsrange

So it's in the format specified by range_send You can see that explained a bit below in the comments above range_recv

  Binary representation: The first byte is the flags, then the lower bound (if present), then the upper bound (if present).  Each bound is represented by a 4-byte length header and the binary representation of that bound (as returned by a call to the send function for the subtype).

The timestamp subtype

In your case, that subtype is timestamp and the send is  timestamp_send.

You can see a timestamp is stored as 8 bytes, and that's just sent with a simple pq_sendint64 (a 64 bit/8 byte int). You'll have to read how timestamp_recv works to see how you should handle a binary representation of a timestamp. Hint: it gets into a struct for in-memory representation in timestamp2tm

/* timestamp2tm()
 * Convert timestamp data type to POSIX time structure.
 * Note that year is _not_ 1900-based, but is an explicit full value.
 * Also, month is one-based, _not_ zero-based.
 * Returns:
 *   0 on success
 *  -1 on out of range

I'm not going to more-entertain this here, but maybe next go around.

Playing around

We try first DEADBEEFin it which an isolation to track the marker 8 byte marker.

psql -d test -c 'COPY ( SELECT E'\''DEADBEEF'\'' ) TO STDOUT WITH ( FORMAT BINARY );' |
  od --skip-bytes=25 --endian big --read-bytes=8 -c

Now we swap that out..

psql -d test -c 'COPY ( SELECT $$2010-01-01 14:30$$::timestamp without time zone ) TO STDOUT WITH ( FORMAT BINARY );' |
  od --skip-bytes=25 --endian big --read-bytes=8 --format=d8 -x

Result: paren-comments added.

0000031      315671400000000  (timestamp in int8)
         0001 1f19 f9a9 aa00  (hex representation)
0000041

And that's your number for the first time zone. For tsrange as per the above section we have

one byte for on the interval
upper: 4 bytes (header) + 8 bytes (timestamp)
lower: 4 bytes (header) + 8 bytes (timestamp)

So to access the first internal timestamp, we skip an addition 5 bytes, on top of already 25 bytes of skipage for 30 bytes total.

psql -d test -c 'COPY ( SELECT $$[2010-01-01 14:30, 2010-01-01 15:30)$$::tsrange ) TO STDOUT WITH ( FORMAT BINARY );' |
  od --skip-bytes=30 --endian big --read-bytes=8 --format=d8 -x

This gives us the same result as above..

0000036      315671400000000
         0001 1f19 f9a9 aa00
0000046

Simply change --skip-bytes to 42 to skip over that 8 byte time stamp, and the next 4 bytes of header for the lower and you'll get another time stamp.


Benefits of using backtick (`) in MySQL queries? In MySQL we can create queries with or without the backtick (`) symbol. Example:

SELECT * FROM TEST;
SELECT * FROM `TEST`;

Both works fine in mysql-console.

Is there any technical difference between them?

Is there any benefit using (`) over over simple queries?

They are called quoted identifiers and they tell the parser to handle the text between them as a literal string. They are useful for when you have a column or table that contains a keyword or space. For instance the following would not work:

CREATE TABLE my table (id INT);

But the following would:

CREATE TABLE `my table` (id INT);

Also, the following would get an error, because COUNT is a reserved keyword:

SELECT count FROM some_table

But the following would be parsed correctly:

SELECT `count` FROM some_table

I hope this helps you.


What is lock escalation? I was asked this question at an interview and had no answer. Can anyone here explain?

DB locks can exist on rows, pages or whole tables or indexes.  When a transaction is in progress, the locks held by the transaction take up resources.  Lock escalation is where the system consolidates multiple locks into a higher level one (for example consolidating multiple row locks to a page or multiple pages to a whole table) typically to recover resources taken up by large numbers of fine-grained locks.

It will do this automatically, although you can set flags on the tables (see ALTER TABLE in the books on line) to control the policy for lock escalation on that particular table.  In particular, premature or overly eager lock escalation used to be a problem on older versions of Sybase and SQL Server when you had two processes writing separate rows into the same page concurrently.  If you go back far enough (IIRC SQL Server 6.5) SQL Server didn't actually have row locking but could only lock tables or pages.  Where this happened, you could get contention between inserts of records in the same page; often you would put a clustered index on the table so new inserts went to different pages.


Oracle 10 to 11: charset issue I was redirected from SE's root site to this one.

I'm not a DBA, so I shouldn't be doing this, but I have to, so I apologize beforehand to all of you DBAs that will read this post:

I'm migrating an Oracle database from 10.2.0.4.0 to 11.2.0.1.0 from win 2003 to win 2008 both 64-bit.

I planned, for starters, to export/import and see what happened, and I found that while most of it went well, I had an issue with different charsets: On 10 I have WE8MSWIN1252 and on the new 11 it's using AL32UTF8.

I get "value is too long" for a few records on a few tables. Everything else seems to be in reasonably good shape.

I read somewhere that the length issue is related to a few mono-byte characters getting translated to multi-byte hence I need to increase the size of my columns with problems.

http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch11charsetmig.htm#g1011430

I'll be doing just that:
1) Letting the import/export service do the charset translation.
2) I'll run an import for the metadata only,
3) then alter the tables with the issues,
4) and afterwards import the data.

I'll be using the EM console to do that because, well, my PL/SQL skills suck. :)

Anything extremely wrong with that approach? Any tips/advice you might think of?

Thanks in advance.

I had the same issue with a migration from 9i to 11g.  I decided that I did not want to take a chance that Oracle's character set conversion would do something I did not expect.  Managers don't care about character sets, they just want to know that the data was migrated without any problems. I kept the WE8MSWIN1252 character set and there were no problems.

However, if you had to have a UTF8 character set, and did not want to take any chances this is how I would do it.

Requirements:  a virtual machine in a test environment, to make the install and testing painless

start on the original 10g database in a test environment.
start a new instance of 11g on a virtual machine in a test environment with the old character set
import your data using a data pump or the old imp/exp.
test the web application to see what happens
if successful take a snapshot of the machine so you can revert to this
establish what columns need to be larger. Usually these are LONG or possibly large VARCHAR2 fields
increase the size of the columns so conversion is not required
if you are able set up another Oracle 11 with the new character set and use the data pump to bring the data over.  If this is not feasible then do an export of your data and bring the virtual machine back to before the Oracle install.  Then install Oracle 11 g with the new character set and import the data. Ideally no conversion will occur if you have identified all the tables.
test the web application

Why go to all this extra work?  This solution breaks your project into two issues, the upgrade and the character set. Upgrading databases is not to be considered lightly. Many times it is not the upgrade that is the problem but peripheral problems like interactions with applications or other databases.  Custom PL/SQL code should have no problems but you never know.

If there is a problem people will be looking to you to explain how you were diligent and careful not how you saved a day or two.


Allow Null Columns vs separate 1-to-many tables Perhaps similar questions have been asked, but this is re having null columns.

I am too inexperienced to determine what would be the sounder way to approach the design of this database. Is it better to have 5 or 6 additional columns on the one table that could reference IDs of other tables, or have 5 or 6 extra tables that provide a link if there is a need for them.

My inclination is that fewer null entries are better, but then joining the tables when accessing the database could have a higher cost?

Allow null is a better approach. It gives us significant performance benefits with significant effort shortage to accomplish the task.
A separate table will cost more JOIN. To query such a data is/can be certainly slower, than to query single table


Learning DB2 without a local install I will be starting a new job in about 5 weeks, and want to spend some private time really getting to grips with as many of DB2s features as possible, as it is the DB used in the new company.

Am I required to do a local home install of the DB so that I can play around with it and try some things out, or is there somewhere online that I can connect to a public sandbox DB to practice/test/play around with it?

If an install is required, I'll follow this link: https://stackoverflow.com/questions/3289433/where-to-start-learning-db2-programming

Thanks,
Stephen

I don't think you can avoid a local install. I'm not aware of any online sites (e.g. SQLFiddle does not offer DB2).

In addition to the book you linked to, I can highly recommend the "DB2 SQL Cookbook" as well: http://mysite.verizon.net/Graeme_Birchall/id1.html

And don't forget the official manual: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp


What could cause the wrong ID to be inserted? I have a SQL Server 2008 server (build 10.0.5500).  Earlier this week I ran this on a table that already had data in it:

delete from dbo.table
go
dbcc checkident('dbo.table',reseed,0)

When the user went to create a new record later on, somehow an ID of 0 was inserted into the ID column, instead of the 1 SQL Server usually puts in if identity(1,1) is configured for the ID.

This caused some weird issues, but clearing the data and running the reseed resulted in a 1 being inserted, as expected.  I can't duplicate the issue.

For reference, here's the general format for our save sp's:

alter procedure dbo._TableSave
    @pk_id int,
    @field varchar(50)
as
    if (@pk_id is null)
    begin
        set nocount on;

        insert into dbo.Table
        (
            Field
        )
        values
        (
            @field
        );
        select scope_identity();
    end
    else
    begin
        update dbo.Table
        set Field=@field
        where PK_ID=@pk_id

        select @pk_id
    end

Does anyone know what could cause SQL Server to insert a 0 in the ID when it should have been a 1?

We are not inserting data into identity columns (with identity_insert on) anywhere in the application.

Doing dbcc checkident('dbo.table',reseed,0) will cause the next entry in a newly created / truncated table to have 0 as the identity.

        CREATE TABLE TestIdent
        (
            ID INT NOT NULL CONSTRAINT PK_TestIdent PRIMARY KEY CLUSTERED IDENTITY(1,1)
            , SomeText nvarchar(255)
        );

        dbcc checkident('dbo.TestIdent',reseed,0)

        INSERT INTO TestIdent VALUES ('Test');

        SELECT * FROM dbo.TestIdent;

This causes the next identity entered to be 10:

        TRUNCATE TABLE dbo.TestIdent;

        DBCC CHECKIDENT('dbo.TestIdent',reseed,10)

        INSERT INTO TestIdent VALUES ('Test');

        SELECT * FROM dbo.TestIdent;


Get NULL statistics for a column in MS SQL Server In MS SQL Server 2005, how can I extract statistical information about NULL fraction (or NULL count) for a given column?

Same information is available in Oracle and PostgreSQL, and I heard that MS SQL also has statistics, but I don't know where to find it. Google didn't help much.

I hope I will hear something like

SELECT null_frac FROM pg_stats WHERE tablename='mytab' and attname='mycol';
/* PostgreSQL, stats gathered by ANALYZE */

or

SELECT nullcnt FROM custom_stats_table WHERE tabname='MYTAB' and colname='MYCOL';
/* Oracle, stats gathered by dbms_stats.get_column_stats */

I'm not asking how to compute these values myself - I know how to do it.
I need these stats for several thousands of columns, and computing this would take very long on the database that I'm working on. I need approximations, and they should be somewhere in the server - they are needed anyway for cost-based optimizer.

Nothing so easy. You would need to use DBCC SHOW_STATISTICS and look at the histogram, taking into account that the statistics might be filtered or multi column.
Probably easier to just let SQL Server do it and generate an estimated execution plan for the statement select * from foo where bar is null SQL Server will then use appropriate statistics if they exist or create them if they don't (assuming that the auto create statistics option is enabled).
You can then look at the estimated number of rows going into the SELECT iterator (This shows up as StatementEstRows in the XML for purposes of parsing programatically)
Example code
/*Create temporary stored procedure with query of interest*/
CREATE PROC dbo.#plan_test
AS
SELECT *
FROM master..spt_values
WHERE low IS NULL

GO

/*Generate an execution plan*/
SET FMTONLY ON
EXEC dbo.#plan_test
SET FMTONLY OFF;

/*Retrieve the plan and parse the estimated rows*/
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
SELECT  query_plan.value('(//sql:StmtSimple[1]/@StatementEstRows)[1]', 'float') AS EstimatedNumberOfRows
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE sys.dm_exec_sql_text.objectid=OBJECT_ID('tempdb.dbo.#plan_test')
     AND sys.dm_exec_sql_text.dbid=2

DROP PROC dbo.#plan_test


Implementing versioning system with MySQL I know this has been asked here and here, but I have the same idea with a different possible implementation and I need some help.

Initially I had my blogstories table with this structure:

| Column    | Type        | Description                                    |
|-----------|-------------|------------------------------------------------|
| uid       | varchar(15) | 15 characters unique generated id              |
| title     | varchar(60) | story title                                    |
| content   | longtext    | story content                                  |
| author    | varchar(10) | id of the user that originally wrote the story |
| timestamp | int         | integer generated with microtime()             |

After I decided I wanted to implement some versioning system for every story on the blog, the first thing that came to my mind was creating a different table to hold edits; after that, I thought I could modify the existing table to hold versions instead of edits. This is the structure that came to my mind:

| Column        | Type          | Description                                       |
|------------   |-------------  |------------------------------------------------   |
| story_id      | varchar(15)   | 15 characters unique generated id                 |
| version_id    | varchar(5)    | 5 characters unique generated id                  |
| editor_id     | varchar(10)   | id of the user that commited                      |
| author_id     | varchar(10)   | id of the user that originally wrote the story    |
| timestamp     | int           | integer generated with microtime()                |
| title         | varchar(60)   | current story title                               |
| content       | longtext      | current story text                                |
| coverimg      | varchar(20)   | cover image name                                  |

The reasons why I came here:

The uid field of the initial table was UNIQUE in the table. Now, the story_id is not unique anymore. How should I deal with that? (I thought I could address story_id = x and then find the latest version, but that seems very resource consuming, so please give your advice)
author_id field value is repeating in each every row of the table. Where and how should I keep it?

Edit

The unique codes generation process is in the CreateUniqueCode function:

trait UIDFactory {
  public function CryptoRand(int $min, int $max): int {
    $range = $max - $min;
    if ($range < 1) return $min;
    $log = ceil(log($range, 2));
    $bytes = (int) ($log / 8) + 1;
    $bits = (int) $log + 1;
    $filter = (int) (1 << $bits) - 1;
    do {
        $rnd = hexdec(bin2hex(openssl_random_pseudo_bytes($bytes)));
        $rnd = $rnd & $filter;
    } while ($rnd >= $range);
    return $min + $rnd;
  }
  public function CreateUID(int $length): string {
    $token = "";
    $codeAlphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $codeAlphabet.= "abcdefghijklmnopqrstuvwxyz";
    $codeAlphabet.= "0123456789";
    $max = strlen($codeAlphabet) - 1;
    for ($i=0; $i < $length; $i++) {
        $token .= $codeAlphabet[$this->CryptoRand(0, $max)];
    }
    return $token;
  }
}

The code is written in Hack, and was originally written in PHP by @Scott in his answer.

The fields author_id and editor_id can be different, because there are users with enough permissions to edit anyone's stories.

Analyzing the scenario —which presents characteristics associated with the subject known as temporal databases— from a conceptual perspective, one can determine that: (a) a “present” Blog Story Version and (b) a “past” Blog Story Version, although very resembling, are entities of different types.

In addition to that, when working at the logical level of abstraction, facts (represented by rows) of distinct kinds must be retained in distinct tables. In the case under consideration, even when quite similar, (i) facts about “present” Versions are different from (ii) facts about “past” Versions.

Therefore I recommend managing the situation by means of two tables:

one dedicated exclusively for the “current” or “present” Versions of the Blog Stories, and
one that is separate, but also linked with the other, for all the “previous” or “past” Versions;

each with (1) a slightly distinct number of columns and (2) a different group of constraints.

Back to the conceptual layer, I consider that —in your business environment— Author and Editor are notions that can be delineated as Roles that can be played by a User, and these important aspects depend on data derivation (via logical-level manipulation operations) and interpretation (carried out by the Blog Stories readers and writers, at the external level of the computerized information system, with the assistance of one or more application programs).

I will detail all these factors and other relevant points as follows.

Business rules

According to my understanding of your requirements, the following business rules formulations (put together in terms of the relevant entity types and their kinds of interrelationships) are specially helpful in establishing the corresponding conceptual schema:

A User writes zero-one-or-many BlogStories
A BlogStory holds zero-one-or-many BlogStoryVersions
A User wrote zero-one-or-many BlogStoryVersions

Expository IDEF1X diagram

Consequently, in order to expound my suggestion by virtue of a graphical device, I have created a sample IDEF1Xa diagram that is derived from the business rules formulated above and other features that seem pertinent. It is shown in Figure 1:

Why are BlogStory and BlogStoryVersion conceptualized as two different entity types?

Because:

A BlogStoryVersion instance (i.e., a “past” one) always holds a value for an UpdatedDateTime property, while a BlogStory occurrence (i.e., a “present” one) never holds it.
Besides, the entities of those types are uniquely identified by the values of two distinct sets of properties: BlogStoryNumber (in the case of the BlogStory occurrences), and BlogStoryNumber plus CreatedDateTime (in the case of the BlogStoryVersion instances).

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is based on the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on the Entity-Relationship view of data, developed by Dr. P. P. Chen; and also on the Logical Database Design Technique, created by Robert G. Brown.

Illustrative logical SQL-DDL layout

Then, based on the conceptual analysis previously presented, I declared the logical-level design below:

-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also you should make accurate tests to define the most
-- convenient index strategies at the physical level.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE UserProfile (
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATETIME NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    UserName        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        BirthDate,
        GenderCode
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (UserName) -- ALTERNATE KEY.
);

CREATE TABLE BlogStory (
    BlogStoryNumber INT      NOT NULL,
    Title           CHAR(60) NOT NULL,
    Content         TEXT     NOT NULL,
    CoverImageName  CHAR(30) NOT NULL,
    IsActive        BIT(1)   NOT NULL,
    AuthorId        INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT BlogStory_PK              PRIMARY KEY (BlogStoryNumber),
    CONSTRAINT BlogStory_AK              UNIQUE      (Title), -- ALTERNATE KEY.
    CONSTRAINT BlogStoryToUserProfile_FK FOREIGN KEY (AuthorId)
        REFERENCES UserProfile (UserId)
);

CREATE TABLE BlogStoryVersion  (
    BlogStoryNumber INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    Title           CHAR(60) NOT NULL,
    Content         TEXT     NOT NULL,
    CoverImageName  CHAR(30) NOT NULL,
    IsActive        BIT(1)   NOT NULL,
    AuthorId        INT      NOT NULL,
    UpdatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT BlogStoryVersion_PK              PRIMARY KEY (BlogStoryNumber, CreatedDateTime), -- Composite PK.
    CONSTRAINT BlogStoryVersionToBlogStory_FK   FOREIGN KEY (BlogStoryNumber)
        REFERENCES BlogStory (BlogStoryNumber),
    CONSTRAINT BlogStoryVersionToUserProfile_FK FOREIGN KEY (AuthorId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT DatesSuccession_CK               CHECK       (UpdatedDateTime > CreatedDateTime) --Let us hope that MySQL will finally enforce CHECK constraints in a near future version.
);

Tested in this SQL Fiddle that runs on MySQL 5.6.

The BlogStory table

As you can see in the demo design, I have defined the BlogStory PRIMARY KEY (PK for brevity) column with the INT datatype. In this regard, you may like to fix a built-in automatic process that generates and assigns a numeric value for such a column in every row insertion. If you do not mind leaving gaps occasionally in this set of values, then you can employ the AUTO_INCREMENT attribute, commonly used in MySQL environments.

When entering all your individual BlogStory.CreatedDateTime data points, you can utilize the NOW() function, which returns the Date and Time values that are current in the database server at the exact INSERT operation instant. To me, this practice is decidedly more suitable and less prone to errors than the use of external routines.

Provided that, as discussed in (now-removed) comments, you want to avoid the possibility of maintaining BlogStory.Title duplicate values, you have to set up a UNIQUE constraint for this column. Due to the fact that a given Title may be shared by several (or even all of the) “past” BlogStoryVersions, then a UNIQUE constraint should not be established for the BlogStoryVersion.Title column.

I included the BlogStory.IsActive column of type BIT(1) (though a TINYINT may as well be used) in case you need to provide “soft” or “logical” DELETE functionality.

Details about the BlogStoryVersion table

On the other hand, the PK of the BlogStoryVersion table is composed of (a) BlogStoryNumber and (b) a column named CreatedDateTime that, of course, marks the precise instant in which a BlogStory row underwent an INSERT.

BlogStoryVersion.BlogStoryNumber, besides being part of the PK, is also constrained as a FOREIGN KEY (FK) that references BlogStory.BlogStoryNumber, a configuration that enforces referential integrity between the rows of these two tables. In this respect, implementing an automatic generation of a BlogStoryVersion.BlogStoryNumber is not necessary because, being set as a FK, the values INSERTed into this column must be “drawn from” the ones already enclosed in the related BlogStory.BlogStoryNumber counterpart.

The BlogStoryVersion.UpdatedDateTime column should retain, as expected, the point in time when a BlogStory row was modified and, as a consequence, added to the BlogStoryVersion table. Hence, you can use the NOW() function in this situation too.

The Interval comprehended between BlogStoryVersion.CreatedDateTime and BlogStoryVersion.UpdatedDateTime expresses the entire Period during which a BlogStory row was “present” or “current”.

Considerations for a Version column

It can be useful to think of BlogStoryVersion.CreatedDateTime as the column that holds the value that represents a particular “past” Version of a BlogStory. I deem this much more beneficial than a VersionId or VersionCode, since it is user-friendlier in the sense that people tend to be more familiar with time concepts. For instance, the blog authors or readers could refer to a BlogStoryVersion in a fashion similar to the following:

“I want to see the specific Version of the BlogStory identified by Number 1750 that was Created on 26 August 2015 at 9:30”.

The Author and Editor Roles: Data derivation and interpretation

With this approach, you can easily distinguish who holds the “original” AuthorId of a concrete BlogStory SELECTing the “earliest” Version of a certain BlogStoryId FROM the BlogStoryVersion table by virtue of applying the MIN() function to BlogStoryVersion.CreatedDateTime.

In this way, each BlogStoryVersion.AuthorId value contained in all the “later” or “succeeding” Versions rows indicate, naturally, the Author identifier of the respective Version at hand, but one can also say that such a value is, at the same time, denoting the Role played by the involved User as Editor of the “original” Version of  a BlogStory.

Yes, a given AuthorId value may be shared by multiple BlogStoryVersion rows, but this is actually a piece of information that tells something very significant about each Version, so the repetition of said datum is not a problem.

The format of DATETIME columns

As for the DATETIME data type, yes, you are right, “MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format”, but you can confidently enter the pertinent data in this manner, and when you have to perform a query you just have to make use of the built-in DATE and TIME functions in order to, among other things, show the concerning values in the appropriate format for your users. Or you could certainly carry out this kind of data formatting via your application programms(s) code.

Implications of BlogStory UPDATE operations

Every time that a BlogStory row suffers an UPDATE, you must ensure that the corresponding values that were “present” until the modification took place are then INSERTed into the BlogStoryVersion table. Thus, I highly suggest fulfilling these operations within a single ACID TRANSACTION to guarantee that they are treated as an indivisible Unit of Work. You may as well employ TRIGGERS, but they tend to make things untidy, so to speak.

Introducing a VersionId or VersionCode column

If you opt (because of business circumstances or personal preference) to incorporate a BlogStory.VersionId or BlogStory.VersionCode column to distinguish the BlogStoryVersions, you should ponder the following possibilities:

A VersionCode could be required to be UNIQUE in (i) the whole BlogStory table and also in (ii) BlogStoryVersion.

Therefore, you have to implement a carefully tested and totally reliable method in order to generate and assign each Code value.
Maybe, the VersionCode values could be repeated in different BlogStory rows, but never duplicated along with the same BlogStoryNumber. E.g., you could have:

a BlogStoryNumber 3 - Version 83o7c5c and, simultaneously,
a BlogStoryNumber 86 - Version 83o7c5c and
a BlogStoryNumber 958 - Version 83o7c5c.

The later possibility opens another alternative:

Keeping a VersionNumber for the BlogStories, so there could be:

BlogStoryNumber 23 - Versions 1, 2, 3…;
BlogStoryNumber 650 - Versions 1, 2, 3…;
BlogStoryNumber 2254 - Versions 1, 2, 3…;
etc.

Holding “original” and “subsequent” versions in a single table

Although maintaining all the BlogStoryVersions in the same individual base table is possible, I suggest not to do it because you would be mixing two distinct (conceptual) types of facts, which thus has undesirable side-effects on

data constraints and manipulation (at the logical level), along with
the related processing and storage (at the physical tier).

But, on condition that you choose to follow that course of action, you can still take advantage of many of the ideas detailed above, e.g.:

a composite PK consisting of an INT column (BlogStoryNumber) and a DATETIME column (CreatedDateTime);
the usage of server functions in order to optimize the pertinent processes, and
the Author and Editor derivable Roles.

Seeing that, by proceeding with such an approach, a BlogStoryNumber value will be duplicated as soon as “newer” Versions are added, an option that and that you could evaluate (which is very alike to those mentioned in the previous section) is establishing a BlogStory PK composed of the columns BlogStoryNumber and VersionCode, in this manner you would be able to uniquely identify each Version of a BlogStory. And you can try with a combination of BlogStoryNumber and VersionNumber too.

Similar scenario

You may find my answer to this question of help, since I as well propose enabling temporal capabilities in the concerning database to deal with a comparable scenario.


Reconciling dm_os_windows_info windows_sku values, gathering same info from pre SQL Server 2008 R2 instances I'm developing an inventory system for RDBMS deployments. I'm starting with SQL Server, and later will be pulling in info for Oracle & other RDBMS deployments. When you query [dm_os_windows_info] there is a numeric value in the windows_sku column that is of use to me.... if I can reconcile it.

 SELECT [windows_sku] FROM [master].[sys].[dm_os_windows_info]

I can't seem to find a simple reference on reconciling this SKU value to the product. If you look at the dm_os_windows_info reference, it says to see the GetProductInfo function. On that page you find a hex value. Looks like if you convert windows_sku to hex and take the right two values it works. If anyone knows of a simpler convert table that would be great. If not I'll probably have to make one. If I have to manually put the data in a table to reference it, I may as well convert it from hex while I do it instead of doing it on the fly.

Also it looks like this view was added in SQL Server 2008 R2, yet you can see version info in server properties for earlier version instances. Where is this stored?

Note I'm trying not to have to jump out to Powershell if possible. Also trying to avoid a dependency on OLE automation being enabled.

Thanks for any help! :)

You can obtain most of the info from your screen shot from xp_msver. It goes back as far as SQL 2005. The other information might be in sys.configurations: http://msdn.microsoft.com/en-us/library/ms188345.aspx


This took some doing, but I think I got it.

Here is your solution

SET @given_locale_id = 3;
SELECT terms_value,MAX(rosetta_value) rosetta_value
FROM
(
    SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    FROM terms T LEFT JOIN rosetta R
    ON T.terms_id=R.terms_id
    UNION
    SELECT T.value,'',@given_locale_id FROM terms T
) A
WHERE
    locale_id = @given_locale_id
GROUP By terms_value;

I loaded your sample data with this:

DROP DATABASE IF EXISTS timhaserback;
CREATE DATABASE timhaserback;
use timhaserback
CREATE TABLE locale
(
    locale_id INT NOT NULL AUTO_INCREMENT,
    value VARCHAR(128),
    PRIMARY KEY (locale_id)
);
INSERT INTO locale (`value`) VALUES
('English'),('Deutch'),('French');
CREATE TABLE terms
(
    terms_id INT NOT NULL AUTO_INCREMENT,
    value VARCHAR(128),
    PRIMARY KEY (terms_id)
);
INSERT INTO terms (`value`) VALUES
('lname'),('lpass'),('welcome_blurb');
CREATE TABLE rosetta
(
    rosetta_id INT NOT NULL AUTO_INCREMENT,
    locale_id INT NOT NULL,
    terms_id INT NOT NULL,
    value VARCHAR(128),
    PRIMARY KEY (rosetta_id)
);
INSERT INTO rosetta (locale_id,terms_id,value) VALUES
(1,1,'Username:'),
(2,1,'Benutzername:'),
(1,2,'Password:'),
(2,2,'Passwort:'),
(1,3,'Welcome to appland! Please log in below.'),
(2,3,'Welcome to appland Bitte melden Sie sich unten.');
SELECT * FROM locale;
SELECT * FROM terms;
SELECT * FROM rosetta;

Here is the sample data actually loaded:

mysql> DROP DATABASE IF EXISTS timhaserback;
Query OK, 3 rows affected (0.14 sec)

mysql> CREATE DATABASE timhaserback;
Query OK, 1 row affected (0.00 sec)

mysql> use timhaserback
Database changed
mysql> CREATE TABLE locale
    -> (
    ->     locale_id INT NOT NULL AUTO_INCREMENT,
    ->     value VARCHAR(128),
    ->     PRIMARY KEY (locale_id)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO locale (`value`) VALUES
    -> ('English'),('Deutch'),('French');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE terms
    -> (
    ->     terms_id INT NOT NULL AUTO_INCREMENT,
    ->     value VARCHAR(128),
    ->     PRIMARY KEY (terms_id)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO terms (`value`) VALUES
    -> ('lname'),('lpass'),('welcome_blurb');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE rosetta
    -> (
    ->     rosetta_id INT NOT NULL AUTO_INCREMENT,
    ->     locale_id INT NOT NULL,
    ->     terms_id INT NOT NULL,
    ->     value VARCHAR(128),
    ->     PRIMARY KEY (rosetta_id)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO rosetta (locale_id,terms_id,value) VALUES
    -> (1,1,'Username:'),
    -> (2,1,'Benutzername:'),
    -> (1,2,'Password:'),
    -> (2,2,'Passwort:'),
    -> (1,3,'Welcome to appland! Please log in below.'),
    -> (2,3,'Welcome to appland Bitte melden Sie sich unten.');
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM locale;
+-----------+---------+
| locale_id | value   |
+-----------+---------+
|         1 | English |
|         2 | Deutch  |
|         3 | French  |
+-----------+---------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM terms;
+----------+---------------+
| terms_id | value         |
+----------+---------------+
|        1 | lname         |
|        2 | lpass         |
|        3 | welcome_blurb |
+----------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM rosetta;
+------------+-----------+----------+-------------------------------------------------+
| rosetta_id | locale_id | terms_id | value                                           |
+------------+-----------+----------+-------------------------------------------------+
|          1 |         1 |        1 | Username:                                       |
|          2 |         2 |        1 | Benutzername:                                   |
|          3 |         1 |        2 | Password:                                       |
|          4 |         2 |        2 | Passwort:                                       |
|          5 |         1 |        3 | Welcome to appland! Please log in below.        |
|          6 |         2 |        3 | Welcome to appland Bitte melden Sie sich unten. |
+------------+-----------+----------+-------------------------------------------------+
6 rows in set (0.00 sec)

mysql>

Let's try it against locale_id 1

mysql> SET @given_locale_id = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT terms_value,MAX(rosetta_value) rosetta_value
    -> FROM
    -> (
    ->     SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    ->     FROM terms T LEFT JOIN rosetta R
    ->     ON T.terms_id=R.terms_id
    ->     UNION
    ->     SELECT T.value,'',@given_locale_id FROM terms T
    -> ) A
    -> WHERE
    ->     locale_id = @given_locale_id
    -> GROUP By terms_value;
+---------------+------------------------------------------+
| terms_value   | rosetta_value                            |
+---------------+------------------------------------------+
| lname         | Username:                                |
| lpass         | Password:                                |
| welcome_blurb | Welcome to appland! Please log in below. |
+---------------+------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Let's try it against locale_id 2

mysql> SET @given_locale_id = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT terms_value,MAX(rosetta_value) rosetta_value
    -> FROM
    -> (
    ->     SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    ->     FROM terms T LEFT JOIN rosetta R
    ->     ON T.terms_id=R.terms_id
    ->     UNION
    ->     SELECT T.value,'',@given_locale_id FROM terms T
    -> ) A
    -> WHERE
    ->     locale_id = @given_locale_id
    -> GROUP By terms_value;
+---------------+-------------------------------------------------+
| terms_value   | rosetta_value                                   |
+---------------+-------------------------------------------------+
| lname         | Benutzername:                                   |
| lpass         | Passwort:                                       |
| welcome_blurb | Welcome to appland Bitte melden Sie sich unten. |
+---------------+-------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Let's try it against locale_id 3

mysql> SET @given_locale_id = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT terms_value,MAX(rosetta_value) rosetta_value
    -> FROM
    -> (
    ->     SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    ->     FROM terms T LEFT JOIN rosetta R
    ->     ON T.terms_id=R.terms_id
    ->     UNION
    ->     SELECT T.value,'',@given_locale_id FROM terms T
    -> ) A
    -> WHERE
    ->     locale_id = @given_locale_id
    -> GROUP By terms_value;
+---------------+---------------+
| terms_value   | rosetta_value |
+---------------+---------------+
| lname         |               |
| lpass         |               |
| welcome_blurb |               |
+---------------+---------------+
3 rows in set (0.00 sec)

mysql>

I tried it in your SQL Fiddle and all three locale_ids work as advertised.

Give it a Try !!!


Optional parameter in where condition I have the following where condition:

lr."dateEnd" >= to_date(?, 'dd.mm.yyyy') and lr."dateEnd" <= to_date(?, 'dd.mm.yyyy')

All works fine. But the second unnamed parameter is optional. How to check if it's empty or null then don't check the second condition?

Use CASE?

http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

something like:

lr."dateEnd" >= to_date(?, 'dd.mm.yyyy') and lr."dateEnd" <= (case when parameter2 is null then
sysdate
  else
to_date(parameter2, 'dd.mm.yyyy')


How to Find SYS Privilege Users I want to know which users have SYS and SYSDBA privilege before export/import operation.

I searched a lot but I could not find.

To your question:

I want to know which users have SYS and SYSDBA privilege before export/import operation.

SYSDBA is a role, not a user or schema. The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.
As per the Burleson consulting blog, the v$pwfile_users view contains a list of all users who have been granted the SYSDBA or SYSOPER roles.
SQL> desc v$pwfile_users;

Name Null?  Type
----------------------------------------- -------- ----------------------------
USERNAME    VARCHAR2(30)
SYSDBA      VARCHAR2(5)
SYSOPER     VARCHAR2(5)
SYSASM      VARCHAR2(5)

SQL>
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE TRUE FALSE
TOM                            TRUE FALSE FALSE

In 11g there is a new column in v$pwfile_users for the SYSASM privilege:
SYS AS SYSDBA @ orcl11 SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

For further details see this Tom Kyte article.%3A%3AP11_QUESTION_ID:2659418700346202574">Here


Generate random row order in a subquery I know other answers here (and here) say to order by newid(). However if I am selecting top 1 in a subquery - so as to generate a random selection per row in the outer query - using newid() yields the same result each time.
That is:
select *,
    (select top 1 [value] from lookupTable where [code] = 'TEST' order by newid())
from myTable

... yields the same lookupTable.value value on each row returned from myTable.
I'm trying to pick a random value from lookupTable. That table has only a few rows. In the real world I want to update myTable set someColumn =  ... a random lookupTable.value value such that each row in myTable is set with a random value and not such that a single random value is generated and assigned to all rows.

One way to achieve this is to ensure the subquery is correlated i.e. it depends in some way on the outer query. Using an AdventureWorks example:
SELECT
    E.BusinessEntityID,
    R.DepartmentID
FROM HumanResources.Employee AS E
CROSS APPLY
(
    SELECT TOP (1)
        D.DepartmentID
    FROM HumanResources.Department AS D
    ORDER BY
        NEWID(),
        CHECKSUM(E.BusinessEntityID, D.DepartmentID) -- Correlation
) AS R;

db<>fiddle demo
Without the correlation, SQL Server recognises that the inner query logically only needs to be run once.
The example above uses CHECKSUM(E.BusinessEntityID, D.DepartmentID) to ensure the intended correlation remains after query optimization. If we just add E.BusinessEntityID, the optimizer realizes its value is constant per iteration, removes the dependency, and we're back where we started.
The semantics of the example are also slightly different from the query in the question. It does not return NULL if lookupTable is empty.


Oracle systimestamp result differs in db server and client I am getting some strange output while executing the below command on db server and on client machine connected to same db server.

On DB server

SQL> select systimestamp from dual;

systimestamp

25-JUN-15 06.16.13.424135 PM +08:00

On Client Machine

SQL> select systimestamp from dual;

SYSTIMESTAMP

25-JUN-15 02.16.57.936662 AM -08:00

Below output for Reference

SQL>  select dbtimezone, sessiontimezone from dual;

DBTIMEZONE                     SESSIONTIMEZONE

+00:00                            +08:00

It is normal, because your NLS settings differ on the server and on the client.

The NLS conversion always happens on the client side no matter what your server settings are.  Compare the output of these:

select * from nls_database_parameters;
select * from nls_session_parameters;


Excluding rows from a query on a combination of mulitple conditions I want to drop rows from a query result based on the combination of two columns e.g.

My table after join is

-- JOIN of person and flag table based on person_id

Person.Person_id  Flag.Query_id  Flag.Person_id  Flag.query_flag
1                 10             1               IGNORED
1                 20             1               FAVORITE
2                 20             2               FAVORITE
2                 10             2               IGNORED
3                 10             3               FAVORITE

Now, I want remove/drop all persons who have "ignored" the query 10.

So remove rows where query_id = 10 and flag = 'IGNORED'.

The resulting table should be:

Person.Person_id  Flag.Query_id  Flag.Person_id  Flag.query_flag
1                 20             1               FAVORITE
2                 20             2               FAVORITE
3                 10             3               FAVORITE

So I want to drop rows from the result based on the combination of two columns.

I know that I can select a row based on the combination of two columns as:

SELECT *
FROM  -- query with joins here
WHERE Flag.Query_id = 10 and Flag.query_flag = 'IGNORED'

I tried doing something like:

 SELECT *
 FROM  -- query with joins here
 WHERE NOT (Flag.Query_id = 10 AND Flag.query_flag = 'IGNORED')

I see here that NOT is applied to the columns individually and not to a combination of conditions.

How can I achieve my result without a subquery?

To be precise, to ...

  remove rows where query_id = 10 and flag = 'IGNORED'

... you need something like this:

SELECT *
FROM  ...  -- you are hiding a more complex query here!
WHERE (Flag.Query_id = 10 and Flag.query_flag = 'IGNORED') IS NOT TRUE;

In a WHERE clause, only TRUE qualifies. Rows with FALSE or NULL are excluded.

What you display:

...
WHERE NOT (Flag.Query_id = 10 and Flag.query_flag = 'IGNORED');

... would also exclude rows with NULL values in Flag.Query_id or Flag.query_flag - not exactly what you asked for. Even if the columns are defined NOT NULL, NULL values can be introduced with an OUTER JOIN or similar in your undisclosed query.

(The sample rows you display are not affected, since there are no NULL values.)


MariaDB instance in cloud hit 95% CPU utilisation for 30 mins I've got MariaDB instance in the cloud which I put live about 2 months ago. I'm new to the DBA business and I got the impression the people at the company who I run it through are learning on the hoof as well.

All is going well except that I saw CPU utilisation spiked on Tuesday to 95% for now apparent reason.

I requested performance stats after a user complained of a long running query. It turns out they were doing regular hourly queries on unindexed fields and the table scans involved suddenly took a lot longer. I am now looking at indexing this column so they can run their queries faster.

I'd like to know whether their queries could be the cause of the CPU utilisation, or whether whatever caused the CPU utilisation had just grabbed all the cache and caused their table scanning query to suddenly take far longer.

What should I be doing to capture information that will show what is going on and why the CPU utilisation spiked, and if it's a concern?

Here's the 5 days chart of CPU utilisation:

and the 1 day chart

and the only other chart that correlated with it:

and

The scales on the chart are probably the key, right?

The other charts provided (database connections, write IOPS, disk queue depth) didn't show any correlation.

I'm also puzzled why the normal pattern has totally changed. The usage is around a million queries per day, and has been for 2 months now.

(Not yet an "Answer", but debugging help:)

High CPU almost always means a SELECT needs optimizing.  To find out which query:

Run SHOW FULL PROCESSLIST; when the naughty query is running (10:00?).  Run it a few times; hopefully, you can spot some query with a large "Time" or perhaps it is being run repeatedly.
Or us the SlowLog to find it.  Here are tips on how to get the info and present it to us here for help.

You show heavy I/O also.  That could be a query that is doing a table scan.  Again, find the query and ask for help.

Meanwhile, refresh graphs, and add one for "slow queries", if it exists.


Risks of changing to ARITHABORT ON I'm working with a vendor with the arrangement that they provide the core application, and I can build my own extensions as long as I don't modify the core application.  It's built in ColdFusion connecting to a SQL Server 2005 database.

Some of the reports I've built depend on views using functions computed from the core tables, and the reports are getting very slow as the tables get larger.  To speed up the reports, I want to use indexed views.  But after creating an indexed view in my test environment, the core application could no longer insert into the core tables (it returned an error message that ARITHABORT is required to be ON when using indexed views).

So it seems that in order to use indexed views, I need to have the core application SET ARITHABORT ON whenever inserting/updating the core tables.  I ran this in my test environment:

ALTER DATABASE MyDatabase SET ARITHABORT ON;

and it seems to work fine.  But my vendor says since the application has thousands of queries, there could be a risk that this setting could break one of these queries, and if we have some future unexpected database issue they would insist I restore the default setting.

Are there actual queries that would be broken by SET ARITHABORT ON?  Is there any situation where it would be better to keep it OFF?

TL;DR For my new indexed views to work I need to set ARITHABORT ON for the whole database, but my vendor warns it will be at my own risk.  Is there actually a risk?

So SET ARITHABORT ON basically says "if a divide by zero error happens or an arithmetic overflow happens abort the query" This is usually desirable behavior and is the default instance wide setting. If this causes issues with your vendor's queries, I would say that they may have been suffering from some coding issues to begin with. I would ask them for more details on why they are concerned here.

Of all of the rules of indexed views, I would call this and many of the set options rules the least controversial.

This would have to be set in the connections that interact with the view. So you would want to work with the vendor and really try and understand their reasoning and try and get them to commit to what they are thinking on the big disagreement here.

That said - Indexed views are a bit of a big deal. They have other rules and they can impact the application and assumptions the vendor's developers had when building and performance testing. You should really have a conversation with them about the business problem you are trying to solve through indexed views and get them involved in the conversation about how to solve the problem.


tsql to find users and database level permissions mapped to a certificate On SQL 2005 and above, how can I find users and database level permissions mapped to a certificate?

Story behing this:
I copied a database from SQL 2005 to SQL 2008. It had a certificate inside the database which got copied over with backup and restore (as far as I can see). I am not seeing any server level credentials for this certificate on both source and destination server and can see this inside the user database using the following code:

select * from sys.certificates

I couldn't find a certificate file on the server corresponding to this certificate. I doubt the following with this:

Did the certificate get copied correctly?
Did the logins get mapped automatically?
Did it loose any of the permissions on the database objects?
How can I find a file corresponding to this certificate?

I just want to make sure that If the logins were granted permissions using a certificate, then I should be able to see and retain the permissions.
The database doesn't have encrypted data.

Thanks in advance..

You can use this table to find users mapped to certificate

select * from sys.database_principals where type = 'C'

If the principals are SQL users, maybe you will have to remap users because when you migrate users and logins from one sql server to an other, SID aren't regenerated and may be different on the new server. So all the link on the users aren't valid.

Hope this help you.


Cluster design: if I expect to insert data into several tables every week, is it a bad idea to cluster them? I have some tables that I join that I think would be perfect for putting in a cluster. But I also expect to insert data into them every week. Not a huge amount of data- something like 5 - 20 records a week. My understanding is that clusters are good for tables that you join and select from, but no so good if you plan to use DML statements on them.
My question is: when Oracle documentation says that clustering isn't efficient to use with DML statements, are they referring to updating and deleting hundreds of records? Or would a small amount of records being inserted also fall under this guideline? My question is essentially, does the scale of DML statements impact the efficiency of clustering? Or is it more of a binary situation. Like, if i was updating one record every day, should I avoid putting my tables in a cluster?

I have yet to see a real world user scenario where the benefit (saving a bit of disk or I/O or block access) of using a cluster instead of just regular tables (or IOTs) with joins is so significant that it worths the hassle of dealing with it.
5-20 records per week: that is nothing. Paper and pencil can do that.
FYI: The data dictionary tables use a few clusters for identifiers. These identifiers never change. They are inserted, deleted, but never updated. In some environments, 5-20 records are inserted/deleted in a matter of seconds or minutes (due to dynamically creating and dropping objects) without causing any problem. So 5-20 records per week will not be a problem. The question is: do you really want to use something that is almost never used, that may not even improve performance noticeably (or even make it worse), but requires extra attention.


Backing up the database without compromising performance I have a data base with a size up to 120 Gb and I need do a backup every night.

The problem is that the system needs stand operative all the 24 hours.

In this moments the backup takes up to 2 hours to complete.

How I can do the backup without compromising server performance?

Thanks,
Andrés

120GB should not take 2 hours

Do you have a separate disk array for backups, with a separate network interface to move the backups off the server?
If you have Enterprise Edition, can you use mirror backups
Can you do weekly full backups and daily differentials?
Is backup compression turned on (Standard in SQL Server 2008 R2+, Enterprise only in SQL Server 2008)

And without more details, it will be hard to be more specific


Restoring database backup file gives access error I am running SQL server 2008 and trying to restore from a backup file.

Note: the backup file and folder and read/write access to "everyone". Running the command as "master"

Running

 RESTORE FILELISTONLY FROM DISK = 'D:\path'

Gives the following error:

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'D:\path'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks,
Bruce

You need to actually specify the backup file in the path.  It's not the file list in an NTFS directory, as it seems may be your understanding. It's the file list of the database files in the backup file.

What you should be doing is:

restore filelistonly
from disk = 'D:\path\backupfile.bak';