MS Access query-by-form opening query, but not running it with new form data I have created a query-by-form in MS Access. The form has a text field for each field in my table. I'm only using one starting out to test. The query criteria for that field is

Like "*" & [Forms]![MasterSearchForm]![KeyWords] & "*" Or [Forms]![MasterSearchForm]![KeyWords] is Null

The form has a button with the onclick event to open the query.

If the query is closed when I click the button, it opens the query with the appropriate criteria. However, if I change the value in the form and then click the button again, only the old results appear. If I execute the query manually, then it does suck the data from the form. Why do I need to close the query each time? How do I make the button run the query, rather than just focusing the query tab?

Private Sub Run_Click()
On Error GoTo Run_Click_Err

    DoCmd.OpenQuery "QueryFromMasterSearch", acViewNormal, acReadOnly

    Run_Click_Exit:
    Exit Sub

    Run_Click_Err:
    MsgBox Error$
    Resume Run_Click_Exit

End Sub

You need to add:

On Error Resume Next
DoCmd.Close AcQuery, "QueryFromMasterSearch"
On Error GoTo Run_Click_Err

just before you open the query.

So, your code becomes:

Private Sub Run_Click()
On Error GoTo Run_Click_Err

    On Error Resume Next
    DoCmd.Close AcQuery, "QueryFromMasterSearch"
    On Error GoTo Run_Click_Err
    DoCmd.OpenQuery "QueryFromMasterSearch", acViewNormal, acReadOnly

    Run_Click_Exit:
    Exit Sub

    Run_Click_Err:
    MsgBox Error$
    Resume Run_Click_Exit

End Sub

The On Error Resume Next bit tells Access to not worry if the Query Window is not actually open.  If you left that out, and didn't have the Query Window open the code would spit out an error.


How to put MAX() inside AVG() in SQL So I am having SQL error: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." I know you will ask me to just put it on a variable but this piece of code is inside a view. SQL does not allow us to put variable inside a view.
SELECT top 1000
              ProductID,
              (
                NULLIF(
                  AVG(
                    CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < MAX(QtyShip_RAW_adjusted) THEN QtyShip_RAW_adjusted END
                  )/COUNT(*),
                  0
                ) + SUM(
                  CASE WHEN QtyShip_RAW_adjusted >= 0 THEN QtyShip_RAW_adjusted END
                ) - (
                  SELECT
                    MAX(QtyShip_RAW_adjusted)
                  FROM
                    QtyShip_RAW_adjusted_test
                ) + (
                  SELECT
                    TOP 1 QtyShip_RAW_adjusted
                  FROM
                    (
                      SELECT
                        TOP 3 QtyShip_RAW_adjusted
                      FROM
                        QtyShip_RAW_adjusted_test
                      ORDER BY
                        QtyShip_RAW_adjusted DESC
                    ) AS Comp
                  ORDER BY
                    QtyShip_RAW_adjusted ASC
                )
              ) / COUNT(
                CASE WHEN QtyShip_RAW_adjusted >= 0 THEN 1 END
              ) as AverageLessPeakWeek
            FROM
                QtyShip_RAW_adjusted_test
            GROUP BY
              ProductID

I already tried putting in a derived table.
FROM
(
                  SELECT
                    MAX(QtyShip_RAW_adjusted) as MaxQty
                  FROM
                    QtyShip_RAW_adjusted_test
) t

But putting it inside derived tables gave me more errors.
                    CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < t.MaxQty THEN QtyShip_RAW_adjusted END

I already made some google search and nothing seems to be working. Thanks Guys!

So I still added the derived table
            (SELECT ProductID, QtyShip_RAW_adjusted,
            (CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < (SELECT
                    MAX(QtyShip_RAW_adjusted)
                  FROM
                    QtyShip_RAW_adjusted_test) THEN QtyShip_RAW_adjusted END) MaxQty
                  FROM
                    QtyShip_RAW_adjusted_test) as Datum

As you can see I included the
                    CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < MAX(QtyShip_RAW_adjusted) THEN QtyShip_RAW_adjusted END
                  )/COUNT(*)

and put the alias in the avg()
                NULLIF(
                  AVG(MaxQty)/COUNT(*),
                  0
                )

Doesn't look good but its working


How to transfer database from SQL Server to DB2? I have a database file on disk called project.mdf. I want to import this into IBM DB2. How do I import this database?

Migrating between platforms is not always an easy task. Besides the changes in schema and SQL code, the data itself may not be an exact fit between platforms. For example, a TIMESTAMP in SQL Server is not the same as a TIMESTAMP in Oracle.

Microsoft offers the SQL Server Migration Assistant, but that is for getting data into SQL Server, not for exporting to other platforms.

The only company I know that offers tools and knowledge for migrating from SQL Server to DB2 is Ispirer. I've never used their tools however, but you may want to start with them.

HTH


Something wrong in pg_hba.conf this my configuration:

#postgres.conf:

listen_addresses = 'localhost'

#pg_hba.conf:

local all postgres md5
local all appuser trust
local all devuser trust
local all all peer

i'm connecting in local using the C-Library libpq.
My connection string is:

host=localhost user=appuser dbname=mydbname port='5432'

However, unless I have a .pgpass file in the home of my developer user (devuser), (the one who launches the application that is linked with libpq), my executable cannot connect, I receive the error fe_sendauth : no password supplied

As I said, if the .pgpass file is here, then it connects ok.

What I don't understand: I configured local all appuser trust.
Why do I need a .pgpass file all the same?

(It's not a serious problem in itself, because later on for security measure, it will no longer be at trust, but md5, so I'll have the .pgpass file anyway. But I'd like to know)

I can see how one might think listen_addresses = 'localhost' is somehow related to the authentication method local. But it is not. I quote the manual on listen_addresses:

  listen_addresses (string)

  Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications.

And the chapter The pg_hba.conf File in the manual:

  local

  This record matches connection attempts using Unix-domain sockets. Without a record of this type, Unix-domain socket connections are
  disallowed.

Bold emphasis mine.

For connections via localhost (TCP/IP, local loopback) you actually need an entry for the authentication method host in your pg_hba.conf file (not for local).


Converting and Subtracting Two Dates I'm trying to get the numeric difference between two dates.  (Ex:  11/30/2018 - 11/05/2018 = 25)

One of the dates (@EndOfMonth) represent the last day of the prior month and is being converted into a 'yyyymmdd' format

BEGIN
DECLARE @EndOfMonth AS VARCHAR(10)
SET @EndOfMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @maxDwdate))), -1), 112))

The second date that I'm looking to subtract from @EndOfMonth, is a field named "DUEDATE" and it's in a 'mm/dd/yyyy' format.

Assuming that you have a valid reason to convert the date to a string, and that you cannot change that logic: declare yourself a variable to store the intermediate results of the date arithmetic before you convert it to a string, then you can easily get the difference between the two dates:

DECLARE @eom DATE
      = DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @maxDwdate))), -1);
DECLARE @EndOfMonth AS VARCHAR(10)
    SET @EndOfMonth = (SELECT CONVERT(VARCHAR, @eom, 112))
 SELECT DATEDIFF(DAY, @eom, DUTEDATE) AS DAYS
   FROM t; -- replace with actual table name.


How does case-insensitive collation work? Default collation type in SQL Server allows for indexing against case insensitive strings yet the case of the data is persisted.  How does this actually work?  I'm looking for the actual nuts and bolts, bits and bytes, or a good resource that explains it in detail.

create table casetest (fruitnames nvarchar(50) not null);
create unique index IX_fruitnames on casetest(fruitnames);

insert into casetest values ('apples');
insert into casetest values ('Pears');
-- this insert fails
insert into casetest values ('pears');

-- this yields 'Pears' as a result
select * from casetest (forceseek) where fruitnames = 'PEARS'

update casetest set fruitnames = 'pears' where fruitnames = 'pEArs'

-- this yields 'pears' as a result
select * from casetest (forceseek) where fruitnames = 'PEARS'

Questions About SQL Server Collations You Were Too Shy to Ask by Robert Sheldon covers how to use collation.  It does not cover how collation works.  I'm interested in how an index can be efficiently created/queried not caring about case, while simultaneously storing case data.

indexing against case insensitive strings yet the case of the data is persisted. How does this actually work?

This is actually not a SQL Server specific behavior, it's just how these things work in general.

So, the data is the data. If you are speaking about an index specifically, the data needs to be stored as it is else it would require a look-up in the main table each time to get the actual value, and there would be no possibility of a covering index (at least not for string types).

The data, either in the table/clustered index or non-clustered index, does not contain any collation / sorting info. It is simply data. The collation (locale/culture rules and sensitivities) is just meta data attached to the column and used when a sort operation is called (unless overridden by a COLLATE clause), which would include the creation/rebuild of an index. The rules defined by a non-binary collation are used to generate sort-keys, which are binary representations of the string (sort keys are unnecessary in binary collations). These binary representations incorporate all of the locale/culture rules and selected sensitivities. The sort-keys are used to place the records in their proper order, but are not themselves stored in the index or table. They aren't stored (at least I haven't seen these values in the index and was told that they aren't stored) because:

They aren't truly needed for sorting since they would merely be in the same order as the rows in the table or index anyway. But, the physical order of the index is just sorting, not comparison.
While storing them might make comparisons faster, it would also make the index larger as the minimum size for a single character is 5 bytes, and that's just "overhead" (of the sort key structure). Most characters are 2 bytes each, plus 1 byte if there's an accent, plus 1 byte if it's upper-case. For example, "e" is a 7-byte key, "E" and "é" are both 8 bytes, and "É" is a 9-byte key. Hence, not worth storing these in the end.

There are two types of collations: SQL Server and Windows.

SQL Server

SQL Server collations (those with names starting with SQL_) are the older, pre-SQL Server 2000 way of sorting/comparing (even though SQL_Latin1_General_CP1_CI_AS is still the installation default on US English OSes, quite sadly). In this older, simplistic, non-Unicode model, each combination of locale, code page, and the various sensitivities are given a static mapping of each of the characters in that code page. Each character is assigned a value (i.e. sort weight) to denote how it equates with the others. Comparisons in this model appear to do a two-pass operation:

First, it removes all accents (such that " ü " becomes " u "), expands characters like " Æ " into " A " and " E ", then does an initial sort so that words are in a natural order (how you would expect to find them in a dictionary).
Then, it goes character by character to determine equality based on these underlying values per each character. This second part is what mustaccio is describing in his answer.

The only sensitivities that can be adjusted in these collations are: "case" and "accent" ("width", "kana type" and "variation selector" are not available). Also, none of these collations support Supplementary Characters (which makes sense as those are Unicode-specific and these collations only apply to non-Unicode data).

This approach applies only to non-Unicode VARCHAR data. Each unique combination of locale, code page, case-sensitivity, and accent-sensitivity has a specific "sort ID", which you can see in the following example:

