Error ORA-12505 when trying to access a newly installed instance of oracle-11g express I encounter problems installing a local instance of oracle express on my Windows 7 - SP1 computer.
I did a basic installation, as administrator (run as 'administrator' on setup.exe).

Then I tried to connect with Oracle Sql Developer with the flollowing parameters :

Username : system
password : the one I provided during installation
hostname : localhost
role :sysdba
port : 1521 (didn't change the default one)
SID : XE.

And I always get :

  Statut : échec -Echec du test : Listener refused the connection with the following error:
  ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

My window user is in ORA_DB group, as well as the administrator user.

When I try to connect as sysdba in SQL command prompt, I got :

ORA-12638: Credential retrieval failed

I also tried to use servicename instead ('orcl'), but it failed with ORA-12514 instead.

My listener.ora looks like this:

  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
        (PROGRAM = extproc)
      )

  (SID_DESC =
        (SID_NAME = CLRExtProc)
        (ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
        (PROGRAM = extproc)
      )

  (SID_DESC =
        (SID_NAME = XE)
        (ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
      )
    )

  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        (ADDRESS = (PROTOCOL = TCP)(HOST = MACHINE0138.com.company.intra)(PORT = 1521))
      )
    )

  DEFAULT_SERVICE_LISTENER = (XE)

The result of "lsnrctl status"

  LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 08-F╔VR.-2016 10:40:01

  Copyright (c) 1991, 2014, Oracle.  All rights reserved.

  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER

  Alias                     LISTENER

  Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production

  Start Date                08-F╔VR.-2016 10:37:59

  Uptime                    0 days 0 hr. 2 min. 6 sec

  Trace Level               off

  Security                  ON: Local OS Authentication

  SNMP                      OFF

  Default Service           XE

  Listener Parameter File   D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server\network\admin\listener.ora

  Listener Log File         D:\Programmes\BDD\OracleExpress\app\oracle\diag\tnslsnr\MACHINE0138\listener\alert\log.xml

  Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MACHINE0138.com.company.intra)(PORT=1521)))

  Services Summary...

  Service "CLRExtProc" has 1 instance(s).

Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

  Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

  The command completed successfully

And eventually, here's the log.xml file after my attempt :

<msg time='2016-02-08T11:17:55.181+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MACHINE0138'
 host_addr='fe80::e958:8b9c:be6f:2610%11'>
 <txt>08-FÉVR.-2016 11:17:55 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=GLE10445))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58750)) * establish * XE * 12505
 </txt>
</msg>
<msg time='2016-02-08T11:17:55.185+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MACHINE0138'
 host_addr='fe80::e958:8b9c:be6f:2610%11'>
 <txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
 </txt>
</msg>

Any tip welcolmed, indeed.
And thanks in advance for reading until this here!

you should create at least one database user that you will use to create database objects. A database user is a type of database object: a user is associated with a database schema, you connect to the database as a database user, and the database user is the owner of any database objects (tables and so on) that you create in the schema associated with the user.

For example, to create a database user named chris(For Example). Follow these steps, using the command line:

Display the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database 11g Express Edition, and then Run SQL Command Line.

Connect as the SYSTEM user:

Type: connect
Enter user-name: system
Enter password:
Create the user. For example, enter a statement in the following form:

SQL> create user chris identified by <password-for-chris>;

Grant the user the necessary privileges. For example:

SQL> grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
  CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
  CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, -
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  to chris;

Optionally, exit SQL*Plus (which also closes the command window):

SQL> exit

SQL Developer as an Alternative for Creating Database Users:

If you have experience with SQL Developer, you can use it instead of the command line to create a database user, as follows:

Create a database connection for the SYSTEM user.

Open that database connection for the SYSTEM user.

Right-click the Other Users node in the Connections navigator under that connection.

Select Create User, and specify the necessary information. (Under System Privileges, grant ALTER SESSION, CREATE SESSION, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, and UNLIMITED TABLESPACE.)

I am also attaching the screen shot with example , which is given by Oracle BOL for SQL Developer connection.

For your ref Here And Here.


How to export an image column to files in SQL Server? I will migrate from a database. There is one column of type image that I would like to export to binary files on the file system. One file for each record. How can I do this with SQL Server?

This is the solution I came up with:

Enable xp_cmdshell with

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

If needed create a directory with xp_cmdshell to get the needed permissions.

EXEC master..xp_cmdshell 'mkdir C:\exportdir'

Use BCP with queryout

EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -N'

**your_db must be the fully qualified table name, i.e [Yourdb].[YourSchema].[YourTable]

USE [POC]
DECLARE @outPutPath varchar(50) = 'C:\Extract_Photos'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max)
, @folderPath  varchar(max)

--Get Data into temp Table variable so that we can iterate over it
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )

INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])
Select [STUDENTNO] , [STUDENTNAME],[STUDENTPHOTO] FROM  [dbo].[STUDENTPHOTOS]

--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable

WHILE @i >= 1
BEGIN

    SELECT
     @data = [Doc_Content],
     @fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName],
     @folderPath = @outPutPath + '\'+ [Doc_Num]
    FROM @Doctable WHERE id = @i

  --Create folder first
  EXEC  [dbo].[CreateFolder]  @folderPath

  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
  EXEC sp_OASetProperty @init, 'Type', 1;
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources

  print 'Document Generated at - '+  @fPath

--Reset the variables for next use
SELECT @data = NULL
, @init = NULL
, @fPath = NULL
, @folderPath = NULL
SET @i -= 1
END


What's the difference between a temp table and table variable in SQL Server? This seems to be an area with quite a few myths and conflicting views.

So what is the difference between a table variable and a local temporary table in SQL Server?

Contents

Caveat
This answer discusses "classic" table variables introduced in SQL Server 2000. SQL Server 2014 in memory OLTP introduces Memory-Optimized Table Types. Table variable instances of those are different in many respects to the ones discussed below! (more details).
Storage Location
No difference. Both are stored in tempdb.
I've seen it suggested that for table variables this is not always the case but this can be verified from the below
DECLARE @T TABLE(X INT)

INSERT INTO @T VALUES(1),(2)

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM @T

Example Results (showing location in tempdb the 2 rows are stored)
File:Page:Slot
----------------
(1:148:0)
(1:148:1)

Logical Location
@table_variables behave more as though they were part of the current database than #temp tables do. For table variables (since 2005) column collations if not specified explicitly will be that of the current database whereas for #temp tables it will use the default collation of tempdb (More details). Additionally User-defined data types and XML collections must be in tempdb to use for #temp tables but table variables can use them from the current database (Source).
SQL Server 2012 introduces contained databases. the behavior of temporary tables in these differs (h/t Aaron)

In a contained database temporary table data is collated in the collation of the contained database.

All metadata associated with temporary tables (for example, table and column names, indexes, and so on) will be in the catalog collation.
Named constraints may not be used in temporary tables.
Temporary tables may not refer to user-defined types, XML schema collections, or user-defined functions.

Visibility to different scopes
@table_variables can only be accessed within the batch and scope in which they are declared. #temp_tables are accessible within child batches (nested triggers, procedure, exec calls). #temp_tables created at the outer scope (@@NESTLEVEL=0) can span batches too as they persist until the session ends. Neither type of object can be created in a child batch and accessed in the calling scope however as discussed next (global ##temp tables can be though).
Lifetime
@table_variables are created implicitly when a batch containing a DECLARE @.. TABLE statement is executed (before any user code in that batch runs) and are dropped implicitly at the end.
Although the parser will not allow you to try and use the table variable before the DECLARE statement the implicit creation can be seen below.
IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END

--Works fine
SELECT *
FROM @T

#temp_tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends (if created in a child batch with @@NESTLEVEL > 0) or when the session ends otherwise.
NB: Within stored routines both types of object can be cached rather than repeatedly creating and dropping new tables. There are restrictions on when this caching can occur however that are possible to violate for #temp_tables but which the restrictions on @table_variables prevent anyway. The maintenance overhead for cached #temp tables is slightly greater than for table variables as illustrated here.
Object Metadata
This is essentially the same for both types of object. It is stored in the system base tables in tempdb. It is more straightforward to see for a #temp table however as  OBJECT_ID('tempdb..#T') can be used to key into the system tables and the internally generated name is more closely correlated with the name defined in the CREATE TABLE statement. For table variables the object_id function does not work and the internal name is entirely system generated with no relationship to the variable name. The below demonstrates the metadata is still there however by keying in on a (hopefully unique) column name. For tables without unique column names the object_id can be determined using DBCC PAGE as long as they are not empty.
/*Declare a table variable with some unusual options.*/
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
A INT CHECK (A > 0),
B INT DEFAULT 1,
InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
UNIQUE CLUSTERED (A,B)
    WITH (FILLFACTOR = 80,
         IGNORE_DUP_KEY = ON,
         DATA_COMPRESSION = PAGE,
         ALLOW_ROW_LOCKS=ON,
         ALLOW_PAGE_LOCKS=ON)
)

INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM   tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
         ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
         ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
         ON c.object_id = p.object_id
WHERE  c.name = 'dba.se'

Output
Duplicate key was ignored.

object_id
name
rows
type_desc
total_pages
used_pages
data_pages
data_compression_desc

574625090
#22401542
13
IN_ROW_DATA
2
2
1
PAGE

574625090
#22401542
13
LOB_DATA
24
19
0
PAGE

574625090
#22401542
13
ROW_OVERFLOW_DATA
16
14
0
PAGE

574625090
#22401542
13
IN_ROW_DATA
2
2
1
NONE

Transactions
Operations on @table_variables are carried out as system transactions, independent of any outer user transaction, whereas the equivalent #temp table operations would be carried out as part of the user transaction itself. For this reason a ROLLBACK command will affect a #temp table but leave the @table_variable untouched.
DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT * FROM #T
SELECT * FROM @T
DROP TABLE #T

Logging
Both generate log records to the tempdb transaction log. A common misconception is that this is not the case for table variables so a script demonstrating this is below, it declares a table variable, adds a couple of rows then updates them and deletes them.
Because the table variable is created and dropped implicitly at the start and the end of the batch it is necessary to use multiple batches in order to see the full logging.
USE tempdb;

/*
Don't run this on a busy server.
Ideally should be no concurrent activity at all
*/
CHECKPOINT;

GO

