The max size for a column of type `NVARCHAR(MAX)` is 2 GByte of storage.

Since `NVARCHAR` uses 2 bytes per character, that&#39;s approx. 1 billion characters.

Leo Tolstoj&#39;s *War and Peace* is a 1&#39;440 page book, containing about 600&#39;000 words - so that might be 6 million characters - well rounded up. So you could stick about 166 copies of the entire *War and Peace* book into each `NVARCHAR(MAX)` column.

Is that enough space for your needs? :-)

&gt;By default, nvarchar(MAX) values are stored exactly the same as nvarchar(4000) values would be, unless the actual length exceed 4000 characters; in that case, the in-row data is replaced by a pointer to one or more seperate pages where the data is stored.

If you anticipate data possibly exceeding 4000 character, nvarchar(MAX) is definitely the recommended choice.

Source: https://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/d5e0c6e5-8e44-4ad5-9591-20dc0ac7a870/

From [MSDN Documentation][1]

&gt; **nvarchar** [ ( n | **max** ) ]
&gt;
&gt; Variable-length Unicode string data. _n_ defines the string length and can be a value from 1 through 4,000. **max** indicates that the maximum storage size is 2^31-1 bytes (2 GB).
The storage size, in bytes, is two times the actual length of data entered + 2 bytes

[1]: http://msdn.microsoft.com/en-us/library/ms186939.aspx

I think actually `nvarchar(MAX)` can store approximately 1070000000 chars.