SELECT COLLATIONPROPERTY(N'SQL_Latin1_General_CP1_CI_AS', 'SortID'), -- 52
       COLLATIONPROPERTY(N'SQL_Latin1_General_CP1_CS_AS', 'SortID'), -- 51
       COLLATIONPROPERTY(N'Latin1_General_100_CI_AS',     'SortID'); --  0

The only difference between the first two collations is the case-sensitivity. The third collation is a Windows collation and so does not have a static mapping table.

Also, these collations should sort and compare faster than the Windows collations due to being simple lookups for character to sort weight. However, these collations are also far less functional and should generally be avoided if at all possible.

Windows

Windows collations (those with names not starting with SQL_) are the newer (starting in SQL Server 2000) way of sorting/comparing. In this newer, complex, Unicode model, each combination of locale, code page, and the various sensitivities are not given a static mapping. For one thing, there are no code pages in this model. This model assigns a default sort value to each character, and then each locale/culture can re-assign sort values to any number of characters. This allows multiple cultures to use the same characters in different ways. This does have the affect of allowing for multiple languages to be sorted naturally using the same collation if they do not use the same characters (and if one of them does not need to re-assign any values and can simply use the defaults).

The sort values in this model are not single values. They are an array of values that assign relative weights to the base letter, any diacritics (i.e. accents), casing, etc. If the collation is case-sensitive, then the "case" portion of that array is used, otherwise it's ignored (hence, insensitive). If the collation is accent-sensitive, then the "diacritic" portion of the array is used, otherwise it's ignored (hence, insensitive).

Comparisons in this model are a multi-pass operation:

First, the string is normalized so that various ways of representing the same character will equate. For example, " ü " could be a single character / code point (U+00FC). You could also combine a non-accented " u " (U+0075) with a Combining Diaeresis " ̈ " (U+0308) to get: " ü ", which not only looks the same when rendered (unless there is a problem with your font), but is also considered to be the same as the single character version (U+00FC), unless using a binary collation (which compares bytes instead of characters). Normalization breaks the single character into the various pieces, which includes expansions for characters like " Æ " (as noted above for SQL Server collations).
The comparison operation in this model goes character by character per each sensitivity. Sort keys for the strings are determined by applying the appropriate elements of each characters collation array of values based on which sensitivities are "sensitive". The sort key values are arranged by all of the primary sensitivities of each character (the base character), followed by all of the secondary sensitivities (diacritic weight), followed by the case weight of each character, and so on.
Sorting is performed based on the calculated sort keys. With each sensitivity grouped together, you can get a different sort order than you would with an equivalent SQL Server collation when comparing strings of multiple characters, and accents are involved, and the collation is accent-sensitive (and even more so if the collation is also case-sensitive).

For more details on this sorting, I will eventually publish a post that shows the sort key values, how they are calculated, the differences between SQL Server and Windows collations, etc. But for now, please see my answer to: Accent Sensitive Sort (please note that the other answer to that question is a good explanation of the official Unicode algorithm, but SQL Server instead uses a custom, though similar, algorithm, and even a custom weight table).

All sensitivities can be adjusted in these collations: "case", "accent", "width", "kana type", and "variation selector" (starting in SQL Server 2017, and only for the Japanese collations). Also, some of these collations (when used with Unicode data) support Supplementary Characters (starting in SQL Server 2012). This approach applies to both NVARCHAR and VARCHAR data (even non-Unicode data). It applies to non-Unicode VARCHAR data by first converting the value to Unicode internally, and then applying the sort/comparison rules.

Please note:

There is no universal default collation for SQL Server. There is an installation default which differs based on the current locale/language setting of the OS at time of installation (which is unfortunately SQL_Latin1_General_CP1_CI_AS for US English systems, so please vote for this suggestion). This can be changed during installation. This instance-level collation then sets the collation for the [model] DB which is the template used when creating new DBs, but the collation can be changed when executing CREATE DATABASE by specifying the COLLATE clause. This database-level collation is used for variable and string literals, as well as the default for new (and altered!) columns when the COLLATE clause is not specified (which is the case for the example code in the question).
For more info on Collations / encodings / Unicode, please visit: Collations Info


SQL Server express - Management studio installation hangs at native images installation I've been trying to install SQL server express edition (2014) with management studio and consistently failing to do so because installation stalls at step 'Install_sql_common_core_Cpu64_Action : Write_NativeImage_64. Generating Native images'. I've tried several time but still it hangs at this point. I even left it overnight but no progress.

I've even tried installing SQL Server 2012 management studio installation file but same issue again.

I tried to search for this kind of issue but no discussion found anywhere on internet. Any clue or pointers will be greatly appreciated. I've been trying since last couple of days but no luck.

Other details:

OS: Windows 10 Pro
Visual Studio 2015 Community edition installed
All windows updates are up to date
Downloaded management studio from MS site and File version is 12.0.2000.8

If any other details needed please let me know.

See below image:

Disabling AVG anti-virus software before starting the install setup allowed me to avoid this problem.


PostgreSQL Index Only Scan can NOT return ctid I have this table in PostgreSQL 11:

CREATE TABLE A (id bigint PRIMARY KEY, text text)

Now I want to fetch the ctid of rows meeting some condition like id = 123.

However, even using pg_hint_plan to hint PostgreSQL to use Index Only Scan, it will always give me Index Scan:

/*+ IndexOnlyScan(a) */ explain analyze select ctid from a where id = 823977776533426178;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Index Scan using a_pkey on a  (cost=0.14..8.16 rows=1 width=6) (actual time=0.038..0.039 rows=1 loops=1)
   Index Cond: (id = '823977776533426178'::bigint)
 Planning Time: 0.122 ms
 Execution Time: 0.095 ms
(4 rows)

My understanding is that ctid is the physical row id (block_no: record_offset) of each row and it must be included in any btree index, since it needs this information to fetch the row from heap file.

Then why can't an index-only scan return the ctid directly?
Is it just that PostgreSQL does not implement it that way?

Your understanding is almost but not quite correct. True, every btree index tuple needs a ctid (or some form of block number and tuple index) to point to the heap tuple (table row). But (at least) since the introduction of "heap-only tuples" with Postgres 8.3, there may be a HOT chain to follow to arrive at the current live tuple in the snapshot - with a different ctid than the one stored in the index.

Since it is also not possible to use the ctid (or any system column) as index expression, it is currently (and for the forseeable future) completely impossible to get a ctid from an index-only scan.

Related:

Redundant data in update statements
How do I decompose ctid into page and row numbers?

Quoting the Postgres Wiki on Index-only_scans / Interaction_with_HOT:

  With HOT, it became possible for an index scan to traverse a so-called
  HOT chain; it could get from the physical index tuple (which would
  probably have been created by an original INSERT, and related to an
  earlier version of the logical tuple), to the corresponding physical
  heap tuple. The heap tuple would itself contain a pointer to the next
  version of the tuple (that is, the tuple ctid), which might, in turn,
  have a pointer of its own. The index scan eventually arrives at tuple
  that is current according to the query's snapshot.


Wait types with queries I have a query to find the wait types in which my system is suffering from.

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
       100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',

        -- Maybe uncomment these four if you have mirroring issues
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',

        N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',

        -- Maybe uncomment these six if you have AG issues
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',

        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
        N'ONDEMAND_TASK_QUEUE',
        N'PREEMPTIVE_XE_GETTARGETSTATE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
        N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_RECOVERY',
        N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
    )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
    CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO

Query is taken from Paul Randal's blog Wait statistics, or please tell me where it hurts

Now is there a way I can correlate the wait types with my actual queries. After some research I found we can configure extended events to capture the query with wait types.

Is there any other alternative through any DMV's can we get wait types along with queries.

You could either use Adam Mechanics sp_whoisactive or the below script and periodically insert the results into a table for later analysis.