/*
The 2nd column is binary to allow easier correlation with log output shown later*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10))

INSERT INTO @T
VALUES (1, 0x41414141414141414141),
       (2, 0x41414141414141414141)

UPDATE @T
SET    B = 0x42424242424242424242

DELETE FROM @T

/*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
DECLARE @allocId BIGINT, @Context_Info VARBINARY(128)

SELECT @Context_Info = allocation_unit_id,
       @allocId = a.allocation_unit_id
FROM   sys.system_internals_allocation_units a
       INNER JOIN sys.partitions p
         ON p.hobt_id = a.container_id
       INNER JOIN sys.columns c
         ON c.object_id = p.object_id
WHERE  ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )

SET CONTEXT_INFO @Context_Info

/*Check log for records related to modifications of table variable itself*/
SELECT Operation,
       Context,
       AllocUnitName,
       [RowLog Contents 0],
       [Log Record Length]
FROM   fn_dblog(NULL, NULL)
WHERE  AllocUnitId = @allocId

GO

/*Check total log usage including updates against system tables*/
DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));

WITH T
     AS (SELECT Operation,
                Context,
                CASE
                  WHEN AllocUnitId = @allocId THEN 'Table Variable'
                  WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
                  ELSE AllocUnitName
                END AS AllocUnitName,
                [Log Record Length]
         FROM   fn_dblog(NULL, NULL) AS D)
SELECT Operation = CASE
                     WHEN GROUPING(Operation) = 1 THEN 'Total'
                     ELSE Operation
                   END,
       Context,
       AllocUnitName,
       [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0),
       Cnt = COUNT(*)
FROM   T
GROUP  BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) )
ORDER  BY GROUPING(Operation),
          AllocUnitName

Returns
###Detailed view

###Summary View (includes logging for implicit drop and system base tables)

As far as I've been able to discern operations on both generate roughly equal amounts of logging.
Whilst the quantity of logging is very similar one important difference is that log records related to #temp tables can not be cleared out until any containing user transaction finishes so a long running transaction that at some point writes to #temp tables will prevent log truncation in tempdb whereas the autonomous transactions spawned for table variables do not.
Table variables do not support TRUNCATE so can be at a logging disadvantage when the requirement is to remove all rows from a table (though for very small tables DELETE can work out better anyway)
Cardinality
Many of the execution plans involving table variables will show a single row estimated as the output from them. Inspecting the table variable properties shows that SQL Server believes the table variable has zero rows (Why it estimates 1 row will be emitted from a zero row table is explained by @Paul White here).
However the results shown in the previous section do show an accurate rows count in sys.partitions. The issue is that on most occasions the statements referencing table variables are compiled while the table is empty. If the statement is (re)compiled after @table_variable is populated then this will be used for the table cardinality instead (This might happen due to an explicit recompile or perhaps because the statement also references another object that causes a deferred compile or a recompile.)
DECLARE @T TABLE(I INT);

INSERT INTO @T VALUES(1),(2),(3),(4),(5)

CREATE TABLE #T(I INT)

/*Reference to #T means this statement is subject to deferred compile*/
SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T)

DROP TABLE #T

Plan shows accurate estimated row count following deferred compile.

In SQL Server 2012 SP2, trace flag 2453 is introduced. More details are under "Relational Engine" here.
When this trace flag is enabled it can cause automatic recompiles to take account of changed cardinality as discussed further very shortly.
NB: On Azure in compatibility level 150 compilation of the statement is now deferred until first execution. This means that it will no longer be subject to the zero row estimate problem.
No column statistics
Having a more accurate table cardinality doesn't mean the estimated row count will be any more accurate however (unless doing an operation on all rows in the table). SQL Server does not maintain column statistics for table variables at all so will fall back on guesses based upon the comparison predicate (e.g. that 10% of the table will be returned for an = against a non unique column or 30% for a > comparison). In contrast column statistics are maintained for #temp tables.
SQL Server maintains a count of the number of modifications made to each column. If the number of modifications since the plan was compiled exceeds the recompilation threshold (RT) then the plan will be recompiled and statistics updated. The RT depends on table type and size.
From Plan Caching in SQL Server 2008

RT is calculated as follows. (n refers to a table's cardinality when a query plan is compiled.)

Permanent table

If n <= 500, RT = 500.
If n > 500, RT = 500 + 0.20 * n.

Temporary table

If n < 6, RT = 6.
If 6 <= n <= 500, RT = 500.
If n > 500, RT = 500 + 0.20 * n.
Table variable
RT does not exist. Therefore, recompilations do not happen because of changes in  cardinalities of table variables.
(But see note about TF 2453 below)

the KEEP PLAN hint can be used to set the RT for #temp tables the same as for permanent tables.
The net effect of all this is that often the execution plans generated for #temp tables are orders of magnitudes better than for @table_variables when many rows are involved as SQL Server has better information to work with.
NB1: Table variables do not have statistics but can still incur a "Statistics Changed" recompile event under trace flag 2453 (does not apply for "trivial" plans) This appears to occur under the same recompile thresholds as shown for temp tables above with an additional one that if N=0 -> RT = 1. i.e. all statements compiled when the table variable is empty will end up getting a recompile and corrected TableCardinality the first time they are executed when non empty. The compile time table cardinality is stored in the plan and if the statement is executed again with the same cardinality (either due to flow of control statements or reuse of a cached plan) no recompile occurs.
NB2: For cached temporary tables in stored procedures the recompilation story is much more complicated than described above. See Temporary Tables in Stored Procedures for all the gory details.
Recompiles
As well as the modification based recompiles described above #temp tables can also be associated with additional compiles simply because they allow operations that are prohibited for table variables that trigger a compile (e.g. DDL changes CREATE INDEX, ALTER TABLE)
Locking
It has been stated that table variables do not participate in locking. This is not the case. Running the below outputs to the SSMS messages tab the details of locks taken and released for an insert statement.
DECLARE @tv_target TABLE (c11 int, c22 char(100))

DBCC TRACEON(1200,-1,3604)

INSERT INTO @tv_target (c11, c22)

VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100))

DBCC TRACEOFF(1200,-1,3604)

For queries that SELECT from table variables Paul White points out in the comments that these automatically come with an implicit NOLOCK hint. This is shown below
DECLARE @T TABLE(X INT);

SELECT X
FROM @T
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)

###Output
*** Output Tree: (trivial plan) ***

        PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002  Hints( NOLOCK )

The impact of this on locking might be quite minor however.
SET NOCOUNT ON;

CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL,
                 [Filler] [char](8000) NULL,
                 PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL,
                 [Filler] [char](8000) NULL,
                 PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @I INT = 0

WHILE (@I < 10000)
BEGIN
INSERT INTO #T DEFAULT VALUES
INSERT INTO @T DEFAULT VALUES
SET @I += 1
END

/*Run once so compilation output doesn't appear in lock output*/
EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEON(1200,3604,-1)
SELECT *, sys.fn_PhysLocFormatter(%%physloc%%)
FROM @T

PRINT '--*--'

EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEOFF(1200,3604,-1)

DROP TABLE #T

Neither of these return results in index key order indicating that SQL Server used an allocation ordered scan for both.
I ran the above script twice and the results for the second run are below
Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0  (class bit0 ref1) result: OK

--*--
Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0  (class bit0 ref1) result: OK

Process 58 acquiring S lock on OBJECT: 2:-1293893996:0  (class bit0 ref1) result: OK

Process 58 releasing lock on OBJECT: 2:-1293893996:0

The locking output for the table variable is indeed extremely minimal as SQL Server just acquires a schema stability lock on the object. But for a #temp table it is nearly as light in that it takes out an object level S lock. A NOLOCK hint or READ UNCOMMITTED isolation level can of course be specified explicitly when working with #temp tables as well.
Similarly to the issue with logging a surrounding user transaction can mean that the locks are held longer for #temp tables. With the script below
    --BEGIN TRAN;

    CREATE TABLE #T (X INT,Y CHAR(4000) NULL);

    INSERT INTO #T (X) VALUES(1)

    SELECT CASE resource_type
             WHEN  'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2)
             WHEN  'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2)
                                           FROM  tempdb.sys.allocation_units a
                                           JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
                                           WHERE  a.allocation_unit_id = resource_associated_entity_id)
             WHEN 'DATABASE' THEN DB_NAME(resource_database_id)
             ELSE (SELECT OBJECT_NAME(object_id, 2)
                   FROM   tempdb.sys.partitions
                   WHERE  partition_id = resource_associated_entity_id)
           END AS object_name,
           *
    FROM   sys.dm_tran_locks
    WHERE  request_session_id = @@SPID

    DROP TABLE #T

   -- ROLLBACK

when run outside of an explicit user transaction for both cases the only lock returned when checking sys.dm_tran_locks is a shared lock on the DATABASE.
On uncommenting the BEGIN TRAN ... ROLLBACK 26 rows are returned showing that locks are held both on the object itself and on system table rows to allow for rollback and prevent other transactions from reading uncommitted data. The equivalent table variable operation is not subject to rollback with the user transaction and has no need to hold these locks for us to check in the next statement but tracing locks acquired and released in Profiler or using trace flag 1200 shows plenty of locking events do still occur.
Indexes
For versions prior to SQL Server 2014 indexes can only be created implicitly on table variables as a side effect of adding a unique constraint or primary key. This does of course mean that only unique indexes are supported. A non unique non clustered index on a table with a unique clustered index can be simulated however by simply declaring it UNIQUE NONCLUSTERED and adding the CI key to the end of the desired NCI key (SQL Server would do this behind the scenes anyway even if a non unique NCI could be specified)
As demonstrated earlier various index_options can be specified in the constraint declaration including DATA_COMPRESSION, IGNORE_DUP_KEY, and FILLFACTOR (though there is no point in setting that one as it would only make any difference on index rebuild and you can't rebuild indexes on table variables!)
Additionally table variables do not support INCLUDEd columns, filtered indexes (until 2016) or partitioning, #temp tables do (the partition scheme must be created in tempdb).
Indexes in SQL Server 2014
Non unique indexes can be declared inline in the table variable definition in SQL Server 2014. Example syntax for this is below.
DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
C2 INT INDEX IX2 NONCLUSTERED,
       INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
);