I need to take an image and save it after some process. The figure looks fine when I display it, but after saving the figure, I got some white space around the saved image. I have tried the `&#39;tight&#39;` option for `savefig` method, did not work either. The code:

      import matplotlib.image as mpimg
      import matplotlib.pyplot as plt

      fig = plt.figure(1)
      img = mpimg.imread(&quot;image.jpg&quot;)
      plt.imshow(img)
      ax = fig.add_subplot(1, 1, 1)

      extent = ax.get_window_extent().transformed(fig.dpi_scale_trans.inverted())
      plt.savefig(&#39;1.png&#39;, bbox_inches=extent)

      plt.axis(&#39;off&#39;) 
      plt.show()

I am trying to draw a basic graph by using NetworkX on a figure and save it. I realized that without a graph it works, but when added a graph I get white space around the saved image;

    import matplotlib.image as mpimg
    import matplotlib.pyplot as plt
    import networkx as nx

    G = nx.Graph()
    G.add_node(1)
    G.add_node(2)
    G.add_node(3)
    G.add_edge(1, 3)
    G.add_edge(1, 2)
    pos = {1:[100, 120], 2:[200, 300], 3:[50, 75]}

    fig = plt.figure(1)
    img = mpimg.imread(&quot;image.jpg&quot;)
    plt.imshow(img)
    ax = fig.add_subplot(1, 1, 1)

    nx.draw(G, pos=pos)

    extent = ax.get_window_extent().transformed(fig.dpi_scale_trans.inverted())
    plt.savefig(&#39;1.png&#39;, bbox_inches=extent)

    plt.axis(&#39;off&#39;) 
    plt.show()

Removing white space around a saved image

I am attempting to insert data from a dictionary into a database. I want to iterate over the values and format them accordingly, depending on the data type. Here is a snippet of the code I am using:

    def _db_inserts(dbinfo):
        try:
            rows = dbinfo[&#39;datarows&#39;]
    
            for row in rows:
                field_names = &quot;,&quot;.join([&quot;&#39;{0}&#39;&quot;.format(x) for x in row.keys()])
                value_list = row.values()
    
                for pos, value in enumerate(value_list):
                    if isinstance(value, str):
                        value_list[pos] = &quot;&#39;{0}&#39;&quot;.format(value)
                    elif isinstance(value, datetime):
                        value_list[pos] = &quot;&#39;{0}&#39;&quot;.format(value.strftime(&#39;%Y-%m-%d&#39;))
    
                values = &quot;,&quot;.join(value_list)
    
                sql = &quot;INSERT INTO table_foobar ({0}) VALUES ({1})&quot;.format(field_names, values)
    
        except Exception as e:
            print &#39;BARFED with msg:&#39;,e


When I run the algo using some sample data (see below), I get the error:

&gt; TypeError: sequence item 0: expected string, int found

An example of a value_list data which gives the above error is:

    value_list = [377, -99999, -99999, &#39;f&#39;, -99999, -99999, -99999, 1108.0999999999999, 0, &#39;f&#39;, -99999, 0, &#39;f&#39;, -99999, &#39;f&#39;, -99999, 1108.0999999999999, -99999, &#39;f&#39;, -99999, &#39;f&#39;, -99999, &#39;f&#39;, &#39;f&#39;, 0, 1108.0999999999999, -99999, -99999, &#39;f&#39;, &#39;f&#39;, &#39;f&#39;, -99999, &#39;f&#39;, &#39;1984-04-02&#39;, -99999, &#39;f&#39;, -99999, &#39;f&#39;, 1108.0999999999999] 

What am I doing wrong?



TypeError: sequence item 0: expected string, int found

I have declared a column of type `NVARCHAR(MAX)` in SQL Server 2008, what would be its exact maximum characters having the MAX as the length?

What is the maximum characters for the NVARCHAR(MAX)?

<p>I have declared a column of type <code>NVARCHAR(MAX)</code> in SQL Server 2008, what would be its exact maximum characters having the MAX as the length?</p>


I have different dates in a column.  For example:

    20080102
    20070821

I want to convert these dates in Year and calendar quarter.  E.g.,

    Year      Quarter
    2008      2008-Q1
    2007      2007-Q3

I can get the first column with:

    select left(date,4) as year from table 

How can I produce the second column?

How to get calendar Quarter from a date in TSQL

In this excellent [SO question][1], differences between `CTE` and `sub-queries` were discussed.

I would like to specifically ask: 

In what circumstance is each of the following more efficient/faster?

- CTE
- Sub-Query
- Temporary Table
- Table Variable

Traditionally, I&#39;ve used lots of `temp tables` in developing `stored procedures` - as they seem more readable than lots of intertwined sub-queries.

`Non-recursive CTE`s encapsulate sets of data very well, and are very readable, but are there specific circumstances where one can say they will always perform better? or is it a case of having to always fiddle around with the different options to find the most efficient solution?
 

----------

**EDIT**  

I&#39;ve recently been told that in terms of efficiency, temporary tables are a good first choice as they have an associated histogram i.e. statistics.


  [1]: https://stackoverflow.com/questions/706972/difference-between-cte-and-subquery

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

    SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
    FROM Invoices
    WHERE BalanceDue &gt; 0 --error

The calculated value `BalanceDue` that is set as a variable in the list of selected columns cannot be used in the `WHERE` clause. 

Is there a way that it can? In this related question (https://stackoverflow.com/q/3567863/264031), it seems like the answer would be, actually, no, you would just write out the calculation (*and* perform that calculation in the query) twice, none of which is satisfactory. 

Reference alias (calculated in SELECT) in WHERE clause

I&#39;m trying to write the following in order to get a running total of distinct NumUsers, like so:

    NumUsers = COUNT(DISTINCT [UserAccountKey]) OVER (PARTITION BY [Mth])

Management studio doesn&#39;t seem too happy about this. The error disappears when I remove the `DISTINCT` keyword, but then it won&#39;t be a distinct count.

`DISTINCT` does not appear to be possible within the partition functions. 
How do I go about finding the distinct count? Do I use a more _traditional_ method such as a correlated subquery?

Looking into this a bit further, maybe these `OVER` functions work differently to Oracle in the way that they cannot be used in `SQL-Server` to calculate running totals.

I&#39;ve added a live example here on [SQLfiddle][1] where I attempt to use a partition function to calculate a running total.


  [1]: http://sqlfiddle.com/#!3/a54aa/2

Partition Function COUNT() OVER possible using DISTINCT

I&#39;ve been searching around the web trying to figure out the right syntax to have Entity Framework Code First create my table with a column: varchar(max).

This is what I have. By default this creates varchar(128). How do I create varchar(max)?

I have tried [MaxLength] without success.

Any help would be appreciated. Thanks!

    [Column(TypeName = &quot;varchar&quot;)]
    public string MediaDesc { get; set; }

How do I set a column in SQL Server to varchar(max) using ASP.net EF Codefirst Data Annotations?

 I have a database file `.mdf` from MS SQL EXPRESS in folder:

    C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA

I would like to attach it to **MS 2008 R2** (MSSQL10_50.MSSQLSERVER) but using Server Management Studio I receive the following error:

   `CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file`

Do you have any idea how to solve it?

CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105)

I am using SQL Server 2008 R2, I have a script to update the DB, that script is approx
50 MB in size and contains some about 800,000 lines.

Error: 

&gt; TITLE: Microsoft SQL Server Management Studio
&gt; 
&gt; Cannot execute script.
&gt; 
&gt; ADDITIONAL INFORMATION:
&gt; 
&gt; Insufficient memory to continue the execution of the program.
&gt; (mscorlib)

Can somebody please help me to run this script without getting this error?

Getting error while running 50 MB script on SQL Server 2008 R2

I&#39;ve been having trouble using the `TransactionScope` to wrap multiple database queries into a transaction, I am using SqlBulkCopy with batchsize 500. When I increased the batch size to 1000 I am getting the error:

&gt; The transaction associated with the current connection has completed
&gt; but has not been disposed. The transaction must be disposed before the
&gt; connection can be used to execute SQL statements.

This is the code I am using:

    using (var scope = new TransactionScope())
	{
		using (var connection = (SqlConnection)customerTable.OpenConnection())
		{
			var table1BulkCopy = new SqlBulkCopy(connection)
			{
				BatchSize = BATCH_SIZE,
				DestinationTableName = TableName1
			};
		   
			table1BulkCopy.WriteToServer(table1DataTable);
		   
			var table2BulkCopy = new SqlBulkCopy(connection)
			{
				BatchSize = BATCH_SIZE,
				DestinationTableName = TableName2
			};
			
			table2BulkCopy.WriteToServer(table2DataTable);
						 
			var table3BulkCopy = new SqlBulkCopy(connection)
			{
				BatchSize = BATCH_SIZE,
				DestinationTableName = TableName3
			};
		   
			table1BulkCopy.WriteToServer(table3DataTable);
											
			var table4BulkCopy = new SqlBulkCopy(connection)
			{
				BatchSize = BATCH_SIZE,
				DestinationTableName = TableName4
			};
		   
			table4BulkCopy.WriteToServer(table4DataTable);
		   
			scope.Complete();
		}
	}

Error - The transaction associated with the current connection has completed but has not been disposed

I have a table with the following data:

    Pk_Id  ProductName           Fk_CompanyId       Price
    ------------------------------------------------------
    1      AMX                   1                  10.00
    2      ABC                   1                  11.00
    3      APEX                  1                  12.00
    4      AMX                   1                  10.00
    5      ABC                   1                  11.00
    6      APEX                  1                  12.00
    7      AMX                   2                  10.00
    8      ABC                   2                  11.00
    9      APEX                  2                  12.00

I want to generate Insert script for migrating records whose Fk_CompanyId is 1.

There is an insert script option to generate script for all records but I want to filter some records to migrate to another database.



Generate insert script for selected records?

I am having trouble encrypting a connection string in app.config.  I have code that will protect the connectionStrings section of app.config, but the password is still displayed in plain text.

I need to encrypt the connection string in so it is not in plain text when deployed.  I see similiar questions on SO for web.config, but not app.config.

Encrypt connection string in app.config

I have a problem when data is null and the warning has appear when the result is display.
How to solve this problem?. How to change the null data to 0 when no data in the table?.

This is my code:-

    SELECT DISTINCT c.username             AS assigner_officer,
                    d.description          AS ticketcategory,
                    (SELECT Count(closed)
                     FROM   ticket
                     WHERE  assigned_to = c.user_id
                            AND closed IS NOT NULL
                     GROUP  BY assigned_to)closedcases,
                    (SELECT Count(closed)
                     FROM   ticket
                     WHERE  assigned_to = c.user_id
                            AND closed IS NULL
                     GROUP  BY assigned_to)opencases
    FROM   ticket a
           JOIN ticketlog b
             ON a.ticketid = b.ticketid
           JOIN access c
             ON a.assigned_to = c.user_id
           JOIN ticket_category d
             ON a.cat_code = d.id
           JOIN lookup_department e
             ON a.department_code = e.code 

    
The result appear like this:-


     Warnings: ---&gt; 
       W (1): Warning: Null value is eliminated by an aggregate or other SET operation.
              &lt;--- 
     assigner_officer     ticketcategory     closedcases     opencases    
     -------------------  -----------------  --------------  ------------ 
     abdulhafiz           Enquiry            (null)          0            
     affan                Enquiry            12              (null)       
     amirul               Enquiry            1               (null)       
     azrul_fahmi          Enquiry            45              0            
     Azwani               Enquiry            (null)          0            
     chai                 Enquiry            4               (null)       
     dalinawati           Enquiry            1               0            
     Emmy                 Complaints         (null)          0            
     Fadhlia              Enquiry            38              0            
     fairulhalif          Others             1               (null)       
     farikh               Enquiry            (null)          0            
     ismailh              Enquiry            28              0            
     izzahanna            Enquiry            (null)          0            
     Kamsuzilawati        Enquiry            1               (null)     



Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio

I have two t-sql queries using SqlServer 2005. How can I measure how long it takes for each one to run? 

Using my stopwatch doesn&#39;t cut it.

Measure the time it takes to execute a t-sql query

**Preamble**

I was modifying a column in SQL Server 2008 today, changing the datatype from something like currency(18,0) to (19,2).  

I got the error &quot;The changes you have made require the following tables to be dropped and re-created&quot; from SQL Server. 

**Before you scramble to answer, please read the following:**

&gt;I already know there is the option in **Tools ► Options ► Designer ► Table and Database Designers ► Uncheck the box** &quot;Prevent saving changes that require table re-creation.&quot;
&gt;[![Prevent saving changes that require table re-creation in five clicks][1]][1]
&gt;...so **do not** answer with that!

**Actual question**

My actual question is for something else, as follows: 

Are there any negative effects / possible drawbacks of doing this? 

Does the table actually get dropped and recreated automatically when this box is unchecked?

If so, is the table copy a 100% exact replica of the source table?


  [1]: https://i.stack.imgur.com/NqKim.gif

&quot;Prevent saving changes that require the table to be re-created&quot; negative effects

I tried to restore a database using the following query:

&lt;!-- language: lang-sql --&gt;

    ALTER DATABASE [DatabaseName] SET Single_User WITH Rollback Immediate GO
    RESTORE DATABASE DatabaseName FROM DISK = &#39;C:\DBName-Full Database Backup&#39;
    ALTER DATABASE [DatabaseName] SET Multi_User GO

but instead of restoring the database, I am getting this error:

&gt; Msg 3159, Level 16, State 1, Line 2
&gt;
&gt; The tail of the log for the
&gt; database &quot;DatabaseName&quot; has not been backed up. Use `BACKUP LOG WITH NORECOVERY` to backup the log if it contains work you do not want to
&gt; lose. Use the `WITH REPLACE` or `WITH STOPAT` clause of the `RESTORE`
&gt; statement to just overwrite the contents of the log. Msg 3013, Level
&gt; 16, State 1, Line 2 `RESTORE DATABASE` is terminating abnormally.

Content Type	Original Author	Original Content on Stackoverflow
Question	Jerameel Resco	View Question on Stackoverflow
Solution 1 - Sql Server	marc_s	View Answer on Stackoverflow
Solution 2 - Sql Server	Madushan	View Answer on Stackoverflow
Solution 3 - Sql Server	JiNish	View Answer on Stackoverflow
Solution 4 - Sql Server	Francisco Javier Larios Soto	View Answer on Stackoverflow

What is the maximum characters for the NVARCHAR(MAX)?

Sql Server Problem Overview

Sql Server Solutions

Solution 1 - Sql Server

Solution 2 - Sql Server

Solution 3 - Sql Server

Solution 4 - Sql Server

TypeError: sequence item 0: expected string, int found

Removing white space around a saved image

Attributions