Brent Ozar has a good post on how to do this is you decide you use sp_whoisactive

    SELECT
    [owt].[session_id],
    [owt].[exec_context_id],
    [owt].[wait_duration_ms],
    [owt].[wait_type],
    [owt].[blocking_session_id],
    [owt].[resource_description],
    CASE [owt].[wait_type]
        WHEN N'CXPACKET' THEN
            RIGHT ([owt].[resource_description],
            CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)
        ELSE NULL
    END AS [Node ID],
    [es].[program_name],
    [est].text,
    [er].[database_id],
    [eqp].[query_plan],
    [er].[cpu_time]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions [es] ON
    [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON
    [es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
    [es].[is_user_process] = 1
ORDER BY
    [owt].[session_id],
    [owt].[exec_context_id];
GO

For you info dm_os_waiting_tasks are what is currently happening on your sql server, dm_os_wait_stats are what has happened in the past/or since you restarted sql or it's services.


Generate CREATE DATABASE FOR ATTACH for all existing user databases Does anyone have a TSQL script that can automatically generate the code necessary to attach ALL existing user databases using the CREATE DATABASE FOR ATTACH syntax?

example:

CREATE DATABASE [mydatabase] ON
(FILENAME=N'E:\MSSQL\Data\mydatabase.mdf'),
(FILENAME=N'D:\MSSQL.1\MSSQL\Data\mydatabase_log.ldf'),
(FILENAME=N'E:\MSSQL\Data\mydatabase_ndf.ndf')
 FOR ATTACH

I have found many examples using sp_attach_db, but none with the CREATE DATABASE FOR ATTACH syntax.

Thanks,

Craig

This one does the job for me.  You can tweak the first CTE to include or exclude specific database and it will then generate the appropriate commands.

;WITH CTE_Databases AS
    (
    SELECT D.name AS DatabaseName
        , D.database_id
    FROM sys.databases AS D
    WHERE D.name <> 'tempdb'
        AND NOT(D.name IN ('master', 'msdb', 'model'))
    )
, CTE_FirstAndLast AS
    (
    SELECT 1 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'IF NOT EXISTS (SELECT TOP 1 1 FROM sys.databases WHERE name = ' + QUOTENAME(D.DatabaseName, '''') + ')' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 2 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'BEGIN' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 3 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , ' CREATE DATABASE ' + QUOTENAME(D.DatabaseName) + ' ON ' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 20 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , '     FOR ATTACH;' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 90 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'END' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 91 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'GO' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 92 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , '' AS CommandText
    FROM CTE_Databases AS D
    )
, CTE_Files AS
    (
    SELECT 10 AS CommandStep
        , ROW_NUMBER() OVER (PARTITION BY F.database_id ORDER BY F.file_id) AS SubStep
        , F.database_id AS DatabaseID
        , D.DatabaseName
        , CASE WHEN ROW_NUMBER() OVER (PARTITION BY F.database_id ORDER BY F.file_id) > 1 THEN '        , ' ELSE '      ' END + '(FILENAME = N' + QUOTENAME(F.physical_name, '''') + ')' AS CommandText
    FROM sys.master_files AS F
        INNER JOIN CTE_Databases AS D ON d.database_id = F.database_id
    )
, CTE_AllTogether AS
    (
    SELECT CommandStep, SubStep, DatabaseID, DatabaseName, CommandText
    FROM CTE_FirstAndLast
    UNION ALL
    SELECT CommandStep, SubStep, DatabaseID, DatabaseName, CommandText
    FROM CTE_Files
    )
SELECT * FROM CTE_AllTogether
ORDER BY DatabaseID, DatabaseName, CommandStep, SubStep;


Go for the normalized structure. In your case, that's Alt 1.

Have your tables defined more or less like these:

CREATE TABLE visits
(
    visit_id INTEGER AUTO_INCREMENT PRIMARY KEY,
    visit_datetime DATETIME NOT NULL DEFAULT now(),
    ip VARCHAR(45),     -- This can probably be encoded more efficiently, it is actually a 32bit integer for IPv4, and 128 for IPv6
    cookie_id INTEGER   -- Do we have a cookie for this visitor? If yes, keep it!
    -- Whatever else you need
) ;

CREATE TABLE visits_clicks
(
    visit_id INTEGER NOT NULL REFERENCES visits(visit_id),
    click_datetime DATETIME NOT NULL DEFAULT now(),
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,

    PRIMARY KEY (visit_id, click_datetime)
) ;

You'll end up having data like these:

INSERT INTO visits (ip, visit_datetime)
VALUES
   ('127.0.0.1',   now() - interval 90 second),
   ('192.168.1.1', now() - interval 38 second),
   ('192.168.1.2', now() - interval 37 second),
   ('192.168.1.1', now() - interval 26 second) ;

INSERT INTO visits_clicks
   (visit_id, click_datetime, x, y)
VALUES
   (1, now() - interval 88 second, 1010,  970),
   (1, now() - interval 87 second, 1201,  894),
   (1, now() - interval 86 second, 1123, 1098),
   (1, now() - interval 85 second, 1241, 1020),
   (1, now() - interval 84 second, 1234, 1034),
   (2, now() - interval 36 second, 1000, 1234)
   ;

With this structure, you will be able to perform queries like the following one, which might be very difficult with an unnormalized structure:

-- Find average number of clicks per visit, count, min and max
SELECT
   count(visit_id)  AS visit_count,
   avg(click_count) AS average_clicks,
   min(click_count) AS min_click_count,
   max(click_count) AS max_click_count
FROM
    (SELECT
        visit_id, count(*) AS click_count
    FROM
        visits_clicks
    GROUP BY
        visit_id
    ) AS q

visit_count | average_clicks | min_click_count | max_click_count
----------: | -------------: | --------------: | --------------:
          2 |         3.0000 |               1 |               5

You will find that, except for very specific questions, this structure will let you query virtually anything you can think of, in an easier fashion than with the unnormalized design. And you can do so within the database, without having to move information in and out of it, which makes the process much faster.

When in doubt: normalize. You'll be able later to aggregate or denormalize if you need to. Some very specific scenarios work better with denormalized data. I don't think that's your case

Check the setup at dbfiddle here


On modeling pairs with a possibly empty second element EDIT: I've made the post significantly shorter.  I hope it's more digestible now.

Consider the following schema:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , y TEXT NOT NULL
    , FOREIGN KEY (y) REFERENCES Y(y)
);

CREATE VIEW mydata AS

  SELECT X.x, Y.y

    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y);

This schema enforces the following constraints on the rows of the mydata view:

no two rows may have the same y-value but different x-values;
the x-value is never empty;
the y-value is never emtpy;

The following output listing would be consistent with this schema:

Listing 1

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 b | j
 c | k
 d | l
 a | h

Now, suppose that instead of the three constraints above we had the following:

  1'. no two rows may have the same non-empty y-value but different x-values;

  2'. the x-value is never empty;

(Constraint 2' is the same as constraint 2; constraint 1' entails adding the qualifier "non-empty" to constraint 1.)

With these new constraints the following listing, where the missing y-values are NULL, would become valid:

Listing 2

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 b |
 c | k
 d |
 a | h

But such a listing would not be possible with the schema shown above.  Even if the NOT NULL constraint were removed from the definition of the _DATA.y column, the closest we would get is

Listing 3

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 c | k
 a | h

...because there's no way to determine the appropriate value of x for those rows where y is NULL.

I'm looking for a normalized schema that would enforce the new set of constraints (and thus allow data like that shown in Listing 2).

My "non-solution"

My "non-solution" to this problem is a hard-to-maintain hack.  I post it here for three reasons:

to clarify the situation described in the question above;
to provide table-initialization code that responders can use to test their proposals if they so wish (it's in fact the code I used to generate Listing 2);
to give an example of the sort of hard-to-maintain hack that I'm trying to avoid.

This "non-solution" consists of defining some distinct values of y that can somehow be recognized as NULL, and redefining mydata to make use of this information.  For example:

INSERT INTO X VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('NULL_b', 'b')
  , ('k', 'c')
  , ('NULL_d', 'd')
;

INSERT INTO _DATA VALUES
    (1, 'h')
  , (2, 'i')
  , (3, 'NULL_b')
  , (4, 'k')
  , (5, 'NULL_d')
  , (6, 'h')
;

CREATE TEMP VIEW mydata AS
  SELECT x,
         CASE WHEN y LIKE 'NULL_%'
              THEN NULL
              ELSE y
         END AS y
    FROM X JOIN     Y USING (x)
           JOIN _DATA USING (y);

Then

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 b |
 c | k
 d |
 a | h
(6 rows)

Ugly as sin, but aesthetic aside, it is also very hard to maintain.

Other options I see:

A) add column x to table _data. A few more changes will be required:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x),
    , UNIQUE (x, y)                    -- required for the modified FK below
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , x TEXT NOT NULL
    , y TEXT
    , FOREIGN KEY (x, y) REFERENCES Y(x, y)    -- modified FK
    , FOREIGN KEY (x) REFERENCES X(x)          -- additional FK
);

The view has to be modified as well:

CREATE VIEW mydata AS
  SELECT X.x, Y.y
    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y)
  UNION ALL
  SELECT X.x, NULL
    FROM X JOIN _DATA USING (x)
  WHERE _DATA.y IS NULL ;

Example:

INSERT INTO X (x) VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y (y, x) VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('k', 'c')
;

INSERT INTO _DATA (data_id, x, y) VALUES
    (1, 'a', 'h')
  , (2, 'b', 'i')
  , (3, 'b', NULL)
  , (4, 'c', 'k')
  , (5, 'd', NULL)
  , (6, 'a', 'h')
;

SELECT x, y FROM mydata;
 x | y
---+---
 a | h
 b | i
 c | k
 a | h
 b |
 d |
(6 rows)

B) separate the data that have no y value associated to another table. No changes at all required to the existing tables, only for the view:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , y TEXT NOT NULL
    , FOREIGN KEY (y) REFERENCES Y(y)
);

CREATE TABLE _DATA_X (
      data_x_id INTEGER PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

The view modified:

CREATE VIEW mydata AS
  SELECT X.x, Y.y
    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y)
  UNION ALL
  SELECT X.x, NULL
    FROM X JOIN _DATA_X USING (x) ;

Example:

INSERT INTO X (x) VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y (y, x) VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('k', 'c')
;

INSERT INTO _DATA (data_id, y) VALUES
    (1, 'h')
  , (2, 'i')
  , (3, 'k')
  , (4, 'h')
;

INSERT INTO _DATA_X (data_x_id, x) VALUES
    (1, 'b')
  , (2, 'd')
;

SELECT x, y FROM mydata;
 x | y
---+---
 a | h
 b | i
 c | k
 a | h
 b |
 d |
(6 rows)


Select data from single table using Self join I have requirement to select a data from single table. The requirement is as follows
Select data from table which has one value but not another.
for e.g
I want to select distinct ID which has value 1 but not value 8 present. For for above e.g the desired o/p should be

ID      Value
123     1
123     8
234     8
456     1
876     5
876     1
765     8
765     5

O/p
ID

456
876

Also my table contains 500K record so query should be able to execute with good performance
Any help regarding it as i am stuck.

Here's how i got it to work:

create table tableone (id int, value int)
insert into tableone values (123,     1);
insert into tableone values (123,     8);
insert into tableone values (234,     8);
insert into tableone values (456,     1);
insert into tableone values (876,     5);
insert into tableone values (876,     1);
insert into tableone values (765,     8);
insert into tableone values (765,     5);
Select a.*
from tableone a
left join tableone X on a.id = x.id and x.value = 8
Where a.value = 1
and X.id is null
drop table tableone


Efficency of stored procedures vs raw queries I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries?  I am specifically interested in SQL Server but would be interested in any and all databases.