Indexes in SQL Server 2016
From CTP 3.1 it is now possible to declare filtered indexes for table variables. By RTM it may be the case that included columns are also allowed albeit they will likely not make it into SQL16 due to resource constraints
DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)

Parallelism
Queries that insert into (or otherwise modify) @table_variables cannot have a parallel plan, #temp_tables are not restricted in this manner.
There is an apparent workaround in that rewriting as follows does allow the SELECT part to take place in parallel but that ends up using a hidden temporary table (behind the scenes)
INSERT INTO @DATA ( ... )
EXEC('SELECT .. FROM ...')

There is no such limitation in queries that select from table variables as illustrated in my answer here
Other Functional Differences

#temp_tables cannot be used inside a function. @table_variables can be used inside scalar or multi-statement table UDFs.
@table_variables cannot have named constraints.
@table_variables cannot be SELECT-ed INTO, ALTER-ed, TRUNCATEd or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT and do not support table hints such as WITH (FORCESCAN)
CHECK constraints on table variables are not considered by the optimizer for simplification, implied predicates or contradiction detection.
Table variables don't seem to qualify for the rowset sharing optimisation meaning that delete and update plans against these can encounter more overhead and PAGELATCH_EX waits. (Example)

Memory Only?
As stated at the beginning both get stored on pages in tempdb. However I didn't address whether there was any difference in behaviour when it comes to writing these pages to disc.
I've done a small amount of testing on this now and so far have seen no such difference. In the specific test I did on my instance of SQL Server 250 pages seems to be the cut off point before the data file gets written to.

NB: The behavior below no longer occurs in SQL Server 2014 or SQL
Server 2012 SP1/CU10 or SP2/CU1 the eager writer is no longer as
eager to write pages to disc. More details on that change at SQL
Server 2014: tempdb Hidden Performance Gem.

Running the below script
CREATE TABLE #T(X INT, Filler char(8000) NULL)
INSERT INTO #T(X)
SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values
DROP TABLE #T

And monitoring writes to the tempdb data file with Process Monitor I saw none (except occasionally ones to the database boot page at offset 73,728). After changing 250 to 251I began to see writes as below.

The screenshot above shows 5 * 32 page writes and one single page write indicating that 161 of the pages were written to disc. I got the same cut off point of 250 pages when testing with table variables too. The script below shows it a different way by looking at sys.dm_os_buffer_descriptors
DECLARE @T TABLE (
  X        INT,
  [dba.se] CHAR(8000) NULL)

INSERT INTO @T
            (X)
SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0))
FROM   master..spt_values

SELECT is_modified,
       Count(*) AS page_count
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = 2
       AND allocation_unit_id = (SELECT a.allocation_unit_id
                                 FROM   tempdb.sys.partitions AS p
                               INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                                          ON p.hobt_id = a.container_id
                                        INNER JOIN tempdb.sys.columns AS c
                                          ON c.object_id = p.object_id
                                 WHERE  c.name = 'dba.se')
GROUP  BY is_modified

###Results
is_modified page_count
----------- -----------
0           192
1           61

Showing that 192 pages were written to disc and the dirty flag cleared. It also shows that being written to disc doesn't mean that pages will be evicted from the buffer pool immediately. The queries against this table variable could still be satisfied entirely from memory.
On an idle server with max server memory set to 2000 MB and DBCC MEMORYSTATUS reporting Buffer Pool Pages Allocated as approx 1,843,000 KB (c. 23,000 pages) I inserted to the tables above in batches of 1,000 rows/pages and for each iteration recorded.
SELECT Count(*)
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = 2
       AND allocation_unit_id = @allocId
       AND page_type = 'DATA_PAGE'

Both the table variable and the #temp table gave nearly identical graphs and managed to pretty much max out the buffer pool before getting to the point that they weren't entirely held in memory so there doesn't seem to be any particular limitation on how much memory either can consume.

There are a few things I'd like to point out based more on particular experiences rather than study. As a DBA, I'm very new so please do correct me where required.

#temp tables by default use the SQL Server instance's default collation. So unless otherwise specified, you might run into problems comparing or updating values between #temp tables and database tables, if the masterdb has a different collation from the database. See: http://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/
Completely based on personal experience, the available memory seems to have an effect on which performs better. MSDN recommends using table variables for storing smaller result sets, but most of the time the difference isn't even noticeable. In larger sets however, it's become obvious in some cases that table variables are far more memory intensive and can slow the query down to a crawl.


Expressing some queries without employing JOIN or OUTER JOIN Assume the following relations schemas:

S(A,B)
T(B,C)

Rewrite the following expressions without using join or outer join operators explicitly:

S LEFT OUTER JOIN T
S FULL OUTER JOIN T

How do I do these two questions? I've tried using a where clause but I don't think it is correct. I know the first one has to use a union but how do I go about writing the queries?

Left outer join will be:

select s.a, s.b, (select c from t where s.b =t.b) as outercol
from s

Full outer join will be:

select s.a, s.b, (select c from t where s.b =t.b) as outercol
from s
union all
select null, b, c from t
where b not in (select b from s)


SQL Server Management Send Mail as Part of a Job I have a SQL Job that I would like to send an email through, but for some reason I can't get any details on why the email is not sending. This code works as expected when executed in the query window, and SQL Server Agent will also send error emails to me, so my email profile seems to be set up correctly. The code:
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfile',
@recipients = 'myaccount@emailcom',
@subject = 'Bad Data Report',
@query = N' USE Database;
   DECLARE @yesterday date = getdate() - 1;  select * from Timecards WHERE Date = @yesterday AND (DailyHours > 24.1 OR DailyHours < 0 ) ',
@exclude_query_output = 1,
@attach_query_result_as_file = 1,
@query_result_header = 1,
@query_attachment_filename = 'BadDataQuery.txt'
END

Following some advice from similar issues on this site, I tried looking through sysmail_allitems and sysmail_faileditems, but neither log any attempts to send. I also created a stored procedure that I had the job call as a roundabout way to send the email, but that also did not send an email nor leave an error. Any ideas what might be going on?

Here are some steps to troubleshoot the situation. After executing them you're probably be able to identify what's causing your problem. Execute one of them at a time to eliminate the possible problems:

Replace @attach_query_result_as_file = 1, for
@attach_query_result_as_file = 'Danchat',. The idea is to cause an
error to see if causes the job to fail or if it goes unnoticed too;
Clean the job step and leave only a simple mail command like this:
EXEC msdb.dbo.sp_send_dbmail    @profile_name = 'MailProfile', @recipients = 'myaccount@emailcom',     @subject = 'Test'; and try to
run the job. Don't use begin/end or anything else on the step, just a
simple code like that;
To find if the problem comes from the parameter of the @query
variable run the complete code of your question on the job, but use
this @query = N'SELECT 1;' instead of your original query. If the
email arrives, it means you only have to correct that internal query
of yours, if the email is not sent, you should start removing the
other options until the email is sent to find the culprit.
Try using this: @execute_query_database = 'YourDatabase',   @query = N'select * from Timecards WHERE Date >= ''20210418 00:00:00.000'' AND Date < ''20210419 00:00:00.000'' AND (DailyHours > 24.1 OR DailyHours < 0 )', assuming this interval contains any data. If not,
query your table for an interval that has at least one row to see if
it works properly.
Finally, if the mail is not sent only the moment you specify a table
on your query, it's possible the problem is related to privileges
(even though you are using an admin account - maybe some change in
context is happening). Try configuring an extended event session to
capture requests and you will be able to verify that.

Solution:
From chat discussion we were able to figure out the problem was in fact a permission problem and he was able to capture the actual user running the internal query following my last suggestion about an XE Session.

please verify if your database mail service is properly configured and check it with the "send test email..." and look into the log for detalis. Usually the mail server has to be configured to accept relay from your sql.


Any better way to rewrite the following "create table"? I have the following table:

CREATE TABLE Employee
(
    UID VARCHAR(10) NOT NULL,
    Name VARCHAR(20) NOT NULL,
    Type ENUM('Class_A', 'Class_B', 'Class_C') NOT NULL,
    Class_A_Speed ENUM('Fast', 'Medium', 'Slow') DEFAULT NULL

    PRIMARY KEY (UID)
);

Type defines three categories of employee (think departments). For "Class A" employees, we further categorize them based on how fast they work.

My concern is that, for all "Class A" employees, they should have a non-NULL speed; the other two classes should have Class_A_Speed set to NULL.

I can add constraints but I rather avoid it. I am trying to learn and this design seems very hacky to me...

Even on MySQL, you don't need to specify DEFAULT null. That's always the default, well usually always because MySQL is a special monster.

Use PostgreSQL.
UID should be a number, not varchar.
Class_A is pretty silly, even in an enum. If your classes are all one letter, just make them a char(1) or the like and just add "type" to the column name.
Class_A_Speed sounds like a function over a real speed, rather than a category. Just add the function to the database, class_from_speed(speed)

Like this,

CREATE TABLE employee (
  employee_id   int       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name          text,
  class_type    "char",   -- or char(1)
  speed         int
);


Is there a means to set the owner of all objects in a PostgreSQL database at the same time? https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ignore the functions I created.

Is there a fairly easy way to reset the owner of ALL objects in the database, including the functions? Doing it by hand is highly undesirable.

You should only ever manipulate system catalogs directly, if you know exactly what you are doing. It may have unexpected side effects. Or you can corrupt the database (or the whole database cluster) beyond repair.

Jeremy's answer, while basically doing the trick, is not advisable for the general public. It unconditionally changes all functions in a schema. Are you sure there are no system functions affected or functions installed by an additional module?
It would also be pointless to change the owner of functions that already belong to the designated owner.

First, check if REASSIGN OWNED could work for you:

  change the ownership of database objects owned by a database role

You have to list all roles to be disowned explicitly. But it also reassigns functions.

To assign all functions (and no other objects) in a given schema to a new owner (optionally regardless of previous owner):