It is less so in SQL Server 2008 and higher, but it is still there.  What it comes down to is the execution plan cache and SQL Server being able to auto-parametrize queries which are sent in.  When using stored procedures (that don't have dynamic SQL within them) the queries are already parametrized so SQL Server doesn't need to generate a plan for each query when it's run as the plans are already stored in the plan cache.

And don't forget about the security issues (dynamic SQL, minimum permissions, etc.) that go away when using stored procedures.

When the app is using dynamic SQL against the base tables to select, insert, update and delete the data in the tables the application needs to have rights to all those objects directly.  So if someone uses SQL Injection to get onto the server they will have rights to query, change or delete all the data in those tables.

If you are using stored procedures they only have the rights to execute the stored procedures getting back just the information which the stored procedure would return.  Instead of issuing a quick delete statement and blowing everything away they would need to figure out what procedures can be used to delete data then figure out how to use the procedure to do so.

Given that SQL Injection is the easiest way to break into a database, this is kind of important.


Return more columns than were inserted inside a PostgreSQL CTE I would like to use a modifying CTE to insert some values. The insert statement inserts data from a select statement. I use the returning keyword to return the values inserted (including auto-incrementing columns). However, I also want that CTE to return other columns. How can I do this?

An example follows:

drop table if exists customers;

CREATE TABLE customers (
 customer_id serial PRIMARY KEY,
 name VARCHAR UNIQUE,
 email VARCHAR NOT NULL,
 active bool NOT NULL DEFAULT TRUE
);

INSERT INTO customers (NAME, email)
VALUES
 ('IBM', 'contact@ibm.com'),
 (
 'Microsoft',
 'contact@microsoft.com'
 ),
 (
 'Intel',
 'contact@intel.com'
 );

drop table if exists customers2;

CREATE TABLE customers2 (
 customer_id serial PRIMARY KEY,
 name VARCHAR UNIQUE,
 email VARCHAR NOT NULL
);

with x as (
    INSERT INTO customers2 (NAME, email)
    select name, email from customers
    returning customer_id, name, email, active
    )
select * from x
;

I would like the last statement to return columns customer_id, name, email, active. But I get an error:

Error: ERROR: column "active" does not exist
  Position: 123
SQLState:  42703
ErrorCode: 0
Error occurred in:
with x as (
    INSERT INTO customers2 (NAME, email)
    select name, email from customers
    returning customer_id, name, email, active
    )
select * from x

According Postgres Docs about 6.4. Returning Data From Modified Rows you can't.

Quoted from docs:

  In an INSERT, the data available to RETURNING is the row as it was inserted. This is not so useful in trivial inserts, since it would just repeat the data provided by the client. But it can be very handy when relying on computed default values. For example, when using a serial column to provide unique identifiers, RETURNING can return the ID assigned to a new row.

Unless this data is updated by a trigger:

  If there are triggers (Chapter 39) on the target table, the data available to RETURNING is the row as modified by the triggers. Thus, inspecting columns computed by triggers is another common use-case for RETURNING.

You can check it by using RETURNING *

CREATE TABLE t1 (id int, foo int);
CREATE TABLE t2 (id int, bar int);

INSERT INTO t2 VALUES (1, 2);

INSERT INTO t1 (id, foo)
SELECT id, bar
FROM   t2
WHERE  id = 1
RETURNING *;

id | foo
-: | --:
 1 |   2

db<>fiddle here

You could get it by using a nested CTE:

WITH x AS
(
    SELECT * FROM t2 WHERE id = 1
), y AS
 (
    INSERT INTO t1 (id, foo)
    SELECT id, bar
    FROM   x
    RETURNING *
 )
 SELECT x.*, y.*
 FROM x, y;

id | bar | id | foo
-: | --: | -: | --:
 1 |   2 |  1 |   2

db<>fiddle here


Is it possible to allocate data free for certain table? After deleted a large dataset (~50GB) and optimized tables, some tables have data free (~50GB) and parts of tables have only data free (~KB). As my understanding, the data free are reused and not keeping the ibdata growing.

The situation is: My commonly used tables are growing really fast, but unfortunately the data free are allocated to not commonly used tables. The disk space of ibdata is keep growing but the data free is not reused.

The question is: Can the data free allocated to commonly used tables, so it can reuse the (~50GB) space, since the storage is costly on cloud?

You have not said whether you are using MyISAM or InnoDB.  I will assume InnoDB.

First of all, "Data_free" is a rotten metric.  It shows only one aspect of the free space.  There are about 3 other major contributors to "free" space, for which there are no metrics.

Second, if you are using innodb_file_per_table = OFF, then the only real metric is the size of the ibdata1 table.  "Data_free" has a radically different meaning for file-per-table versus not.  It sounds like you might have set that setting one way for some tables, the other way for others.

Third, tiny table allocate more space 16KB at a time; large tables grow by 4MB (or maybe 8MB?) at a time.  So the space will 'lurch', helping to make things unpredictable.

Give us a sample of the 'evidence'; we can probably explain what it means and sympathize with over how cryptic/deceptive it is.


Warning about implicit convert I have table with 2 name columns:

CREATE TABLE Test
(
  TestID int identity primary key clustered
  , Name_Eng nvarchar(50)
  , Name_Nat nvarchar(50)
)

Now I need a query to get this Name column separated by ,, like this:

    DECLARE @NameColumns NVARCHAR(1024)

            SET @NameColumns = STUFF(
      (SELECT   ',' + 'Test.' + name AS [text()]
       FROM     ( SELECT    c.name
                  FROM      sys.columns c
                            INNER JOIN sys.tables t ON t.object_id = c.object_id
                  WHERE     t.name = 'Test'
                            AND c.name LIKE 'Name_%'
                ) AS D
                FOR                  XML PATH('') ,
                                         TYPE).value('.[1]', 'VARCHAR(MAX)'), 1, 1,
                                     N'')

select  @NameColumns

But this query has a warning in the execution plan:

Is there any way to remove this warning?

While I agree with @Kin about data types, I don't think this warning is as troublesome as you think. You're performing grouped concatenation, which is going to be orders of magnitude more expensive than any conversions anyway (and as Daniel said, unless your catalog views are massive - as in larger than physical memory - it is unlikely to affect anything in terms of estimation either).

I would write the query this way, taking care to never use varchar strings (which also means stop leaving out the N prefix on string literals), and also ensuring you use statement terminators:

DECLARE @NameColumns nvarchar(max); -- why 1024 when you use max below?

SET @NameColumns = STUFF(
  (SELECT   N',Test.' + name AS [text()] FROM
    (
      SELECT c.name FROM sys.columns AS c
        INNER JOIN sys.tables AS t
          ON t.object_id = c.object_id
        WHERE t.name = N'Test'
        AND   c.name LIKE N'Name_%'
    ) AS D FOR XML PATH(N''),
    TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'');

SELECT @NameColumns;

Even still, I don't believe there is any way to avoid the implicit convert without changing the output by avoiding TYPE/value() as Mikael suggested. I would say worry about this when you can actually prove that this has some material impact on the performance of the query. In my tests, the two different forms performed the same (we're talking sub-10ms every time), but of course if I have a table named Sales & Stuff then it becomes Sales &amp; Stuff without the implicit convert.


Password generator function What's the best way of creating a password generator user defined function function in SQL Server if it is not possible to use non deterministic funtions within functions?

I would like to create a function that returns a randomly generated varchar(10) using characters from a given string like this one:
"1234567890QWERTYUIOPASDFGHJKLZXCVBNM"

I have my own ideas that work but are not very natural solutions.

The idea behind the function is that if somebody requires a password reset  I could do the following:

UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = @UserID

A function would be easier to deploy and reuse than a separate table of preallocated passwords.

You have a few options (TL;DR -> working function is towards the end of Option #2, BUT also see Update section after Option #3 !):

If you are ok with just hex values (0 - 9 and A - F) you can just call CRYPT_GEN_RANDOM which was introduced in SQL Server 2008:

SELECT CRYPT_GEN_RANDOM(5) AS [Hex],
       CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(5), 2) AS [HexStringWithout0x],
       CONVERT(VARCHAR(20), CRYPT_GEN_RANDOM(10)) AS [Translated-ASCII],
       CONVERT(NVARCHAR(20), CRYPT_GEN_RANDOM(20)) AS [Translated-UCS2orUTF16]

returns:

Hex             HexStringWithout0x    Translated-ASCII    Translated-UCS2orUTF16
0x4F7D9ABBC4    0ECF378A7A            ¿"bü<Ý±Øï           튄ꆠ䤅㫘ᓟ멿ৢ폫씎䛯

As you can see, you need to use the format option of 2 on the CONVERT function so that it doesn't give you characters that, while more diverse, might be harder to type in, and you don't get the leading 0x. But you can also see that even with a single row, multiple calls will get different return values (just like NEWID() ).

Please note the "length" values passed into the CRYPT_GEN_RANDOM function. Since a hex byte is 2 characters, you only need to generate a 5-byte value. The CONVERT(VARCHAR... uses a "length" of 10 since each hex byte becomes a character. And the CONVERT(NVARCHAR... uses a "length" of 20 since every 2 bytes will become a character (when running this in a database with a default collation that does not end in _SC, which is most cases).
You can always create your own algorithm and pass in the value of CRYPT_GEN_RANDOM for the random aspect. AND, if you can structure your algorithm to be a single SELECT such that it can be done in an Inline Table-Valued Function / iTVF (usually using CTEs), then you technically can use a non-deterministic function (at least effectively) since the function call will pass in the expression instead of the result of that expression, like it does with Multistatement TVFs and Scalar UDFs:

USE [tempdb];
GO

CREATE FUNCTION dbo.GeneratePassword_InlineTVF(@RandomValue VARBINARY(10))
RETURNS TABLE
AS RETURN
  SELECT CONVERT(VARCHAR(20), @RandomValue, 2) +
         '~~' +
         CONVERT(VARCHAR(20), @RandomValue, 2) AS [HowRandomAmI?];
GO

CREATE FUNCTION dbo.GeneratePassword_MultistatementTVF(@RandomValue VARBINARY(10))
RETURNS @Result TABLE ([HowRandomAmI?] VARCHAR(100))
AS
BEGIN
  INSERT INTO @Result ([HowRandomAmI?])
         VALUES (CONVERT(VARCHAR(20), @RandomValue, 2) +
                 '~~' +
                 CONVERT(VARCHAR(20), @RandomValue, 2));
  RETURN;
END;
GO

CREATE FUNCTION dbo.GeneratePassword_ScalarUDF(@RandomValue VARBINARY(10))
RETURNS VARCHAR(100)
AS
BEGIN
  RETURN CONVERT(VARCHAR(20), @RandomValue, 2) +
         '~~' +
         CONVERT(VARCHAR(20), @RandomValue, 2);
END;
GO

And then run the tests:

SELECT * FROM tempdb.dbo.GeneratePassword_InlineTVF(CRYPT_GEN_RANDOM(10));
-- 27169EEC2B9CF7CC2731~~E9A95F49060BD41C0FF6

SELECT * FROM tempdb.dbo.GeneratePassword_MultistatementTVF(CRYPT_GEN_RANDOM(10));
-- 68EBC132814EA78602DE~~68EBC132814EA78602DE

SELECT tempdb.dbo.GeneratePassword_ScalarUDF(CRYPT_GEN_RANDOM(10)) AS [HowRandomAmI?];
-- 702DAF1C441C42FFBF5F~~702DAF1C441C42FFBF5F

And just to be sure about multiple rows in an iTVF:

SELECT TOP 5 rnd.[HowRandomAmI?]
FROM tempdb.dbo.GeneratePassword_InlineTVF(CRYPT_GEN_RANDOM(10)) rnd
CROSS JOIN [master].[sys].[objects];

returns:

HowRandomAmI?
---------------
1D57F0ABFDE44BCAED00~~AD57E9E2FF01768BB86F
264674BE1C9ABBC1572E~~6C75CD4D472935FDFA40
CB54CC6BB5F31F42FDEA~~B3EC7061027FCD36C9AC
44525355FC15655C1F4D~~4DDF874CD06BC4F2D7A4
0E51B6364F193F588C93~~08E2ED40ED9752267EF7

Putting all of this info into practice, we can do the following with regards to the original request:

CREATE FUNCTION dbo.GeneratePassword_Real(@RandomValue VARBINARY(30))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  WITH base(item) AS
  (
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
  ), items(item) AS
  (
    SELECT NULL
    FROM   base b1
    CROSS JOIN base b2
  )
  SELECT (
     SELECT TOP (LEN(@RandomValue))
            SUBSTRING('1234567890QWERTYUIOPASDFGHJKLZXCVBNM',
                      (CONVERT(TINYINT, SUBSTRING(@RandomValue, 1, 1)) % 36) + 1,
                      1) AS [text()]
     FROM   items
     FOR XML PATH('')
  ) AS [RandomPassword];

And then execute it as follows:

DECLARE @PasswordLength INT = 10;

SELECT * FROM tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength));

CAUTION: Please see Item # 2 below related to running this for multiple rows!
You can create a SQLCLR function that would have access to randomizing functionality. The main disadvantage here is that a Scalar function is much more likely to have its return value cached than an iTVF.

Update

Given the newly provided context for usage of this function from the Question update:

  The idea behind the function is that if somebody requires a password reset I could do the following:

    UPDATE Users SET Password = dbo.GeneratePassword() WHERE UserID = @UserID

we can address the following concerns as expressed in a comment on this answer:

  One needs to pass a parameter to the function with a random value. This enforces that it is necessary to know one way of creating random strings in order to use the function...

True. And it is admittedly slightly inconvenient. However, that does not negate this approach's ability to solve the issue at hand. It just requires some comments / documentation on how to properly use this function.

  ...the function couldn't be executed inside another function because you wouldn't be allowed to use a non deterministic function to generate random values inside the container function.

True. But there is no need to call this function within another function. This doesn't need to be a scalar function in order to be used in an UPDATE:

  USE [tempdb];
CREATE TABLE #Users (UserID INT NOT NULL PRIMARY KEY, [Password] VARCHAR(20));
INSERT INTO #Users (UserID, [Password]) VALUES (1, 'a'), (2, 'b');

DECLARE @PasswordLength INT = 10;

UPDATE tmp
SET    tmp.[Password] = pass.RandomPassword
FROM   #Users tmp
CROSS APPLY dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength)) pass
WHERE  tmp.UserID = 1;

SELECT * FROM #Users;

With regards to handling multiple rows (please see multi-row test in Option 2 above), functions of any type (user created, at least) -- Scalar UDF, Multistatement TVF, iTVF, SQLCLR UDF, or SQLCLR TVF -- are not guaranteed to run per row! You might need to get creative in how you convince the Query Optimizer to not cache the result, but there might be cases when nothing works. The following example shows the same iTVF being called twice. Passing in just @PasswordLength causes the output value to be cached. However, when you pass in a formula that incorporates a field from the current row, then it might behave as you are expecting it to:

  DECLARE @PasswordLength INT = 10;
SELECT pass.RandomPassword, pass2.RandomPassword
FROM [master].[sys].[objects] sac
CROSS APPLY tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@PasswordLength)) pass
CROSS APPLY tempdb.dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM((sac.[object_id] % 1)
            + @PasswordLength)) pass2

returns:

  RandomPassword    RandomPassword
--------------  --------------
KX1M1NSKE7        8I1OK6TJI3
KX1M1NSKE7        S0RA6AWKAJ
KX1M1NSKE7        MKXV9UAB0Y
KX1M1NSKE7        4RQ2K6O1RP
KX1M1NSKE7        8J8PKJP7K2

The point made by @BaconBits in a comment on the question, regarding it being best to not store readable passwords, is a valid and good point. However, circumstances might prevent that option from being possible, or at least possible at this point in time.


RAMdisk and MySQL in Ubuntu20.04 I'm working on a development in WordPress/WooCommerce which has a very large number of products, currently 180,000. The problem here is, of MySQL which slows down dramatically as the product number exceeds 60,000. This is mainly due to MySQL apparently using the Ubuntu filing system to store data. It would be better if a different database could be used but in the meantime I was considering setting up a RAMdisk and using that for the MySQL temp data. Now there are any number of posts on how to do it, all of which are wrong for Ubuntu 20.04. Running 'df' I can see a mount point at /dev/shm which appears to be usable and wondered if it is.  My knowledge of Linux isn't great enough to be sure. Tutorials also suggest editing /etc/rc.local which no longer exists in Ubunto 20 along with /etc/apparmor.d/local/usr.sbin.mysqld which also doesn't exist. Also /etc/mysql/my.cnf  has no reference to tmpdir.  All this would appear to indicate that what I'd like to do (other than use a grown up database like PostgreSQL or SQL-Server) can't be done or perhaps doesn't need to be done. Of course running the whole MySQL database in RAM would be fast but unfeasible, I know.
Any suggestions?

... MySQL ... slows down dramatically as the product number exceeds 60,000.

MySQL does not run slowly.  Trust me; it really, really doesn't.
What does get slower are the queries that you run against the data structures inside MySQL.

I was considering setting up a RAMdisk and using that for the MySQL temp data.

Ah ha!
It's not the size of the database that's giving you trouble.  It's the amount of temporary space being used to process the queries.  That says to me that you have some [poorly written] queries that are in need of some tuning.
Turn on the Slow Query log, see which queries appear in there and then start tuning those.
Often, it's only a handful of poor queries that bring an entire system to its knees.
Since volume/size seems to be an issue, here's some common things to think about:

Indexes.
Do you have [the right] indexes to support your queries?

Using "select *"?  Don't.
Always specify the columns you want explicitly in every query.
Pulling back redundant columns only adds to the network overhead seen by the client.

Using "select distinct" to get rid of "duplicate" rows.  Again, don't.
Fix the join conditions that are introducing the "duplicates".

Joining to a bracketed select statement?  (This might be fixed in newer versions).
In some older versions, joining from a table to a select statement wrapped in brackets caused MySQL to "create" the bracketed result set in temporary space before joining it to the other table.

... running the whole MySQL database in RAM would be fast but unfeasible, I know.

Fast?  Probably not.  You're effectively "conning" the operating system into thinking that a big chunk of memory is actually disk space but, unless you have a lot more memory  besides that, the O/S will probably need to page that memory in and out, to and from disk, which is slow.
Worse still, as soon as the machine was powered off, the RAMDisk and everything on it (i.e. "the whole MySQL database") would evaporate, like the morning dew.
Possibly not the most resilient operating model.  :-)


Problems connecting to Windows-based Postgresql Other computers are having trouble even finding the running Postgresql 9.1 on my Windows 7 machine.  Local connections work just fine of course.

In my pg_hba.conf I have:

#office subnets
host    all             all             192.168.##.0/24            md5
host    all             all             192.168.##.0/24            md5

In postgresql.conf I have (Yes, port 5433 is intentional):

listen_addresses = '*'      # what IP address(es) to listen on;
                # comma-separated list of addresses;
                # defaults to 'localhost', '*' = all
                # (change requires restart)
port = 5433             # (change requires restart)

Upon running netstat -a -b in the cmd I find:

 Can not obtain ownership information
   TCP    0.0.0.0:5433           XXXXX-PC:0           LISTENING

Heck, I even made a firewall exception:

Protocols and Ports:
   Local port: Specific Ports, 5433
   Remote port:  All Ports
Scope: Any IP Address
Advanced: Profiles: Private, Public
Programs and Services:  Apply to this service:  postgresql-x64-9.1

The typical response from pgAdmin is always the dreaded:

Server doesn't listen

could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "...." and accepting TCP/IP connections on port 5433?

Any ideas?

Here are the steps of troubleshooting I would recommend:

From the local machine try the following.  If they work you know the issue is not PostgreSQL.  If they don't work then you know your issue is local to the machine.

psql -h localhost -p 5433
psql -h [external ip] -p 5433
If the latter fails, try temporarily disabling all firewalls and trying again

Now if that works the next step is to troubleshoot the network.  Traceroute may be helpful here.  Also try connecting to any other open ports using other software and make sure that works.  From there, you may be able to figure out exactly where the packets are being dropped.


Incorrect Query Result I have a table with the following data:

    unit_codes
    kind    | code
-------------------------
    UNIT    | PS3b
    UNIT    | PS3B
    UNIT    | PS3C

If I run this select I get the correct result.

select * from unit_codes where kind = 'UNIT';

Result:

UNIT    PS3b
UNIT    PS3B
UNIT    PS3C

If I filter on the code column I get no rows.

select * from unit_codes where code = 'PS3C';

No rows are retuned.

I suspect there is a non visible character on the code column for some rows. The problem started after some OS patches were installed.

We are running PostgreSQL 9.2.4 on sparc-sun-solaris2.10, compiled by cc: Sun C 5.9 SunOS_sparc 2007/05/03, 32-bit.

Aside from invisible characters, the possibility of an OS patch changing the results of such an query exists through a change in locales.

An index involving text types is only valid if the underlying collation never changes. Postgres relies on the OS for comparing strings, through the libc, so a change in an OS collation may have the effect of an index becoming unusable, as if it was corrupted.

If there is an index on the column, you can test this hypothesis by issuing SET enable_indexscan TO off; before the offending query and see if it gives the same results. This check may also reveal an index corruption independantly of any problem with the collation.


What system privilege is needed for a user to manage user accounts only I'm looking to delegate user termination to our NOC.

Requirements are:

A user NOC will be created
User NOC will execute stored procedures allowing them to either 1) lock another db account or 2) Modify a db account.
NOC should not be able to run alter user statements without using the stored procedures.

Create the procedures with AUTHID DEFINER option (this is the default if you skip this clause) in a schema who has the ALTER USER privilege.

Them simply grant execute on these procedures to NOC.

NOC does not need any other privileges, as the procedures will run as if their owner would have called them.


Does the system process slow down my Database access? I have a Fairly large DB containing mostly html documents. I wrote a python script that fetches always 1000 html documents at once from my database cur.execute("SELECT id,url,html_file FROM html ORDER BY id OFFSET %s LIMIT %s;" % (offset, limit)). Afterwards I do a bit of regular expression on the html documents. Because I have a lot html files to go through I track the time how long each step takes. For the first 4000 times retrieving the html documents from the database took around 3 seconds. Now it is up to 4 Minutes. I'm running a Windows 7 machine and took a look at the Resource Monitor. The Database is on its own HDD with nothing else on it. But in the Resource Monitor i could see that the System Process constantly reads something  out of my Postgres Folder

Image   PID File    Read (B/sec)    Write (B/sec)   Total (B/sec)   I/O Priority    Response Time (ms)
System  4   E:\PostgreSQL\data\base\10596207\10598404.1 13,855,193  0   13,855,193  Normal  9
System  4   E:\PostgreSQL\data\base\10596207\10598404.1 11,182,442  0   11,182,442  Normal  9

so the question is is this normal or is the system process the culprit here and how do I stop it? (malware and virus scanning are disabled as well as search indexing)

I followed the advice from Reaces and found that superfetch was already disabled. But I also downloaded the process explorer from sysinternals and there I observed something strange. After about the same amount of parsed html documents (about 4,513,000 fetched in package of a 1,000 from the database) the postgres process begins to write a lot of stuff to the HDD up to this point it did read ~3TB from disk and write ~100mb.