SELECT string_agg('ALTER FUNCTION ' || oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public';
-- AND p.relowner <> (SELECT oid FROM pg_roles WHERE rolname = 'foo')
-- AND p.proname ~~ 'f_%'

This generates the canonical SQL commands ALTER FUNCTION ... to change all functions (in the specified schema). You can inspect the commands before executing them - one by one or all at once:

ALTER FUNCTION public.bar(text, text) OWNER TO foo;
ALTER FUNCTION public.foo(x integer) OWNER TO foo;
...

I included some commented WHERE clauses you might want to use to filter the results.

The cast to regprocedure produces a valid function name with parameters, double-quoted where necessary, schema - qualified where necessary for the current search_path.

The aggregate function string_agg() requires PostgreSQL 9.0 or later. In older version substitute with array_agg() and array_to_string().

You could put all of this into a DO statement or a function like demonstrated in this related answer:

Drop all functions in a namespace? (Execute generated DDL commands?)

In Postgres 9.5 or later, you can simplify the query using new object identifier types regnamespace and regrole:

SELECT string_agg('ALTER FUNCTION '|| oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc
WHERE  pronamespace = 'public'::regnamespace;
-- AND relowner <> 'foo'::regrole
-- AND proname ~~ 'f_%'

Well, I didn't find a one-step process, but this takes care of all the objects I can see in my database:

update pg_class
SET relowner = (SELECT oid FROM pg_roles WHERE rolname = 'foo')
where relnamespace = (select oid
                      from pg_namespace
                      where nspname = 'public'
                      limit 1);

update pg_proc
set proowner = (select oid from pg_roles where rolname = 'foo')
where pronamespace = (select oid
                      from pg_namespace
                      where nspname = 'public'
                      limit 1);


Can't login ORA-28031: maximum of 148 enabled roles exceeded I can't login - and receive the error code above. How do I fix this?

to let the user connect you need revoke some roles from the user

see discussion about here: AskTom

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1565923300346644029

the discussion about parameter MAX_ENABLED_ROLES that used to limit max count of roles to 150 roles. the parameter is marked as deprecated but as the discussion says it still had some value for limit

  It is deprecated because they are currently maxing it out (just like
  maxtrans is deprecated - but still "exists", it is 255 no matter what
  you set it to)
  MAX_ENABLED_ROLES

Range of values 0 to 148

  deprecated

  MAX_ENABLED_ROLES specifies the maximum number of database roles that
  users can enable, including roles contained within other roles. The
  actual number of roles that users can enable is 2 plus the value of
  MAX_ENABLED_ROLES, because each user has two additional roles, PUBLIC
  and the user's own role.

First, login as

SYSTEM@databasename AS SYSDBA

Then

SELECT * FROM DBA_ROLES;

Then for each role you don't want...

DROP ROLE <rolename>;

To drop roles.

If you have some pattern to the offending roles, you can use the following anonymous loop to clean those up. Imagine you had 50 roles with the word GILES in them which were generated from some erroneous SQL routine:

BEGIN
  FOR c IN ( SELECT ROLE FROM DBA_ROLES WHERE ROLE LIKE '%GILES%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP ROLE ' || c.ROLE;
  END LOOP;
END;
/

Don't ask me how to re-architect your roles, I'm no expert. I just had the same issue where some automated tests were creating roles but not dropping them.

I believe you can't alter the limit so discussions about the limit are pointless.

As Justin_Cave mentions, you can also revoke roles from users to deactivate them or make the role 'non default' which effectively reduces the active role count, and allows you to retain the role for future use.


Calculating the optimal size for the oracle undo tablepsace To size the undo tablespace I query v$UNDOSTAT

SELECT BEGIN_TIME, END_TIME, UNDOBLKS, MAXQUERYLEN from v$UNDOSTAT;

BEGIN_TIME          END_TIME              UNDOBLKS MAXQUERYLEN
------------------- ------------------- ---------- -----------
14.10.2016 10:38:57 14.10.2016 10:48:13        911         244
14.10.2016 10:28:57 14.10.2016 10:38:57        977        1120
14.10.2016 10:18:57 14.10.2016 10:28:57       1031        2266
14.10.2016 10:08:57 14.10.2016 10:18:57       1018        2365 <--

To find out the blocksize of the undo tablespace, I query

SELECT TO_NUMBER(value) DB_BLOCK_SIZE FROM v$parameter WHERE name = 'db_block_size';

DB_BLOCK_SIZE
-------------
         8192

Now I can calculate the undo tablespace size. I have to divide the UNDOBLKS by 600 to get the rate of undo generation in blocks per second, and multiply by the blocksize to get the figure in bytes. Multiply by the largest figure for MAXQUERYLEN to find the space needed if the highest rate of undo generates coincided with the longest query and then divide by a billion to get the answer in gigabytes

1018/600 * 8192*2365=328713557,33
328713557,33/1000000000=0.3287GB

Is this a correct calculation? Should I consider other metrics to calcualte the size of the undo tablepsace?

You may use the calculation mentioned in this site:

https://taliphakanozturken.wordpress.com/tag/actual-undo-size/


If using a function in a case, does it run on each condition and result or is cached? In this example:
select case when someFunction() is null or someFunction() = 0 then 999 else someFunction() end

someFunction() runs three times or just one?

It runs every time you call it, in your case three times.
So rewrite the function that makes the case superfluous.


Applying Quotes Across Multiple Lines In SQL Server Management Studio you can use the shortcut CTRL + K > C or CTRL + K > U to comment/uncomment lines in bulk.

Is there a similar keyboard function that applies single quotes across a list of values? I have 150 IDs that I have copied from an Excel spreadsheet and I would like to execute a query that uses an IN clause on this list, and I dont want to manually add single quotes and comma around each item.

For example:

SELECT * FROM tbl WHERE someValue IN (
ABC1
ABC2
ABC3
ABC4
)

I want to highlight the values and using a keyboard shortcut turn the list into:

SELECT * FROM tbl WHERE someValue IN (
'ABC1',
'ABC2',
'ABC3',
'ABC4'
)

Native tooling approach

Grab a newer copy of Management Studio (2012 SP2 and 2014 are both free, fully functional, and can co-exist with your 2008 R2 tools). Then you can do this.

Put your cursor right before the first leading ABC.
Hold Shift+Alt, then hit the down arrow three times.
Type '.

It's subtle, but you should see a faint blue vertical line here indicating that keystrokes will actually affect all 4 lines (or 150 lines).

Excel approach

If you don't want to use a more modern version of Management Studio, then just add the stuff in Excel. Insert a new column, put a single-quote in the first cell where you want it, hover over the bottom right of the cell until the cursor becomes a solid +, then click and drag to the bottom:

Repeat for the second single-quote, commas, etc. Copy all, paste into SSMS, then do a quick replace for '<tab>, <tab>',, etc.

Regular expression approach

Your third alternative is to use a Regular Expression, valid with all versions of SSMS

Find what: {.+}
Replace with: '\1',
Look in: Selection
Expand Find Option
Use: Regular expression (checked)

That regular expression indicates find everything and remember what we found
Replace everything we found \1 by wrapping it with with tic marks and a comma

If you have more complex requirements, the right chevron next to the drop down arrow on Find what lists the regular expression dialect SSMS/Visual Studio understands

It is very simple in any database:

For example, let's assume that your values are as below.

abc
wrt
hgt
ngl

place your cursor on a of abc and press Shift+Alt
then press the ↓ arrow three times while holding Shift+Alt.

You will see a blue line came up.
keep pressing ↓ arrow till you get to the value you want to update and release Shift+Alt
then press quote, comma or whatever you want to place there instead of all values

Similarly, place the cursor after the c of abc and apply the above process.

Now the output for the above with quotes and comma should be

'abc',
'wrt',
'hgt',
'ngl',


grant access to user 'root' to all databases, but least specific one I've got the users: root, lmanager.
And too, a few databases: db1, db2, db3,...
I want to grant all privileges to user 'root' over all databases, except for the db2.
The user 'lmanager' will get all permissions only to db2.

It is possible to make it work?
it is possible to make db2 'invisible' to user root?
If it's possible, how i can do it?
Thanks in advance!

Unfortunately, you must do lots of these:

GRANT ALL ON db1.* TO ...;
GRANT ALL ON db3.* TO ...;  -- skipping db2
...

The syntax of GRANT/REVOKE is too clumsy to get what you want in any easier way.

You could write a query something like this to generate them:

SELECT
       CONCAT('GRANT ALL ON ',
              SCHEMA_NAME,
              '.* TO ...;')
    FROM information_schema.SCHEMATA
    WHERE SCHEMA_NAME NOT IN ('db2', 'mysql');

Then manually execute them.

Precede that by doing a GRANT USAGE TO ... to provide the password.

You should be able to do this by having a row in your mysql.user table permissioning root as having no permissions on db2, this row occurring before the row that grants it default full permissions on everything.  (The permission tables are processed in order.)  You may need to dump mysql.user to a text file and reconstruct it to achieve this.


mysql trigger not working with if statement CREATE TRIGGER updateNewEmp
AFTER UPDATE ON BasicDetails
FOR EACH ROW  BEGIN
    if(NEW.MiddleName<>OLD.MiddleName) then
      insert into BasicDetails_audit(MiddleName,`State`,Country,Community)
      VALUES(NEW.MiddleName,NEW.`State`,NEW.Country,NEW.Community)
end if;
end

It throws a syntax error near the END IF statement. Can anyone please help?

You have to change DELIMITER to something else than ;

DELIMITER $$
CREATE TRIGGER updateNewEmp AFTER UPDATE ON BasicDetails FOR EACH ROW
BEGIN
  IF(NEW.MiddleName<>OLD.MiddleName)
  THEN
    INSERT INTO BasicDetails_audit(MiddleName,`State`,Country,Community)
         VALUES (NEW.MiddleName,NEW.`State`,NEW.Country,NEW.Community);
  END IF;
END$$
DELIMITER ;


Will auto vacuum analyzer delay visibility of bulk insertion or updation I am reading records from table A and doing some calculations and inserting and updating the table B. I am doing yearly migration from 2017 to now. For2017-2019 I am using different query(Function  A) and 2020 jan to now different query(Function  B). My migration has started at 9:57 and all the migration are over by 16:37. I am looping the year and months through service. Both the function s returning true to the service and I couldn’t find and bugs in postgres logs as well.But there are no inserted records in the table B. But auto-vacuum analyzer is running even more than 12 hours after the migration over which is showing background started time as  9:58 which is my migration started time. Someone please advise me what could be the reason and how can I solve it. Since I am new bee to the postgres and I am a software engineer not a DBA not sure how can I solve.

The reason is synchronous commit. Postgres has introduced this to increase the performance. The client gets results before actually it got committed. That’s why table did not get updated , getting time to update. I was checking before it get updated
Source: https://www.mydatahack.com/top-10-configuration-parameters-to-tune-postgresql-for-better-performance/


MySQL Backup Strategy We are running the backup from the replication server and it is working good, since we are not facing any other issues, some information for better understanding our strategy:

Backup Type: Full Backup
Server : MySQL 5.5
Backup Tool: mysqldump
Interval: Every 24 hours.
Data size at present : 2 TB.

Actually we are in need to store the back up of 13 months but we have only 2 months. The data is growing around 6% in every month. We are running out of Storage.

Recently, we thought of moving into mysql incremental backup. The decided plan is we need to run full backup on every week interval and incremental backup at every 24 hours.

Our main concern is storage, since our data is growing every month, I need to know the answer for following questions.

What is the best possible solution for backup and storing the data considering our situation?
Whether the incremental backup strategy will solve our storage issue?
Is there any other better way to handle this situation?

Any suggestions welcome, thank you in advance.

2 TB sounds too much for mysqldump. Did you try to restore a database from the dump? How much time does it take?

For large databases I would suggest XtraBackup. Not only it's faster, especially when it comes to restoring but it also supports incremental backups.

Weekly full copies sounds reasonable. For daily backups you have two options:

Incremental copy containing changes since last day.
Incremental copy containing changes since last full copy (sometimes this type is called differential backups). I prefer this one because of smaller restore time and higher chance to successfully restore the database.

If you need to store 13 month of data then you'd probably need to store 13 full copies. In that case storage requirements would be similar.

What you can do is to take full copy at month zero and then take a differential copy every moth. Then assuming 2TB initial size and 6% monthly growth the total storage size would be ~4TB.

Month   Database size, TB   Delta, TB
1   1.00    0.00
2   1.06    0.06
3   1.12    0.06
4   1.19    0.07
5   1.26    0.07
6   1.34    0.08
7   1.42    0.08
8   1.50    0.09
9   1.59    0.09
10  1.69    0.10
11  1.79    0.10
12  1.90    0.11
13  2.01    0.11
    Total delta, TB 1.01

Do you need daily backups?  Why not keep the latest few days, and the latest few weeks, and only monthly before that?


Can I create a user defined table type and use it in the same transaction? When I execute the following (in management studio, GO will separate the commands into batches)

use tempdb

begin tran
go

CREATE TYPE dbo.IntIntSet AS TABLE(
    Value0 Int NOT NULL,
    Value1 Int NOT NULL
)
go

declare @myPK dbo.IntIntSet;
go

rollback

I get a deadlock error message. My process has deadlocked with itself. I've seen this behaviour in 2008, 2008R2 and 2012.

Is there a way to use my newly created type inside the same transaction it was created?

This has been reported no less than four times (but all traces have been removed from the WayBack Machine since Connect was murdered). This one was closed as fixed:

connect.microsoft.com/SQLServer/feedback/details/365876/

But that wasn't true. (Also look at the workarounds section - the workaround I suggested is not always going to be acceptable.)
This one was closed as by design / won't fix:

connect.microsoft.com/SQLServer/feedback/details/581193/

These two are newer and still active:

connect.microsoft.com/SQLServer/feedback/details/800919/ (now closed as Won't Fix)
connect.microsoft.com/SQLServer/feedback/details/804365/ (now closed as By Design)

Until Microsoft can be convinced otherwise, you're going to have to find a workaround - just have all the types deployed before running your test, or break it up into multiple tests.
I will try to get confirmation from my contacts about what Umachandar meant by fixed in the earliest item, because obviously that conflicts with later statements.
UPDATE #1 (of, hopefully, exactly 2)
The original bug (that was closed as fixed) involved alias types, but not of type TABLE. It was reported against SQL Server 2005, which obviously didn't have table types and TVPs. It seems UC reported that the bug with non-table alias types was fixed based on how they handle internal transactions, but it did not cover a similar scenario later introduced with table types. I am still waiting on confirmation of whether that original bug should have ever been closed as fixed; I have suggested that all four be closed as by design. This is partly because it is kind of how I expected it to work, and partly because I get the sense from UC that "fixing" it to work in a different way is extremely complex, could break backward compatibility, and would be helpful in a very limited number of use cases. Nothing against you or your use case, but outside of test scenarios I'm not inclined to believe there is much value in this actually working.
UPDATE #2
I've blogged about this issue:
http://www.sqlperformance.com/2013/11/t-sql-queries/single-tx-deadlock

Unless someone knows different, I don't think there's a way to do this in a single transaction. I don't think this is a bug.

First, you need to take a schema modification lock (Sch-M) when you create the type. Since you don't commit the transaction, the lock stays open. Then you try to declare a variable to that type in the same transaction. This tries to take a Schema Stability lock (Sch-S).  Those two types are incompatible simultaneously on the same object. Since they're in the same transaction, SQL treats it as a deadlock because the Sch-S can never be granted while the transaction is open.

Run each batch one at a time, and select against sys.dm_tran_locks as soon as you try to declare the variable. You'll see the same process holding an Sch-M and waiting for an Sch-S on the same object.


How to get "mysqlbinlog" utility for Ubuntu? I can't find "mysqlbinlog" utility in my Ubuntu server, i can see mysql, mysqldump, mysqlshow etc... command line utilities but not mysqlbinlog utility.

Can someone please help me from where I can get this utility and install it?

mysqlbinlog utility is in mysql server package. You should try install mysql server.

mysqlbinlog is an impded utility inside MySQL server try to install MySQL server by executing the following command

sudo apt-get install mysql-server


How to fix a messed up replication on MS SQL Server I restored a database from a backup. The database uses replication to publish to a different server. Assuming the database restore would break the replication, I tried to delete the replication and re-create it (we have a script to re-create it from scratch). I'm not sure exactly what I did, but now it is in a completely messed up state and I can't fix it.

First, I try to get rid of the subscription (on the publisher server):

EXEC sp_dropsubscription @publication = 'PublicationName', @article = N'all', @subscriber = 'SubscriberServerName'

This seems to work. SELECT * FROM syssubscriptions shows no results. Looking on the subscriber server, SSMS > {SubscriberServer} > Replication > Local Subscriptions - the subscription is not there.

So then I try to delete the publication. SSMS > {Server} > Replication > Local Publications > {PublicationName} > Delete. This gives the following error message:

Could not delete publication 'PublicationName'.
Could not drop article. A subscription exists on it.
Changed database context to 'DatabaseName'. (Microsoft SQL Server, Error: 14046)

Ok, so I try to drop the articles:

EXEC sp_droparticle @publication = 'PublicationName', @article = N'all'

and get this error:

Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot.
Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75
Could not drop article. A subscription exists on it.

Ok, so I try starting the Snapshot Agent and I get this internal SQL exception:

The SQL command 'sp_MSactivate_auto_sub' had returned fewer rows than expected by the replication agent.

So I tried an alternative method of deleting the article, DELETE FROM sysarticles. This seems to have worked - I have now got rid of the articles, but I still get the same 'Cannot drop the publication because at least one subscription exists for this publication' error when I try to delete the publication.

I have also restarted SQL Server - didn't help.

I don't know what is going on here and how do I fix it?

BTW this is what happens when you give a software developer who knows just enough to be dangerous the keys to the database. Fortunately, this isn't a production environment...

TLDR:

It appears that disabling and re-enabling replication probably fixed the issue:

exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'false'
exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'true'

I guess this is the equivalent of switching it off and then back on again...

Longer version:

A workmate had a go at trying to fix it. He tried a few things but didn't get very far. The one change he did make before giving up was to disable the replication.

I then tried Cody's suggestion. The sp_dropsubscription command complained that no subscriptions exists. So I tried the sp_droppublication command. This complained that replication was not enabled on the database. So I enabled it and re-ran the command. This time it complained that the publication did not exist. I refreshed the Local Publications node in SSMS and sure enough it had gone. I ran the replication set up script, generated a new snapshot and every thing is now working properly. Joy!

I'm not 100% certain that disabling and enabling the replication is what actually fixed the problem, but it is definitely worth trying if the replication gets messed up.

I had the same problem on my pre-production box, the command

exec sp_cleanupdbreplication

seem to have worked on cleaning up bogus subscription entries...


MySQL cluster concurrency I have a little application that executes select queries against a MySQL cluster.

By using 1 thread to read a record the latency is x.

By using 2 threads to read records concurrently the avg latency is ~ 2x.

By using n threads to read records concurrently the avg latency is ~ nx.

Is there a way to optimize that?

I use one mysqld for 3 Data Nodes.

Make sure that you have uniform servers across the cluster because a slow/overloaded master node can affect performance of the Galera Replication cluster.
Check the node status by monitoring value of these variables wsrep_ready and wsrep_connected
Make sure that value of this variable is close to 0 wsrep_flow_control_paused
A high value for this variable wsrep_local_send_queue_avg for a node indicates a bottleneck in network line, If this is the case, the cause can be at any layer, from physical to OS configuration and must be investigated.
You can yourself profile SELECTs on diffrent nodes and see performance.

  mysql> SET profiling = 1;

  mysql> SELECT * FROM

  mysql> SHOW PROFILES;

I hope this information helps.


Taking backup of all the databases in SQL Server I have a Microsoft SQL Server 2005 DB server. In the DB server, I have around 250 user databases. I have to take a back up of all these databases. Since, manually taking backup consumes lot of time, I am looking for a Batch script or DB script which will automatically take the backup of all the 250 databases. Can anyone please help on this?

Note: First create a folder on D: drive. (e.g. D:\User_DataBackup\)

Step 1: Create a procedure that are given below.

Create PROCEDURE [dbo].[UserDataBaseBackUp]
AS

BEGIN
SET NOCOUNT ON;

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'D:\User_DataBackup\' -- as same as your created folder'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
DECLARE db_cursor CURSOR FOR
  SELECT name
  FROM MASTER.dbo.sysdatabases
  WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
  BACKUP DATABASE @name TO DISK = @fileName
  FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END

Step 2: Execute the above procedure.

 EXEC [UserDataBaseBackUp]

You can also schedule this procedure. This procedure is tested hopefully it will help.

You could also use PowerShell as shown in Use PowerShell to Backup All User Databases by Buck Woody:

# Performs a Full backup followed by a transaction log backup on all user databases

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'BWOODY1\SQL2K8'
$bkdir = $s.Settings.BackupDirectory
$dbs = $s.Databases
$dbs | foreach-object {
    $db = $_

    if ($db.IsSystemObject -eq $False -and $db.IsMirroringEnabled -eq $False) {
        $dbname = $db.Name
        $dt = get-date -format yyyyMMddHHmmss
        $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
        $dbbk.Action = 'Database'
        $dbbk.BackupSetDescription = "Full backup of " + $dbname
        $dbbk.BackupSetName = $dbname + " Backup"
        $dbbk.Database = $dbname
        $dbbk.MediaDescription = "Disk"
        $dbbk.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", 'File')
        $dbbk.SqlBackup($s)
        if ($db.DatabaseOptions.RecoveryModel -ne 'Simple') {
            $dt = get-date -format yyyyMMddHHmmss
            $dbtrn = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
            $dbtrn.Action = 'Log'
            $dbtrn.BackupSetDescription = "Trans Log backup of " + $dbname
            $dbtrn.BackupSetName = $dbname + " Backup"
            $dbtrn.Database = $dbname
            $dbtrn.MediaDescription = "Disk"
            $dbtrn.Devices.AddDevice($bkdir + "\" + $dbname + "_tlog_" + $dt + ".trn", 'File')
            $dbtrn.SqlBackup($s)
            }
        }
    }


Two slaves using the same server-id I have a MySQL master with two slaves. Due to poor configuration both slaves had the same server-id.

My questions are:

Is the slaves' data fully synced with the master or did they miss transactions and I should start a new replication ?
During the past 4 days the master server was getting stuck randomly (3 times) with no apparent reason - is this the cause? if so, why would it get stuck? (By stuck I mean that it was running, no errors in the log, but not accepting connections)
The slaves have been busy for 3 days with a big ALTER that the master long finished, does this have anything to do with it?

The server ID is used for replication to enable master and slave servers to identify themselves, uniquely. While replication may work if two slaves have the same server ID, it could affect performance. Also, having them all unique can provide help in managing and monitoring the MySQL servers participating in the replication. Currently, a MySQL master server allows slaves with the same server-ID. This is a known problem, and could cause slaves to make several smaller relay logs files. Closing and opening files as a result will consequently slow down the replication process.


Alternative to "FOR JSON" in SQL Server 2012? In SQL Server 2016 I can output query results as JSON as described in the documentation Format Query Results as JSON with FOR JSON (SQL Server)

SELECT *
FROM x
FOR JSON PATH

But I'm finding that this is not supported in SQL Server 2012.

Is there a built-in way to convert tabular data to JSON data in SQL Server 2012?

To make this cross compatible I would ideally like to be able to use for json path in SQL Server 2012.

No. JSON support was new in SQL Server 2016.

If you want to convert data to JSON, you have a few options:

Fetch the rows as you would any other query result, and convert it in the application tier (like C#, Java, etc)
Build T-SQL logic in a stored procedure or function to convert it to JSON yourself (but keep in mind that you won't be able to use "FOR JSON PATH" the exact same way 2016 does)
Build CLR logic to do it (again, won't be compatible with "FOR JSON PATH")

Working solution to build JSON data out of SQL Server 2012
    DECLARE @TableName NVARCHAR(50)
    DECLARE @TempTableColumns TABLE (
        [Object_Id] NVARCHAR(100),
        [TableName] NVARCHAR(100),
        [ColumnName] NVARCHAR(100),
        [ColumnSequence] INT,
        [Type] NVARCHAR(100)
    )

    INSERT INTO @TempTableColumns ([Object_Id],[TableName],[ColumnName],[ColumnSequence],[Type])
    SELECT
        [T].[OBJECT_ID] [Object_Id]
        , [T].[Name]
        , [C].[Name] [ColumnName]
        , [C].[column_id] AS [ColumnSequence]
        , UPPER(CASE
            WHEN
                UPPER([ST].[Name]) = 'VARCHAR' OR UPPER([ST].[Name]) = 'NVARCHAR' THEN [ST].[Name] + '(' +  CASE WHEN CONVERT(VARCHAR,[C].[Max_Length]) = -1 THEN 'MAX' ELSE CONVERT(VARCHAR,[C].[Max_Length]) END + ')'
            ELSE [ST].[Name] END) [Type]
        FROM SYS.COLUMNS [C]
        INNER JOIN SYS.TYPES [ST] ON [C].[System_Type_Id] = [St].[System_Type_Id]
        INNER JOIN SYS.TABLES [T] ON [C].[OBJECT_ID] = [T].[OBJECT_ID]
        WHERE [ST].[Name] <> 'sysname'
    ORDER BY [T].[Name], [C].[column_id]

    SELECT
        [T1].[TableName],
        REPLACE('SELECT ROW_NUMBER() OVER (ORDER BY ' + (SELECT T3.ColumnName FROM @TempTableColumns T3 WHERE T3.TableName = T1.TableName AND T3.ColumnSequence = 1) + ' ASC) SequenceNumber, ''{' +
        STUFF((
            SELECT '"' + T2.ColumnName + '": '' + CASE WHEN ' + T2.ColumnName + ' IS NULL THEN ''NULL'' ELSE ''"'' + CONVERT(NVARCHAR(MAX), ' + T2.ColumnName + ') + ''"'' END  + '','
                FROM @TempTableColumns AS T2 WHERE T1.TableName = T2.TableName
                FOR XML PATH('')
        ),1, 0,'')  + '}'' DataResults FROM ['+ T1.TableName +'] WITH (NOLOCK)', ',}','}') AS [Query]

    FROM @TempTableColumns [T1]
    WHERE T1.TableName >= @TableName OR @TableName = ''
    GROUP BY [T1].[TableName]

The Output of queries would return Select statement as follows :
SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) SequenceNumber, '{"ID": ' + CASE WHEN ID IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ID) + '"' END  + ',"DatabaseName": ' + CASE WHEN DatabaseName IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), DatabaseName) + '"' END  + ',"SchemaName": ' + CASE WHEN SchemaName IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), SchemaName) + '"' END  + ',"ObjectName": ' + CASE WHEN ObjectName IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ObjectName) + '"' END  + ',"ObjectType": ' + CASE WHEN ObjectType IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ObjectType) + '"' END  + ',"IndexName": ' + CASE WHEN IndexName IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), IndexName) + '"' END  + ',"IndexType": ' + CASE WHEN IndexType IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), IndexType) + '"' END  + ',"StatisticsName": ' + CASE WHEN StatisticsName IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), StatisticsName) + '"' END  + ',"PartitionNumber": ' + CASE WHEN PartitionNumber IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), PartitionNumber) + '"' END  + ',"ExtendedInfo": ' + CASE WHEN ExtendedInfo IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ExtendedInfo) + '"' END  + ',"Command": ' + CASE WHEN Command IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), Command) + '"' END  + ',"CommandType": ' + CASE WHEN CommandType IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), CommandType) + '"' END  + ',"StartTime": ' + CASE WHEN StartTime IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), StartTime) + '"' END  + ',"EndTime": ' + CASE WHEN EndTime IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), EndTime) + '"' END  + ',"ErrorNumber": ' + CASE WHEN ErrorNumber IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ErrorNumber) + '"' END  + ',"ErrorMessage": ' + CASE WHEN ErrorMessage IS NULL THEN 'NULL' ELSE '"' + CONVERT(NVARCHAR(MAX), ErrorMessage) + '"' END  + '}' DataResults FROM [CommandLog] WITH (NOLOCK)