But now about 3000 html documents later the process explorer shows me that the postgress proces did write 10GB to the HDD with heavy write access for every cur.execute("SELECT id,url,html_file FROM html ORDER BY id OFFSET %s LIMIT %s;" % (offset, limit)) command

What is the database doing and how do I stop it? my best guess is that the database orders it self to be more efficient the next time but that does not help me now.

One of the common disk usage process on a windows 7 machine under the System process is Superfetch.
You might want to look into disabling it disabling superfetch.

The reason why it's likely Superfetch is because you're seeing

Only reads
It's not your OS disk
You're running windows 7, not a server version
more slowdown as the amount of files increases

If that doesn't help Sysintenals' Process Explorer might help you get a better idea of what's going on. sysinternals


PostgreSQL joining using JSONB I have this SQL:

CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB);

INSERT INTO test(data) VALUES
   ('{"parent":null,"children":[2,3]}'),
   ('{"parent":1,   "children":[4,5]}'),
   ('{"parent":1,   "children":[]}'),
   ('{"parent":2,   "children":[]}'),
   ('{"parent":2,   "children":[]}');

That would give:

 id |                 data
----+--------------------------------------
  1 | {"parent": null, "children": [2, 3]}
  2 | {"parent": 1, "children": [4, 5]}
  3 | {"parent": 1, "children": []}
  4 | {"parent": 2, "children": []}
  5 | {"parent": 2, "children": []}

When doing normal one to many, it would show something like this:

SELECT *
FROM test x1
  LEFT JOIN test x2
    ON x1.id = (x2.data->>'parent')::INT;
 id |                 data                 | id |               data
----+--------------------------------------+----+-----------------------------------
  1 | {"parent": null, "children": [2, 3]} |  2 | {"parent": 1, "children": [4, 5]}
  1 | {"parent": null, "children": [2, 3]} |  3 | {"parent": 1, "children": []}
  2 | {"parent": 1, "children": [4, 5]}    |  4 | {"parent": 2, "children": []}
  2 | {"parent": 1, "children": [4, 5]}    |  5 | {"parent": 2, "children": []}
  5 | {"parent": 2, "children": []}        |    |
  4 | {"parent": 2, "children": []}        |    |
  3 | {"parent": 1, "children": []}        |    |

How to join based on children (using LEFT JOIN or WHERE IN)? I've tried:

SELECT data->>'children' FROM test;
 ?column?
----------
 [2, 3]
 [4, 5]
 []
 []
 []

SELECT json_array_elements((data->>'children')::TEXT) FROM t...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT json_array_elements((data->>'children')::JSONB) FROM ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT json_to_record((data->>'children')::JSON) FROM test;
ERROR:  function returning record called in context that cannot accept type record
HINT:  Try calling the function in the FROM clause using a column definition list.

SELECT * FROM json_to_record((test.data->>'children')::JSON);
ERROR:  missing FROM-clause entry for table "test"
LINE 1: SELECT * FROM json_to_record((test.data->>'children')::JSON)...

This would be more efficient:
With jsonb and jsonb_array_elements_text() in pg 9.4+
EXPLAIN
SELECT p.id AS p_id, p.data
     , c.id AS c_id, c.data
FROM   test p
LEFT   JOIN LATERAL jsonb_array_elements_text(p.data->'children') pc(child) ON TRUE
LEFT   JOIN test c ON c.id = pc.child::int;
db<>fiddle here
About jsonb_array_elements_text():

How to turn JSON array into Postgres array?

Use the -> operator instead of ->> in the reference to children. The way you have it, you'd first cast json / jsonb  to text and then back to json.
The clean way to call a set-returning function is LEFT [OUTER] JOIN LATERAL. This includes rows without children.
To exclude those, change to a [INNER] JOIN LATERAL or CROSS JOIN - or the shorthand syntax with a comma:
, json_array_elements(p.data->'children') pc(child)

Avoiding duplicate column names in result.
With json and json_array_elements() in pg 9.3
SELECT p.id AS p_id, p.data AS p_data
     , c.id AS c_id, c.data AS c_data
FROM   test p
LEFT   JOIN LATERAL json_array_elements(p.data->'children') pc(child) ON TRUE
LEFT   JOIN test c ON c.id = pc.child::text::int;

Old sqlfiddle
Aside: A normalized DB design with basic data types would be way more efficient for this.


AWS RDS PostgreSQL dump/restore - syntax error I am trying to import a postgresql database from an EC2 instance to RDS on the same subnet, so far I am doing the following...

1) Dump EC2 DB:

pg_dump --host localhost --port 5432 -Fc --encoding='UTF8' -U postgres ProcessorDB > /home/jboss/proc_dump_jan15.dump

2) Restore from EC2:

psql -f /home/jboss/proc_dump_jan15.dump --host=blahrds.11938475.eu-west-1.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=ProcessorDB

I am able to connect to the remote DB but I am getting the following errors:

psql:/home/jboss/proc_dump_jan15.dump:1: ERROR:  syntax error at or near "PGDMP"
LINE 1: PGDMP
             REVOKE ALL ON SCHEMA public FROM postgres;
        ^
GRANT
GRANT
psql:/home/jboss/proc_dump_jan15.dump:5: ERROR:  syntax error at or near ""
LINE 1:     'en'
        ^
psql:/home/jboss/proc_dump_jan15.dump:14: ERROR:  syntax error at or near ""
LINE 1:     'free',
        ^
psql:/home/jboss/proc_dump_jan15.dump:21: ERROR:  syntax error at or near ""
LINE 1:     'windows',

This goes on, then changes to:

psql:/home/jboss/proc_dump_jan15.dump:316: ERROR:  invalid byte sequence for encoding "UTF8": 0xc2 0x40
psql:/home/jboss/proc_dump_jan15.dump:316: invalid command \?0?4
psql:/home/jboss/proc_dump_jan15.dump:317: invalid command \?????M?
psql:/home/jboss/proc_dump_jan15.dump:318: invalid command \)??LF,?C?{<??y??ݚ???Ɲ???0??{?dV
                                                                                ?
psql:/home/jboss/proc_dump_jan15.dump:321: ERROR:  invalid byte sequence for encoding "UTF8": 0xbd
psql:/home/jboss/proc_dump_jan15.dump:331: invalid command \?`?v?ʺ??;6????6??N??.????c?~???&?G(??
psql:/home/jboss/proc_dump_jan15.dump:335: invalid command \@(??n']??sIZ??1VZ?º???????j?+??λ?=L?E??ܡ|c?0t
psql:/home/jboss/proc_dump_jan15.dump:338: invalid command \?A???N??/hL???Q??G??r
psql:/home/jboss/proc_dump_jan15.dump:354: invalid command \??I?$?R?o??¬???NJ?-F'??x?0z??z??hŗc?
psql:/home/jboss/proc_dump_jan15.dump:357: invalid command \x?r?Y??gMb~?墶6wб??m'aem?o????]X?s?????}??u?&?;-xY8?1)?}c??-@??h?K???IA?֒qv??Hs?g??J?DoD?Y?m
psql:/home/jboss/proc_dump_jan15.dump:361: invalid command \??W?]U?22?U-??N?
psql:/home/jboss/proc_dump_jan15.dump:362: invalid command \4??im?@??ښ?S?j???ꨫ?d܊פJ?cu??l!?ް̳u?5+^O??>=?k+]???԰A??5????~??N???0??:=??ՙM?3zm
psql:/home/jboss/proc_dump_jan15.dump:372: invalid command \HF??w?
psql:/home/jboss/proc_dump_jan15.dump:378: invalid command \g?#?s?t?I,???T?}5???

Cut short for brevity...

I spotted the 'invalid byte sequence for encoding "UTF8"' and added the --encoding attribute to the dump command but that hasn't had any effect

Anyone know what the issue might be? Am I missing an option on the dump? The AWS docs are a bit vague here

Nb. the EC2 database is PG 9.0 and the RDS database is PG 9.3.5 but I assume this is ok as the dump/restore will deal with the migration?

A pg_dump output with the -Fc (custom) is not a SQL script, it's a compressed archive that only pg_restore is able to handle.

You may either pass the dump file to pg_restore and pipe its output to psql (often done that way and close to what you tried):

pg_restore /home/jboss/proc_dump_jan15.dump | psql  --host=blahrds [other options]...

...or use the -d,-h,... and other options of pg_restore to have it connect to the database and skip the psql invocation.

See pg_restore manpage for 9.3 for details.

  Nb. the EC2 database is PG 9.0 and the RDS database is PG 9.3.5 but I
  assume this is ok as the dump/restore will deal with the migration?

The recommended method is to use pg_dump 9.3 against the 9.0 database to create the dump. The reasoning is that newer versions always know how to extract older databases such that they will import flawlessly into their version. The opposite is not true, since obviously at the time when pg_dump 9.0 is released, the authors don't know what compatibility problems future versions might bring.


What's the most appropriate NLS_SORT parameter to cover all/most European languages? I'm developing an Oracle 11g-based application which is likely to be used by clients in many different countries (mostly European). I want the program to change its NLS_SORT so that it's case insensitive on startup using the ALTER SESSION SET NLS_SORT='???'; command.

I'm going to have a config file which contains the NLS_SORT setting the user wants to use so it is editable, but I want a default which will cover European languages. Which is the best one to choose?

WEST_EUROPEAN would seem to be the obvious choice, but there is no default that lets you avoid setting it on an individual language basis.


How mysql binlog position generate? The binlog position isn't continuous, so how mysql binlog position generate?

There no document about this.

The binlog position is the byte offset into the current binlog.  So, the pair of binlog filename and pos uniquely identify the location in the virtually unlimited binlog.

The GTID (if being used) also provides a unique location, but it is not "offset" based.


Insert Does Not Increment Primary Key Correctly I need to figure out how to insert into this table without breaking the application that runs on it. When I run an insert statement outside the application, I select the max uid (which is the indexed primary key) and add one, then insert the data with that uid.

The application is our ERP and was purchased from a vendor. Their support does not extend to SQL help so I have no way of seeing how it is doing the insert. There are no sequences in the database at all either.

Something does not make it down to the application though and it will not allow new inserts into the table through the application until I delete the rows I inserted through SQL. It gives an SQL error that the primary key customer_salesrep_uid is already taken. The application does not seem to know about the inserts I made outside of it and the increases to the uid field. There must be a counter somewhere that I need to update? The index should update by itself on insert.

Update 8/10 re comments:
Thank you all for your advice. I will try the profiler and also will try inserting in test with proper locks, with a lower uid than max, and with a much higher uid than max and see if I can glean any further clues from that.

Table Structure:

    Column_name Type    Computed    Length  Prec    Scale   Nullable    TrimTrailingBlanks  FixedLenNullInSource    Collation
    customer_salesrep_uid   int no  4   10      0       no  (n/a)   (n/a)   NULL
    company_id  varchar no  8                   no  yes no  SQL_Latin1_General_CP1_CI_AS
    customer_id decimal no  9   19      0       no  (n/a)   (n/a)   NULL
    salesrep_id varchar no  16                  no  yes no  SQL_Latin1_General_CP1_CI_AS
    commission_percentage   decimal no  9   19      4       no  (n/a)   (n/a)   NULL
    primary_salesrep_flag   char    no  1                   no  yes no  SQL_Latin1_General_CP1_CI_AS
    row_status_flag int no  4   10      0       no  (n/a)   (n/a)   NULL
    date_created    datetime    no  8                   no  (n/a)   (n/a)   NULL
    created_by  varchar no  255                 no  yes no  SQL_Latin1_General_CP1_CI_AS
    date_last_modified  datetime    no  8                   no  (n/a)   (n/a)   NULL
    last_maintained_by  varchar no  255                 no  yes no  SQL_Latin1_General_CP1_CI_AS

No Identity columns

Indexes:

    index_name  index_description   index_keys
    ak_customer_salesrep    nonclustered, unique, unique key located on PRIMARY company_id, customer_id, salesrep_id
    pk_customer_salesrep    clustered, unique, primary key located on PRIMARY   customer_salesrep_uid

The application is our ERP and was purchased from a vendor. Their support does not extend to SQL help so I have no way of seeing how it is doing the insert. There are no sequences in the database at all either.

You could run a trace using SQL Profiler to catch the exact statement that your application is executing.

Also, a slight alteration to Scott's suggestion, I wouldn't use a TABLOCK, instead, something like:

BEGIN TRANSACTION
SELECT @customer_salesrep_uid = max(customer_salesrep_uid)
    FROM YourTable WITH (XLOCK, READPAST)...

This should reduce the risk of locks on the table.


Clustered monotonically increased index insert performance I have a table with field Id (bigint, IDENTITY) as primary key and clustered index on it.
I inserted 400 rows and saw execution plan. I got: the relative query cost for this insertion 36% and for this query "Clustered Index Insert"'s cost 97%. What's going on? Why does it work so slow?

ADDED:

continue

See also SQL Server insert performance

The relative cost of the components of an execution plan is not always reliable.

The INSERTED scan or deleted scan shouts TRIGGER to me. This is your problem most likely.

Are you looping over 400 rows and sending an email in an extreme example?


Need SQL outer join help with 3 tables See below example. ID_1 and ID_A are primary keys for table_1 and table_3 respectively. Table_2, the primary key is ID_1 and ID_A. ID_A is joined to one and only one ID_1 via table_2 due to business rules.

I need a query that will return all rows from table_1 where the value_1 = 11 and joined to that, I need only the rows from table_3 that have a value_a = 'a' if it joins to a row in table_1 that has a value_1 = 11.

Please see results required. Note there is no row

|1 | a |

in the result set because value_a for id_a = 'b';

I've tried a left outer join but all ID_1 are returned. I've also tried CTE, to no avail. Here's one SQL, I tried.

Select ID_1, Value_1, ID_A, VALUE_A
   From TABLE_1 t1
   Join TABLE_2 t2 on t1.ID_1 = t2.ID_1
   Left Outer Join TABLE_3 t3 on t2.ID_A = t3.ID_A;

I've also tried using a temporary table which include only the results from TABLE_1 that I need, but the left outer join using the temp table still returns the above mentioned row.

The DB is Oracle, but I prefer a standard solution. Also, performance is a consideration, given that TABLE_1 has over 200 million rows and TABLE_3 has about 4 times that many.

TABLE_1

| ID_1 | Value_1  |
-------------------
| 1    | 11       |
| 2    | 12       |
| 3    | 11       |
| 4    | 13       |

TABLE_2

| ID_1 | ID_A  |
----------------
| 1    | a     |
| 1    | b     |
| 3    | c     |
| 4    | d     |

TABLE_3

| ID_A | Value_A |
------------------
| a    | b       |
| b    | a       |
| c    | e       |
| d    | a       |

Results Required

| ID_1 | ID_A    |  Value_1  | Value_A |
----------------------------------------
| 1    | b       |    11     |    a    |
| 3    | <NULL>  |    11     | <NULL>  |

First, join TABLE_2 and TABLE_3 using an inner join and additionally filtering on Value_A = 'a':

SELECT
  t2.ID_1,
  t3.ID_A,
  t3.Value_A
FROM
  TABLE_2 t2
  INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
WHERE
  t3.Value_A = 'a'

This will give you the following result set:

ID_1  ID_A  Value_A
----  ----  -------
1     b     a
4     d     a

Now use the above as a derived table and join it, using an outer join this time, to TABLE_1, additionally filtering the results on Value_1 = 11:

SELECT
  t1 .ID_1,
  t23.ID_A,
  t1 .Value_1,
  t23.Value_A
FROM
  TABLE_1 t1
  LEFT JOIN
  (
    SELECT
      t2.ID_1,
      t3.ID_A,
      t3.Value_A
    FROM
      TABLE_2 t2
      INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
    WHERE
      t3.Value_A = 'a'
  ) t23 ON t1.ID_1 = t23.ID_1
WHERE
  t1.Value_1 = 11
;

That will give you the output you want:

ID_1  ID_A  Value_1  Value_A
----  ----  -------  -------
1     b     11       a
3     NULL  11       NULL

However, nesting a query is not the only way to solve your problem – you can also use a nested join, which is much more concise:

SELECT
  t1.ID_1,
  t3.ID_A,
  t1.Value_1,
  t3.Value_A
FROM
  TABLE_1 t1
  LEFT JOIN
    TABLE_2 t2
    INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
  ON t1.ID_1 = t2.ID_1
WHERE
  t1.Value_1 = 11
;

The last query implements exactly the same logic as the previous query: first, tables TABLE_2 and TABLE_3 are joined and the result is filtered, then it is joined to TABLE_1 and the final set is filtered again. Some people also add brackets around a nested join:

FROM
  TABLE_1 t1
  LEFT JOIN
  (
    TABLE_2 t2
    INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
  )
  ON t1.ID_1 = t2.ID_1

to make it clearer (perhaps both for themselves and for future maintainers) that the nested join takes place before the outer-level one, logically, although the syntax is unambiguous enough without them.

Nevertheless, many people find it confusing even with brackets, and if you find yourself struggling with it as well, there is another option – right outer join:

SELECT
  t1.ID_1,
  t3.ID_A,
  t1.Value_1,
  t3.Value_A
FROM
  TABLE_2 t2
  INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
  RIGHT JOIN TABLE_1 t1 ON t1.ID_1 = t2.ID_1
WHERE
  t1.Value_1 = 11
;

Again, the logical sequence of events specified by the FROM clause repeats that of the derived table solution: a join between TABLE_2 and TABLE_3, filtered, is followed by a join with TABLE_1. The different syntax does not alter the outcome and the results produced still match your requirements.


Drop all tables from database command apart from named few I have been looking for a command to drop all tables from a single database apart from a few tables without doing something like this:

DROP table table1, table2, table3

and ignoring the tables I do not want to drop. Is there a better way to do this by dropping all tables apart from the few that I do not want to drop?

Using such a CURSOR, you can print or execute all the DROP statements:

DECLARE cur_del CURSOR FOR
    SELECT QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name)
    FROM sys.tables
    WHERE name NOT IN ('x', 'y', 'z', ...);

OPEN cur_del;

DECLARE @table sysname;
DECLARE @sql nvarchar(max);

FETCH NEXT FROM cur_del
INTO @table;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql =  'DROP TABLE ' + @table + ';';
    PRINT @sql;
    --EXEC sp_executesql @sql;

    FETCH NEXT FROM cur_del INTO @table
END

CLOSE cur_del;
DEALLOCATE cur_del;

This query only prints the DROP statements out. You can uncomment the EXEC line if you want DROP them.

The first SELECT does not look for anything at all aside from what is included in the IN(...) and you have to make sure it does not match more table than what you really want to delete.

In the WHERE clause, you can replace name (table name) by the fully qualify name with schema using QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name) NOT IN ('schema.x', ...).
If there is only one schema, you can add AND SCHEMA_NAME(schema_id) = 'xxx' and keep name.

It also does not look for dependencies which may prevent DROP.

If there are Foreign Keys, you can first DROP them using a similar cursor along with this query:

SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(f.schema_id))+'.'+ QUOTENAME(OBJECT_NAME(o.object_id)) + ' DROP CONSTRAINT ' +  QUOTENAME(f.name)
FROM sys.foreign_keys f
INNER JOIN sys.objects o ON f.parent_object_id = o.object_id
WHERE OBJECT_NAME(f.referenced_object_id) NOT IN ('x', 'y', 'z')
    AND  OBJECT_NAME(o.object_id) NOT IN ('x', 'y', 'z');


Update with id returned from insert using WITH I need a help with this query I make an insert that returns me an id, then I need to run an UPDATE with that id returned...

WITH disca AS (
    INSERT INTO callcenter.campanha_cliente_chamada
        (   id_campanha_cliente,
                cod_contato,
                telefone,
                telefone_discar,
                id_status_chamada,
                tentativa,
                monitorando,
                acionar_proxima,
                datahora_update,
                datahora_registro
        ) VALUES (154424,1824704,'(11) 2524-9636','1125249636',1,1,0,0,CURRENT_TIMESTAMP::timestamp(0),CURRENT_TIMESTAMP::timestamp(0))
    RETURNING id_campanha_cliente_chamada
) UPDATE callcenter.campanha_cliente_chamada
  SET accountcode = ('D-'||id_campanha_cliente_chamada||'-'||tentativa)::varchar(128)
  WHERE id_campanha_cliente_chamada = disca;

You don't need to INSERT and then UPDATE - and it isn't advisable anyway, to insert/update/delete the same rows twice in one statement. You can calculate the values and then do the INSERT:

WITH disca
  ( id_campanha_cliente_chamada,
    id_campanha_cliente, cod_contato,
    telefone, telefone_discar,
    id_status_chamada, tentativa, monitorando, acionar_proxima,
    datahora_update, datahora_registro
  )
AS
  ( VALUES
      ( nextval('callcenter.campanha_cliente_chamada_id_campanha_cliente_chamada_seq'),
        154424, 1824704,
        '(11) 2524-9636', '1125249636',
        1, 1, 0, 0,
        CURRENT_TIMESTAMP::timestamp(0), CURRENT_TIMESTAMP::timestamp(0)
      )
  )
INSERT INTO callcenter.campanha_cliente_chamada
  ( id_campanha_cliente_chamada,
    id_campanha_cliente, cod_contato,
    telefone, telefone_discar,
    id_status_chamada, tentativa, monitorando, acionar_proxima,
    datahora_update, datahora_registro,
    accountcode
  )
SELECT
    id_campanha_cliente_chamada,
    id_campanha_cliente, cod_contato,
    telefone, telefone_discar,
    id_status_chamada, tentativa, monitorando, acionar_proxima,
    datahora_update, datahora_registro,
    ('D-' || id_campanha_cliente_chamada || '-' || tentativa)::varchar(128)
FROM
    disca;

You can find the actual name of the sequence that has to be put in nextval('...') with \d campanha_cliente_chamada.


Count 2 tables same query - PG 9.3 I've got two tables that I need a count on both of them.

table1: id
table2: cid (table1.id = table2.cid) -- Join can be done by this.

I already got two queries that I'm using, and wanna put them in one single query.

Query related to table1:

WITH c_status AS (
select
CASE
  WHEN regdate = 1 THEN 'yes'
  WHEN regdate = 2 THEN 'no'
  from table1
  end as status_1
)

select status_1, count(*) AS c_status_count from c_status group by status_1

OUTPUT:

yes 548
no  2269

Query related to table2:

WITH u_status AS (
select
CASE
  WHEN regdate = 1 THEN 'yes'
  WHEN regdate = 2 THEN 'no'
  from table2
  end as status_2
)

select status_2, count(*) AS u_status_count from u_status group by status_2

OUTPUT:

yes 564256
no  31452345234

Question:

How can I put those two queries together? I want one single query reporting both counts.

UPDATE: Desired output:

u_status yes    548
u_status no     2269
c_status yes    564256
c_status no 31452345234

What you want can be achived in the following fashion:

WITH
c AS  -- This part of CTE for counting on table 1
(
    -- This select will just return ONE row
    SELECT
        sum(case when regdate = 1 then 1 end) AS c_status_yes,
        sum(case when regdate = 2 then 1 end) AS c_status_no
    FROM
        table1
),
u AS   -- And this one for table 2
(
    -- This select will also return just one row
    SELECT
        sum(case when regdate = 1 then 1 end) AS u_status_yes,
        sum(case when regdate = 2 then 1 end) AS u_status_no
    FROM
        table2
)
-- And you just put everything together
SELECT
    c_status_yes,
    c_status_no,
    u_status_yes,
    u_status_no
FROM
    c, u ;  -- This is a 1x1 CROSS JOIN, don't worry

NOTE: the little trick: sum(case when regdate = 1 then 1 end) is in fact counting how many times the regdate = 1 condition is met; when regdate <> 1 the case statement returns NULL, effectively not summing 1 in that case.

You can also use a count(*) filter instead of a sum(case when ...), although I am not sure this could already be done in version 9.3 (I've tested on 9.4):

WITH c AS
(
    SELECT
        count(*) filter (where regdate = 1) as c_status_yes,
        count(*) filter (where regdate = 2) as c_status_no
    FROM
        table1
)
, u AS
(
    SELECT
        count(*) filter (where regdate = 1) as u_status_yes,
        count(*) filter (where regdate = 2) as u_status_no
    FROM
        table2
)
SELECT
    c_status_yes,
    c_status_no,
    u_status_yes,
    u_status_no
FROM
    c, u ;

dbfiddle here


SQL Query question (items not having some value in a column) Here is a conundrum I need to solve with some T-SQL Magic, I tried and failed.

It's one of these things that, before I think of the actual solution I think I can solve in 3 minutes with some T-SQL, but then when the "rubber meets the road" I just can't picture in my mind the right operators to achieve this logic.

I'm wondering if anyone in the Stack Exchange community can find an elegant solution to this one. I guess with a combination of 3 or 4 queries using temp tables or cursors I could find the solution, but it would not be SET based...

I have a table that looks like this (more or less...)

CREATE TABLE Computers (
   ID               int IDENTITY(1,1),
   ComputerName     varchar(255),
   OS               varchar(255),
   AppName          varchar(255),
   AppVersion       varchar(10)
)

So, every row has a computer name, an OS value (that can be either Windows 7 OR Windows 10) and then a description of an application (e.g. AppName = "Notepad++", AppVersion = "7.5.6")

ComputerName   | OS           | AppName   | AppVersion
Computer 1     | Windows 7    | App 1     | 1.0
Computer 1     | Windows 7    | App 2     | 1.0
Computer 1     | Windows 7    | App 3     | 1.0
Computer 2     | Windows 10   | App 4     | 1.0
Computer 2     | Windows 10   | App 5     | 1.0
Computer 3     | Windows 10   | App 4     | 1.0
Computer 4     | Windows 7    | App 4     | 1.0
Computer 4     | Windows 7    | App 5     | 1.0
Computer 5     | Windows 7    | App 1     | 1.0
Computer 5     | Windows 7    | App 4     | 1.0
Computer 5     | Windows 7    | App 5     | 1.0

The way it works is like this: if a computer has 10 apps installed, then you will have 10 rows in this table (one for each app). The computername and OS will be repeated (please, don't start a discussion about 2nd normal form... this is strictly a question about a query to solve a problem).

So, the problem I need to solve is: I need to find a list of computers WHERE the OS is Windows 7 (that's EAAAASY) but...

Those computers should only show up in my list if ALL the applications that are reported in this table for that PC are reported as being used on other Windows 10 computers.

If a Windows 7 computer has apps that are not listed in a Windows 10 computer, then they should not show up in this list. Each and every app for a computer must comply with this rule for the computer to qualify on my list.

The idea is to know that the computers I'm getting in my SELECT have all apps that I know work for Windows 10, so they should be safer to migrate.

Sample:

For those trying this at home, here is a CREATE TABLE and some INSERTs you have the right idea of what my data looks like

-- Create the table
 CREATE TABLE Computers (
    ComputerName    varchar(255),
    OS              varchar(255),
    AppName         varchar(255),
    AppVersion      varchar(10)
 )

 -- Insert some values...
 INSERT INTO Computers
 VALUES
 ('Computer 1', 'Windows 7', 'App 1', '1.0'),
 ('Computer 1', 'Windows 7', 'App 2', '1.0'),
 ('Computer 1', 'Windows 7', 'App 3', '1.0'),
 ('Computer 2', 'Windows 10', 'App 4', '1.0'),
 ('Computer 2', 'Windows 10', 'App 5', '1.0'),
 ('Computer 3', 'Windows 10', 'App 4', '1.0'),
 ('Computer 4', 'Windows 7', 'App 4', '1.0'),
 ('Computer 4', 'Windows 7', 'App 5', '1.0'),
 ('Computer 5', 'Windows 7', 'App 1', '1.0'),
 ('Computer 5', 'Windows 7', 'App 4', '1.0'),
 ('Computer 5', 'Windows 7', 'App 5', '1.0')

With this example, the only computer that qualifies for my query would be Computer 4, as is the only Windows 7 computer where ALL apps associated with it (Apps 4 and 5) also exist in at least one row for a Windows 10 PC.
Computer 5 does not qualify because one of the apps associated with it ("App 1") only exists in this set on another Windows 7 computer (Computer 1) so I can't say that this app is already working on a Windows 10 PC.
Computers 2, and 3 are Windows 10. So that should be enough to discard them....
Computer 1 should also NOT qualify, all their apps are not running in Windows 10 PCs.

Hopefully makes sense...

I believe the following query will give you the right computer name

 select ComputerName from dbo.computers
 where os= 'windows 7'
 except
 select ComputerName from dbo.computers
 where os= 'windows 7'
 and appname not in (select appname from dbo.computers where os='windows 10')

The result is:


ORA-00907: missing right parenthesis on query with subqueries and EXCEPT operator I am getting

  ORA-00907: missing right parenthesis

for the following query:

SELECT PEOPLE.NAME FROM
CLIENTS K INNER JOIN PEOPLE ON K.ID_L = PEOPLE.ID_L
WHERE NOT EXISTS
(
(SELECT DISTINCT ARTIST.ID_U
FROM CLIENTS INNER JOIN ADORES ON CLIENTS.ID_L = ADORES.ID_L
INNER JOIN ARTIST ON ADORES.ID_U = ARTIST.ID_U
WHERE CLIENTS.ID_L = K.ID_L)

EXCEPT

(SELECT DISTINCT ARTIST.ID_U
FROM CLIENTS INNER JOIN BOUGHT ON CLIENTS.ID_L = BOUGHT.ID_L
INNER JOIN ARTWORK ON BOUGHT.ID_D = ARTWORK.ID_D
INNER JOIN ARTIST ON ARTWORK.ID_U = ARTIST.ID_U
WHERE CLIENTS.ID_L = K.ID_L)
)

I've checked both subqueries - both are ok. It works if I remove the EXCEPT operator and only use one subquery (but I need to actually test that both are equal).

Any idea on what the problem is?

EXCEPT is an SQL standard that is not available in Oracle. You probably want to use the Oracle equivalent of MINUS.
See here for a good answer on the two keywords.
Edit: @a_horse_with_no_name corrected thanks!


Report rollbacks to log file There are several reasons for SQL transactions to rollback. I want to log all of them to the SQL Server Log File so that I can find information about them at a later time in the SQL Server Log File Viewer (see screenshot for example). The information about the transaction should be as detailed as possible. I want to use this information for detecting problems with a client who is losing data. I suspect the problem has somethging to do with a rollback. The problem does almost never occur so I can't use a profiler to see what happens.

The question is: how to log all rollbacks to the log file?
The platform is SQL Server 2005, but if this solution is only possible in a newer version then I would like to hear that too.

I cant offer you to dump this to the errorlog. For that you need to do some serious research and both these options are lightweight and can leave you with a simple file to look at.

In SQL Server 2005 you need to create a server side trace on transaction filtering on eventsubclass 2.

In SQL Server 2008R2 and later you are better off creating an extended event session on the
SQLSERVER.SQL.TRANSACTION event filtering on transaction_state = 2

But since you are using SQL Server 2005 you need a trace, here is a script that will create a server side trace capturing all rollback events:

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically.

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

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 50, 11, @on
exec sp_trace_setevent @TraceID, 50, 6, @on
exec sp_trace_setevent @TraceID, 50, 8, @on
exec sp_trace_setevent @TraceID, 50, 10, @on
exec sp_trace_setevent @TraceID, 50, 12, @on
exec sp_trace_setevent @TraceID, 50, 13, @on
exec sp_trace_setevent @TraceID, 50, 15, @on
exec sp_trace_setevent @TraceID, 50, 34, @on
exec sp_trace_setevent @TraceID, 50, 35, @on
exec sp_trace_setevent @TraceID, 50, 60, @on
exec sp_trace_setevent @TraceID, 50, 64, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 2
exec sp_trace_setfilter @TraceID, 21, 0, 0, @intfilter

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

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

For this to work for you, you will need to setup the trace to start with the server.

For that you need to make sure that the filename is unique each time the trace is started, create a procedure from the trace definition and make it a startup procedure.