and when you run individual queries returned from above. Would give you the output as follows :

{"ID": "79143","DatabaseName": "msdb","SchemaName": NULL,"ObjectName": NULL,"ObjectType": NULL,"IndexName": NULL,"IndexType": NULL,"StatisticsName": NULL,"PartitionNumber": NULL,"ExtendedInfo": NULL,"Command": "DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'G:\Backup\AWS19WVSQL072\msdb\FULL' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)","CommandType": "xp_create_subdir","StartTime": "Jul 18 2020  1:00AM","EndTime": "Jul 18 2020  1:00AM","ErrorNumber": "0","ErrorMessage": NULL}

{"ID": "79144","DatabaseName": "msdb","SchemaName": NULL,"ObjectName": NULL,"ObjectType": NULL,"IndexName": NULL,"IndexType": NULL,"StatisticsName": NULL,"PartitionNumber": NULL,"ExtendedInfo": NULL,"Command": "BACKUP DATABASE [msdb] TO DISK = N'G:\Backup\AWS19WVSQL072\msdb\FULL\AWS19WVSQL072_msdb_FULL_20200718_010056.bak' WITH CHECKSUM, NO_COMPRESSION","CommandType": "BACKUP_DATABASE","StartTime": "Jul 18 2020  1:00AM","EndTime": "Jul 18 2020  1:01AM","ErrorNumber": "0","ErrorMessage": NULL}


Cannot revoke permissions or drop user in PgSQL AWS RDS I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and sequences inside have been created by users foo_migration and bar_migration respectively.

First I log in as the postgres user to the foo DB. When I try to drop the user, I get:

ERROR:  role "asd" cannot be dropped because some objects depend on it with details about privileges from multiple sequences, tables, the bar DB and a few objects in the postgres DB.

So, I try to clear things one by one (while still logged in as postgres user in the foo DB). I first try to REASSIGN OWNED BY asd TO foo_migration;. This returns ERROR: permission denied, which is weird because I'm logged in as the postgres user. Anyway, then I try

REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM asd;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM asd;

and I get a bunch of WARNING: no privileges could be revoked for all the sequences and tables that the user has rights on. I also try DROP OWNED BY asd; which also ends in ERROR: permission denied.

This was all unsuccessful, so I try logging in the postgres DB as the postgres user and perform the same steps. The REVOKE commands execute successfully without warnings, but no permissions actually get changed/affected. The REASSIGN and DROP OWNED BY still result in ERROR: permission denied.

The error messages are bare. There's no hints or details provided alongside with them.

I do not have the password for the asd user, so I cannot try to log in with it and perform any actions.

Do you have any ideas of how to approach this? Do I need to look into some tables that show ownerships and go from there? Isn't the postgres user the admin user that is "almighty" ?

I was running into similar issue on Postgres RDS. This was the workaround I used to reassign objects from one user to another and then drop user. For you it would go like:

-- logged in as the rds_superuser role you defined when you created your RDS instance
CREATE ROLE change_owner LOGIN;
GRANT asd TO change_owner;
GRANT foo_migration TO change_owner;

-- log out of current user and log in as `change_owner`
REASSIGN OWNED BY asd TO foo_migration;

-- log out of current user and log in as rds_superuser role
DROP USER asd;

I'm going thru this issue myself, so one easy thing you can do is change the password of asd. That can be done by running

ALTER USER asd WITH PASSWORD 'new_password';

After that, I tried the following, which was provided by AWS Support and even took me a while to realize which commands should be executed by which role.

-- logged in as postgres, grant the unwanted role to the master user
GRANT asd TO postgres;

-- and then reassign the objects
REASSIGN OWNED BY asd TO postgres;
DROP ROLE IF EXISTS asd;

In case there are still objects that asd has privileges on, you would need to log into each user that is the owner of the objects mentioned in the error message, and then revoke all the privileges for the concerned object from the user which needs to be dropped.

-- logged in as the owner of {table}
REVOKE ALL PRIVILEGES ON TABLE {table} FROM asd;

Run the above for each table, logged in as the owner of the table in question. Once done, perform the DROP ROLE again as postgres.

PS: My case has about 100 items and I still couldn't script my way around these steps nor perform it all manually, but the first tests have worked. I'm confident that this will do the trick but cannot confirm just yet. Good luck.


How can one drop all default values and allow NULL for all columns of a table? I'm working on a PHP script to modify the primary key (PK) of a table.  My current code is:

$q = "SHOW KEYS FROM $db.$table WHERE Key_name='PRIMARY';";
$keys = Query ( $q);
if ( $keys)
{
    $q = "ALTER TABLE $db.$table
            DROP PRIMARY KEY,
            ADD PRIMARY KEY ($keyQ);";
    $res = Query ( $q);
}
else
{
    $q = "ALTER TABLE $db.$table
            ADD PRIMARY KEY ($keyQ);";
    $res = Query ( $q);
}

This mostly works for what I need.  The only problem is that if I drop a field as a PK, it retains the NULL='No' and DEFAULT=0 properties from being a PK.

Since I'm going to define the PK later and I generally don't need not null and default constraints I would like to clear all not null values and default values.  Then I can proceed to define the primary key and know that the primary key is the only columns with default values and not NULL constraints.

Is there a way to do this with MySQL?  I could do it with PHP, but it would probably be more work and would be less elegant.

You might need INFORMATION_SCHEMA.KEY_COLUMN_USAGE
You would have to issue this query
SELECT column_name
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';

This will retrieve every column in the PRIMARY KEY.
You could then make this query generate the ALTER TABLE clauses for you as follows:
SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';

This will produce a comma-separated list of MODIFY COLUMN clauses you need to apply to the column after dropping the PRIMARY KEY. If you want to drop any of these columns, you have to change MODIFY COLUMN  to DROP COLUMN for the columns that need to be dropped.
Give it a Try !!!
UPDATE 2014-12-09 14:00 EST
Your comment

I'm trying to understand how this works. GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL')) is especially confusing to me. Where does column_name come from in this query? I tried running it as is in MySQL, but I get no results

To give you a general idea of how query can help you, here is a table in MySQL on my laptop
mysql> show create table weird.vinner\G
*************************** 1. row ***************************
       Table: vinner
Create Table: CREATE TABLE `vinner` (
  `startnr` int(11) NOT NULL,
  `alder` varchar(25) NOT NULL,
  `kjonn` varchar(25) NOT NULL,
  `ovelseid` varchar(25) NOT NULL,
  `slutttid` int(11) NOT NULL,
  PRIMARY KEY (`startnr`,`slutttid`),
  KEY `slutttid` (`slutttid`),
  CONSTRAINT `vinner_ibfk_1` FOREIGN KEY (`slutttid`) REFERENCES `passering` (`slutttid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

See the PRIMARY KEY. It has two columns: startnr and slutttid.
Now, if I run my code on that tabl, I get this list
mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
    -> FROM information_schema.key_column_usage
    -> WHERE table_schema='weird'
    -> AND table_name='vinner'
    -> AND constraint_name='PRIMARY';
+------------------------------------------------------------------------+
| GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))     |
+------------------------------------------------------------------------+
| MODIFY COLUMN startnr DEFAULT NULL,MODIFY COLUMN slutttid DEFAULT NULL |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

The output creates a MODIFY COLUMN clause for both columns.
If you were dropping the slutttid column, you would use PHP to change MODIFY to DROP
MODIFY COLUMN startnr DEFAULT NULL,DROP COLUMN slutttid

You would be doing this kind of code to generate the clauses needed

I wrote this script to output the scripts to change all columns in a database to allow nulls:

select
case
when sc.max_length = '-1' and st.name in ('char','decimal','nvarchar','varchar')
then
'alter table  [' + so.name + '] alter column [' + sc.name + '] ' + st.name + '(MAX) NULL'
when st.name in ('char','decimal','nvarchar','varchar')
then
'alter table  [' + so.name + '] alter column [' + sc.name + '] ' + st.name + '(' + cast(sc.max_length as varchar(4)) + ') NULL'
else
'alter table  [' + so.name + '] alter column [' + sc.name + '] ' + st.name + ' NULL'
end as query
from sys.columns sc
inner join sys.types st on st.system_type_id = sc.system_type_id
inner join sys.objects so on so.object_id = sc.object_id
where so.type = 'U'
and st.name <> 'timestamp'
order by st.name


Changed MyISAM to InnoDB, huge performance loss So I'm testing on our dev server to change MyISAM to InnoDB and am having a huge performance loss.

Here is my.cnf:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instance=2
innodb_log_file_size=512M
key_buffer_size=24M
#70% of available RAM
innodb_buffer_pool_size=3072M
innodb_thread_concurrency=0
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=5000

I'm running a Amazon CentOS t2.medium instance (2 vCPU & 4 GB ram).

Here are a few tests I ran. I ran alot more but with the same theme. InnoDB takes way longer and loads the CPU more.

Anyone have any thoughts? I've read if you have a lot of INSERTS / UPDATES with big data InnoDB is way better then MyISAM. I'm not sure what I'm missing here.

You said in your question

  I've read if you have a lot of INSERTS / UPDATES with big data InnoDB is way better then MyISAM. I'm not sure what I'm missing here.

Your server class is too small for your InnoDB settings.

On Apr 14, 2011, I mentioned how the storage engines have distinct ways of caching.

MyISAM will only cache indexes in RAM. Data is always read from disk.
InnoDB caches data and indexes in the InnoDB Buffer Pool

I also noted how your innodb_read_io_threads and innodb_write_io_threads are maxed out. You only have 4GB of RAM or which you only have 1GB free (due to your 3GB or 3072M Buffer Pool). I would lower your threads from 64 to 8.

If you have the budget, you should go upgrade your server class to m3.large or m3.xlarge where you can have 4 CPU and 15 or 16G of RAM. You could then increase innodb_buffer_pool_size to 10G or 12G. Then, your remaining InnoDB settings would make sense. I would still lower your threads from 64 to 8.

The structure of MyISAM and InnoDB are not the same:
MyISAM
When do dml(ex:insert) it's just store in memory and then push it after that.(bad when server crush)
InnoDB
need to syncs that data to disk until finished it cost the disk I/O.
Hint

you can set you transaction to autocommit=0;
https://dev.mysql.com/worklog/task/?id=6470


How to create a row for every day in a date range using a stored procedure? I would like to create a stored procedure that will create a row in a table for every day in a given date range.  The Stored Procedure accepts two inputs - A start date and end date of the date range desired by the user.

So, let's say I have a table like so:

SELECT Day, Currency
FROM ConversionTable

Day is a DateTime, and Currency is just an integer.

To keep things simple, let's just say I always want the Currency column to be 1 for each of these inserted rows.  So, if someone inputs 'March 5, 2017' as the start date and 'April 11, 2017' as the end date, I would like the following rows created:

2017-03-05 00:00:00, 1
2017-03-06 00:00:00, 1
...
2017-04-11 00:00:00, 1

What's the best way to code the stored procedure to do this?  I am using SQL Server 2008 R2 in my test environment, but our real environment uses SQL Server 2012, so I can upgrade my test machine if there is new functionality introduced in 2012 that makes this task easier.

One option is a recursive CTE:

DECLARE @StartDate datetime = '2017-03-05'
       ,@EndDate   datetime = '2017-04-11'
;

WITH theDates AS
     (SELECT @StartDate as theDate
      UNION ALL
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE DATEADD(day, 1, theDate) <= @EndDate
     )
SELECT theDate, 1 as theValue
  FROM theDates
OPTION (MAXRECURSION 0)
;

(MAXRECURSION hint added thanks to Scott Hodgin's comment, below.)

Fill about 10 records in Item & 3 in Category tables, then fill Ledger table with historical data from 01.01.10 to 31.12.20 as 10 records per day using a stored procedure.


What flavor of SQL is used inside Microsoft Access? I don't have much Access experience, and I took on a new Access project. I can use SQL to query the Access databases.

What SQL specification/flavor is used inside Access? Is it the same as SQL Server?

For full details read the docs: Microsoft Access SQL reference

MS Access definitely is not the same as SQL Server. The overall syntax is similar as any flavor of SQL, but it doesn't have a lot of features that were added since SQL Server 2005.

It doesn't even have some pretty basic features, like:

  DISTINCT aggregate function references. For example, Microsoft Access SQL does not allow SUM(DISTINCT columnname).
  The LIMIT TO nn ROWS clause used to limit the number of rows returned by a query. You can use only the WHERE clause to limit the
  scope of a query.

On the other hand, it has enhanced features, like:

  The TRANSFORM statement, which provides support for crosstab queries.
  Additional aggregate functions, such as StDev and VarP.
  The PARAMETERS declaration for defining parameter queries.

Window functions that were added since SQL Server 2005+, such as ROW_NUMBER, OVER clause don't exist as well.

Set of built-in functions for string or date/time manipulation are similar, but they are not the same.

To answer "Is it the same as SQL Server", no.
SQL Server (T-SQL) can scale up to much larger tasks than the Access (JET SQL).
In terms of flavors, think of Access as limited to tablespoons while SQL Server (and other databases), can accept ladles, pots, vats, or barrels of data. (To extend the analogy, Big Data in the tera- and petabytes gets you into swimming pools, lakes, and oceans, and require non-relational database tools).
MS Access limits documented by Microsoft are here.  Some key differences:

MS Access Jet
SQL Server T-SQL

Max Database Size
2 GB
524 TB

Simultaneous Users
255
32767

Number of Objects
32768
2147483647

If you are dealing with tables holding 100K records or more, just a few queries can bump up against the size and object limits.  It's possible to work around with intermediate queries, but these can become more complex and difficult to manage than putting the same logic into a scalable database like MS SQL Server.


What is the use of "Backup set will Expire:" option while creating Backup Database task in Maintenance plan While backing up the database, I would like to use "Backup set will Expire:" option to delete/overwrite old database backups. I am not able to use this option.

Any advice on how to use "Backup set will Expire:" options while creating Maintenance plan will be appreciated.

The "Backup set will Expire" option is used to indicate how long a backup should be prohibited from being over written. It will not clean up old backup files. You can use the "Maintenance Clean up Task" in your maintenance plan to clean up backup files older than a certain number of days.

The "Maintenance Clean up Task" checked only file physically write time and not check the Backupset Expire time.
This can use only with INIT Option.


how to verify DB hive already exists by expect script we create the database hive and some other commands

in case we run the expect script again on the machine that hive already created

then we get - ERROR hive already exists

so how to verify database hive already created ?

And by this verification we can escape the expect script

# su - postgres
 Last login: Sun Aug 13 11:12:03 UTC 2017 on pts/0
 -bash-4.2$ psql
 psql (9.2.13)
 Type "help" for help.

postgres=# CREATE DATABASE hive;
ERROR:  database "hive" already exists

my expect script: ( from my bash script )

set timeout -1

#exec the scripts

spawn timeout 60 ssh root@IP
expect "#"
spawn su - postgres
expect "$"
send "psql\n"
expect "=#"
send "CREATE DATABASE hive;\n"
.
.
.
.

you can use IF EXISTS clause:

create database if exists hive;


Please enlighten me about FILESTREAM and BLOB files This is a really confusing topic for me.

I can understand what a BLOB is, I can easily use it.
I can understand what FILESTREAM is. I can easily implement in on a database, I can backup the database and restore it with the folder created to store these files. Things like these, I think, I can understand well.

What I don't get is:

What's the difference between inserting the .PDF (for example) using just a VARBINARY(MAX) column in the database, and storing this .PDF in a FILESTREAM database. I know with the BLOB varbinary(max) the .PDF is INSIDE the database. If I physically delete the .PDF, can I still restore it using the value stored inside the varbinary(MAX) column right?
When I insert this .PDF file in a filestream, I can see it inside the folder (the one created for the filegroup), if I insert 100 files, I can see 100 files inside the filestream folder, but when I run DELETE FROM and delete 100 rows in the database, I can still see these files in the filestream folder. Why does this happen? I thought that, to maintain consistency, these files would be deleted too.
With both cases, after inserting the .PDF file inside the BLOB VARBINARY(MAX) or FILESTREAM, do I still need the original file? As it is inside the database, I can easily recover it.

I'm using .PDF as an example

I can use and implement both approaches but it still not clear to me what happens with them. For me, it's the same thing, but one is stored outside the database, and the other one inside the database.

Based on the whitepaper written by Paul Randal -

BLOBs smaller than 256 kilobytes (KB) (such as widget icons) are better stored inside a database.
BLOBs larger than 1 megabyte (MB) are best stored outside the database.
For those sized between 256 KB and 1 MB, the more efficient storage solution depends on the read vs. write ratio of the data, and the rate of “overwrite”. Storing BLOB data solely within the database (e.g., using the varbinary (max) data type) is limited to 2 gigabytes (GB) per BLOB.

Answering your questions :

  What's the difference between inserting the .PDF (for example) using just a VARBINARY(MAX) column in the database, and storing this .PDF in a FILESTREAM database. I know with the BLOB varbinary(max) the .PDF is INSIDE the database. If I physically delete the .PDF, can I still restore it using the value stored inside the varbinary(MAX) column right?

Once you insert the pdf into the database as SINGLE_BLOB, you dont need the original .pdf file. You can export out if you need it at later time.

  When I insert this .PDF file in a filestream, I can see it inside the folder (the one created for the filegroup), if I insert 100 files, I can see 100 files inside the filestream folder, but when I run DELETE FROM and delete 100 rows in the database, I can still see these files in the filestream folder. Why does this happen? I thought that, to maintain consistency, these files would be deleted too.

File Streams uses a garbage collection process to clean up files that are not longer needed.   A system task wakes up periodically (~10 seconds or so) and checks for file stream garbage collection needs. Read "Why don't all the files get collected?"

  DANGER:  Deleting a file directly from the file stream container is considered database corruption and dbcc checkdb will report corruption errors.  These files are linked to the database in a transactional way and the deletion of a file is the same as a damaged page within the database.

-

  With both cases, after inserting the .PDF file inside the BLOB VARBINARY(MAX) or FILESTREAM, do I still need the original file?

For BLOB Varbinary(max) --> You can delete the original file. For Filestream, do not delete the file from the filestream folder. If the original file is not in the same folder than the filestream folder, then you can delete it.

References :

Excellent whitepaper (pdf) : To BLOB or Not To BLOB:
Large Object Storage in a Database or a Filesystem?
Free book from redgate : The Art of
SQL Server FILESTREAM

Yes, you can restore it in either case. Filestream uses the best of 2 worlds:  the file transfer optimization using NTFS when retreiving the data and the structured storage of SQL puting the files in a special folders called Containers;the file size limit is determined by the space of the filesystem.
While Varbinary(MAX) puts the entire file (as a BLOB) into the database table space, it has a size limit of 2GB. According to several documentation you should use Filestream for quick reading files above 1MB.
check this topic https://stackoverflow.com/questions/1625001/filestream-files-being-left-behind-after-row-deleted
No, you don't need it any longer.


Does 3NF require 2NF? I am a bit confused with the definition of 3NF.

According to Wikipedia:

  The third normal form (3NF) is a normal form used in database
  normalization. 3NF was originally defined by E.F. Codd in 1971. Codd's
  definition states that a table is in 3NF if and only if both of the
  following conditions hold:

  The relation R (table) is in second normal form (2NF)
  Every non-prime attribute of R is non-transitively dependent on every superkey of R.

But, according to book  the Database System Concepts 6th Edition by Abraham Silberschatz, Henry F.Korth, S.Sudarshan:

  A relation schema R is in third normal form (3NF) with respect to a
  set F of functional dependencies if, for all functional dependencies
  in F+ of the form α → β, where α ⊆ R and β ⊆ R, at least one of the
  following holds:

  α → β is a trivial functional dependency.
  α is a superkey for R.
  Each attribute A in β − α is contained in a candidate key for R.

Also, according to Database Systems 6th Edition by Ramez Elmasri  Shamkant B. Navathe:

  A relation schema R is in third normal form (3NF) if, whenever a
  nontrivial functional dependency X→A holds in R, either (a) X is a
  superkey of R, or (b) A is a prime attribute of R.

After reading the above definitions, I am not sure if a relation schema has to be in 2NF in order to be in 3NF. Also, does a relation schema have to be in 3NF in order to be in BCNF?

The normal forms are specified in such a way that for any normal form, the model is also meets the criteria for the lower numbered normal forms.  So, yes if your model is in 3rd normal form, it is also in 2nd normal form and 1st normal form.

When normalizing a model, you normally work at resolving one normal form before the next.  This makes normalization simpler and less error prone as you are only applying a few rules at at time.  It is possible that an entity at a lower normal form is also at a higher normal form.  Most 3rd normal form entities are also in 4th and 5th normal form.

4th, 5th, and 6th normal forms go beyond the functional dependence.  Where applicable, they further simplify the data, at the expense of more tables.  They reduce redundancy, and increase data integrity.

By trying to implement 2nf and 3nf you might yet a better idea. In real time you can't differentiate between these two as they happen at the same time. When you are 2nf-ing you just can't stop there and w 3nf is not itself if it doesn't have primary keys or super keys defined.


Insert data from a source table into a destination table with more columns? I want to use INSERT INTO SELECT * to insert data into a dest table from a source table, but I have some extra columns added to destination table. These columns are distributed around the table like 2 are at top and 2 are at bottom.

Also for insertion I can insert some default value like null to these columns.

SELECT * selects col1, col2, col3, as defined in table2, and if the selection doesn't match the target, it will fail.

the conclusion is to alter the selection:

INSERT INTO table1
SELECT col1, col2, NULL as col_extra, 'default' as col_extra2, col3, [...]
FROM table2

not tested, but should work

INSERT INTO test_import_two (`name`, name1, name2)
 (SELECT `name`, name1, name2
   FROM test_import_one WHERE id = 2
 )


Difference between MongoDB's find and findone calls I am working on a project and I am unsure if there is a difference between the way the find cursor works and the way the findOne cursor works. Is findOne just a wrapper for find().limit(1)? I was looking around for it and maybe someone knows if mongodb has a special method for it or not. I am working with the PHP API for mongodb if that makes a difference.

Based on my own benchmarks, find().limit(1) is orders of magnitude faster than findOne().

There is either an error in the MongoDB documentation or a bug in findOne(). findOne() performs more like find().limit(N) where N is the number of documents the query would return. I figured this out while trying to figure out why my simple queries were so slow!

update: response from a 10gen (MongoDB) engineer:

  The two queries you are executing are very different. A find query
  returns a cursor, this is essentially a no-operation scenario, as no
  actual data is returned (only the cursor information). If you call
  findOne, then you are actually returning the data and closing the
  cursor. The docs should definitely be clearer :-)

Update: Indeed, if the find().limit(1) document is retrieved, the orders of magnitude speed difference seems to disappear. Also, I could not reproduce the major speed difference with the MongoDB JavaScript driver. I originally benchmarked using the MongoDB Java driver.

You must check this link ...

http://mongoosejs.com/docs/2.7.x/docs/finding-documents.html

find    -  option return  array []
findOne -  option return document {}